If Field 1 Is Yes, Then Field 2 Must Have Comment
I just can't figure this out..I have two fields in my database that I want to be able to have a regulation that if the user Enter Yes to field1 than they MUST enter some comment on field2. They can't skip it. However, Field1=No, they can skip field2..Is this possible? is this have something to do data validation? Please help..thanks in advance..
New to the forum. Any help you can give is greatly appreciated. I consider myself maybe a little above a beginner on Access.
I need a formula to use in a report running off of a query for the following:
If field 1 equals "P1" and field 2 < "01/01/2002", sum field 3.
Hi all, I only want to do this at the Report level if possible.
I have a Report running off a Query. The Query has 2 Date Fields.
I would like Field A to display the value shown in Field B, if Field A is not populated.
In the instance where Field A does not contain a value, I only want Field B's value to be shown in Field A on the report, and don't actually need the data to be stored.
I hope I'm making sense.
I have two fields 1 & 2
field 1 is a simple combo list of user defined values ie A, B, C or D
Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e
A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z
I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc
How do I do this?
I have a form that I want to set up so that the user can either manually enter data in this memo field or click a button to populate it based on a memo field in another table.
The two tables are linked by the WORKORDER field which is unique.
I have a qry which outputs a single email address that I want to export to the "To" field of my default email program with the click of a button hopefully.
The query is called qryemailsec, the field inside is [email] and I need it to output to Outlook Express 6. Can this be done?
How can I double click a field in a non editable or locked field to be editable or unlocked field in a form for safety reasons and after updating or lostFocus the field again becomes locked or non editable. I remain for your kind reply. thanks and regards.
Have completed a monthly invoice system ... including a monthly invoice report which accomplishes all but one important function ... the total outstanding balance needs to be updated in the customer table field "Previous Balance" (a currency field).
I bought the book "Microsoft Access 2000 Bible" which contains an example of a monthly invoice with a calculated field called "Outstanding Balance" ... but the book does not describe how to get the calculated result into the customer table field "Outstanding Balance." E-mail to the author of the book has not been answered.
I've racked my brain trying to figure out how to get around this bottleneck ... it should be fairly simple (at least it was when I wrote this routine in dBase II years ago):
1. Two tables: customers (basic data) and charges, related by unique customer ID
2. Invoice report ... start at top of Customer ID sorted tables, step through each record with activity charges, total each line, add that all up with total charges for the month, add the previous balance (from customer table), subtract all credits posted for that account... add that total to the current month activity for a current balance ...
3. Now, take that current balance and put that data into the customer table Previous Balance field.
4. Go to the next record, repeat the process.
It all works except the updating of customer balance. I figured that a macro must be the way to attack this problem, but have no clue as to where to start.
Thanks for any help ...
The Post Dispatch
Post City, Texas
I'm clearly new to access, but I'm trying to create a very simple db to track automobile maintenance history. I have each record autonumbered including basic info such as HistDate, Mileage, etc. How can I display the difference between the current mileage and the last recorded mileage for an oil change? I have different types of maintenance in the same table so I would only want the mileage difference between oil changes and not coolant change etc. I hope I was clear enough, any input would be greatly appreciated.
This is my second try at posting. Here goes. I want to create a third field in a table's design view that subtracts one field's value from another field's value to create the field Units in Stock. It works when I create the field in the form but I don't know how to a this in the table's design view. I am going to the expression builder and typing in this Ex:= [Inventory Received]-[Inventory Consumed] I am not even sure if I am in the right section of the properties details. Please help.
Can someone hopefully suggest a method to extract a certain line of text from a memo field in a table.
I have a database which has a linked table to Outlook. I have created a table which selects just the two fields from this linked table which are needed i.e. Created and Contents
The body of the email goes into the Contents field which is a memo type. This is all okay for my use.
The emails are from a web site by users completing an online form. They always follow the same format.
What I need to be able to do is extract a line of text from this memo field and create a seperate control on a form so they can be indexed.
The email always has the following:
Event = 1 (This number can change)
StaffNbr = 1000 (This number also changes and is the unique number required to be extracted)
The email then contains several other items and text but I am not concerned about these items.
Whilst the field in the tables "Created" is probably unique indexing/ searching on that is not practical as it is the member of staff that needs to be searched for.
I need to figure out a way to take data in a specific field for a specific record and parse the data into multiple records: For example, I have a table titled: dbo_TRA. In this table are four fields: CA_ID, RISK_ID, CA_NAME, & DESCRIPTION.
In the first record of the table, the data in field DESCRIPTION looks like this:
<P>Posted on quarterly basis.</P><P>Controls inadequate.</P><P>Seek additional help.</P>
The entire record looks like this (fields are seperated with a pipe):
599 | 26804 | Forecasts used for reporting | <P>Posted on quarterly basis.</P><P>Controls inadequate.</P><P>Seek additional help.</P>
I need to get the one record to look like this in three records:
599 | 26804 | Forecasts used for reporting | <P>Posted on quarterly basis.</P>
599 | 26804 | Forecasts used for reporting | <P>Controls inadequate.</P>
599 | 26804 | Forecasts used for reporting | <P>Seek additional help.</P>
Please let me know if you have any suggestions.
I have a small database that tracks who has a patient's paper chart as it's passed around the office. Whenever a chart leaves the record room it is put into the database. Whenever it is moved from one person to another, that person uses a form to update where it is. What I'd like to do is add a control that changes the "DateSent" field to NOW whenever anyone changes the field "SentTo".
What I've tried is linking the form to an update query, so that any time the SentTo field is modified the DateSent is udpated to NOW. However I can't get it working.
Any suggestions on the simplest way to accomplish this?
Hi, I'm very new at creating forms. I need a control to lookup a record in a table. I want my form to be able to lookup a Vendor ID number from a table based on what I select from a drop-down list of Vendor Names. I can't figure out how to use the Dlookup function, and that may not be the correct method anyway. I did create a combo box which includes my Vendor names, so the next control on my form I want to automatically populate with the Vendor ID number from the vendor table. Is this even possible?
Any help is very much appreciated. I'm just not very good at this yet!
I don't think this is possible, but...
Can I get a field to not show on a report if the query returns a blank field?
I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.
The two tables I would like to link are,
Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records.
Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.
I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).
The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.
I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?
The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.
I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.
Thanks in advance (I hope),
I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.
What formula do I use?
I'm creating a database that keeps a track of questions and scores.
The questions in the database need to be dynamic and are changed frequently.
I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.
I want the question field in my scorecard table to populate with the value in my question table.
I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:
This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.
The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id
FROM ft_payees AS p, ft_txn_summary AS t
WHERE p.payee_id=t.payee_id And p.market=t.market
GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc
SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "")
WHERE rc.component_name='Total Revenue';
as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!
hi, i would like to be able to prevent a user from being able to change a field (lock a field) in a main form if a field in a subform is complete. (the field in the subform is named: "new_weekly_base" if this is complete then i would like the field: "weekly base" to be locked on the main form. is this possible?, please help.
the main form is named: "SCREEN-MAIN"
the subform is named: "SCREEN-SUBFORM"
(the main form has a button on it which loads the subform.)
ive tried the below code but it doesnt work, any help would be excellent.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If [NEW_WEEKLY_BASE] >= 0 Then
.Visible = True
.Enabled = False
in my form I created an unbound field with a requested combination, now how can I put those in a table field.
in other words, how can I send the content of a form's unbound field to a field in the table?
your help is very appreciated.
I need to create a New Form control for this situation:
If I enter a date into a field and the choice for another field is equal to a certain value. How can I get the date I entered to be automatically populated into another date field.
If I enter 11/10/2005 in a date field and I choose either "BN", "BA", or "BT" in a text field, I need that date of 11/10/2005 to be automatically populated in another date field on the same form.
Any help is greatly appreciated.
I'm running into a problem where I have these little square characters that are sort of subdividing a memo field. I'm wandering if this means this is a corrupted database and if not what I can do to get rid of them in one fell swoop?
I have a primary key field named [CoID] that is a number. I want that field to default to be Max([CoID])+1 , but it doesn't let me do it in the table design view. Any help would be great!
I am trying to set validation to say that the currancy entry in one field must be less than or equal to the entry in another field.
On the General tab in Table Design View on the Order table I have put a validation of <[price], I have also tried adding in the current field so [amount_to_pay]<[price]
I keep getting the following error message though.
Invalid SQL syntax - cannot use multiple columns in a column-level CHECK contraint. Can anyone help?
I have two tables. Both have a field named pt_id or Patient ID. The people open up on form to enter the patient ID among other things into that forum. They then open up the other forum to continue entering the patients information. I want it so that the Patient ID copies onto the other table with the Patient ID field.
I have 2 fields one yes/no for Deletion and one that says deletion date. Is there anyway that I can make it so that if a user marks yes for deletion that they must enter a deletion date.
I currently have it so the user selects the client but it stores the client ID. And when the user wants to adjust the current client, they double click the client field to open the client info form. before that form opens, I want them to see a message box which includes the client name so the user doesn't mistakenly update a client they don't need to. Also, i'd like the current client to be shown on the client info form.
Main form - frmMain
Client form - frmClientInfo
Client field - named Client on frmMain (but pulls from CompanyID and Company via SQL to populate the drop down)
Client field on client form - Company
Hope this is enough info. I have a feeling this is a very easy thing, i'm just having a moment. Thanks!
I know this will be simple for a lot of you out there...that's what I'm hoping for...
I want to look up an ItemPrice based on the ItemName chosen from a combo box, but I also don't want an error to pop up if no ItemName is chosen. This is the code I have written:
Private Sub ItemName_Exit(Cancel as Integer)
If IsNull(Me.ItemName) Then
MyCriteria = "[ItemName] = ' " & ItemName & " ' "
ItemPrice = DLookup("[ItemPrice]", "JTemplate", MyCriteria)
Could someone lead me in the right direction? It does look up the price perfectly, but I keep getting run-time error 2424 if I move to the next line on my continuous form and not enter anything in. BTW, I want to move to the next line so my total at the bottom of the form can finish calculating.
I've got a simple problem that I just can't figure out. I'd like to write some code that takes the date in a date field(text box) and saves it in a different date field(new text box), that way next time someone opens the Access database they can see the last date entered. Any ideas? Do I need to save the date to a table first? Does someone have some code to do this?
Edited by: idanovich on Thu Sep 21 10:02:00 EDT 2006.
I have a report that lists addresses and if a particular part of the address is not there, I want the labels following it to move up. Like what happens with the mailing labels. Does anyone know how to do this?
If I had not seen this in my database, I wouldn't have guessed it was possible:
In the Purchase Order Entry part of the database, for some very technical and lengthy product descriptions and specifications, I have a table (let's call it tbl1) for products (the ususal, with code numbers, description, etc.) and a memo field for the specs for that item. There is tbl2 (for purchase order items that is used for all types of purchase orders, aand I also have separate table for specifications (call it tbl3). Tbl3 has most of the same fields and a linking field for the PO#, a desciption field (text 255 characters) and an additional Specifications field for what might be very lengthy (more than 255 characters, thus a memo field). The data for this table is entered from a bound form (record source tbl2). When entering items that require detailed specs, I post the item information also to tbl3 (via VBA), so that I can produce and attach a Specification sheet (report) to the PO.
This has worked well for over a year. That is until yesterday. I attempted to enter a new PO with items with very lengthy specs for each item. To my surprise, many of the specs were cut off and incomplete. After several attempts at re-entry (direct to the tbl2) and a lot of hair pulling, I discovered that the field was now limited to 255 characters. I checked the field type for specifications and it still indicated it was a memo field.
Because of a time crunch, I just added another memo field, updated it to all the previous data from the earlier Specifactions field, deleted the old field, renamed the new field. I was now able to complete the PO and send it to the vendor.
Anybody have a clue how this could occur? I haven't had time to test possible causes, but my first is posting (adding a new record via VBA) where data from a memo field (special products specs) is posted to tbl3 memo field.
i am having some trouble with the following and do apologise but i dont even know how to search the forums for this type of problem, dont know what it would be called
i want to set a cmd button to copy text from a parent form to a new record in a subform (tabular view) and sort the field descending. ie, the new field to be added to the top.
i have minimal vba experience but looking to expand that knowledge so looking forward to your responses.