If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dlookup criteria
All 3 arguments for the DLookup() function need to be strings. Also, I
suspect that Department is misspelled. I'm also not sure that Between will work, you may have to enter this as = And =. =DLookUp("[units]","qrysoldunits","[salesperson]=QueryDefs![qrycontactdata]![Salesperson] And [deptartment]=QueryDefs![qrycontactdata]![Department] And ([monthandyear] Between [txtboxstartdate] And [txtboxenddate])") Also, it appears that you're trying to pull the value from another query. Does this other query only return a single value? If not, then which of the rows do you want the value from? These statements themselves will probably need to be DLookup() statements. Are the values you want here available on the report? If so, just take them from the controls on the report. Also, to use values from the controls on the report, you'll need to concatenate in the value or give the full path to the control. The latter works for forms, I haven't tried it with a report. =DLookUp("[units]","qrysoldunits","[salesperson]=QueryDefs![qrycontactdata]![Salesperson] And [deptartment]=QueryDefs![qrycontactdata]![Department] And ([monthandyear] = #" & [txtboxstartdate] & "# And [monthandyear] = #" & [txtboxenddate] & "#)") The #'s are date delimiters, just as " are string delimiters. The field you have at the end is called MonthAndYear, is it an actual date or just part of one? DLookup() will only return ONE value, the first one it finds in the record set specified by the second argument that matches the criteria in the third argument. If you are wanting to return a list, a list box may be what you're needing with a query as the Row Source. Will you give a better explanation of what you're trying to do? -- Wayne Morgan MS Access MVP "Greg" wrote in message ... i'm trying to get dlookup to pull a value from a query in the details section of my report (which is based off of a different query). this way, it will reflect the value i need it to as the groupings change. right now, i have: =DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]= [qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]= [qrycontactdata]![Department] And ([qrysoldunits]![monthandyear] Between [txtboxstartdate] And [txtboxenddate])) but all i get is an error message (#error). if i remove the bit about the dates being in the correct range: =DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]= [qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]= [qrycontactdata]![Department]) it's not an error for the 1st salesperson, but it only pulls the units value for the first record in the query, that may or may not match that salesperson -- and for every other salesperson, it's an error. i'm pretty sure that this is the right direction for my solution but i must have my criteria wrong. please help. |
#2
|
|||
|
|||
dlookup criteria
depatment was only misspelled in my post. sorry. the salesperson and
department fields are text and the monthandyear field is a date formatted mmm/yyyy. the query that i'm trying to pull from has only 4 fields: salesperson, department, monthandyear and units. it looks like this: jon doe 5 10-2005 new jon doe 4 10-2005 used jane doe 6.5 10-2005 new john smith 8.5 10-2005 new john smith 3 10-2005 used i have a report written based on a query that calculates the # of customers each salesperson sees for a user-prompted time period. that report pulls the information from a form that is set up to prompt for about 5 things, run both queries and then print the report. the report was constructed using the wizard off of only one query (qrycontactdata). i later found that i needed a 2nd query to pull the # of sold cars from a different table and that needs to be part of the report, grouped by salesperson and then department (the way the report was done using the wizard) i thought to put my textbox with this dlookup value in the 'details' section of the report so that it will pull the appropriate value for each salesperson and department as it constructs the report. i had already tried joining the queries and tables in all kinds of combinations to get it to work, without success. dlookup 'felt' like the right direction but now you're making me think otherwise. a listbox? what is this listbox of which you speak? "Wayne Morgan" wrote: All 3 arguments for the DLookup() function need to be strings. Also, I suspect that Department is misspelled. I'm also not sure that Between will work, you may have to enter this as = And =. =DLookUp("[units]","qrysoldunits","[salesperson]=QueryDefs![qrycontactdata]![Salesperson] And [deptartment]=QueryDefs![qrycontactdata]![Department] And ([monthandyear] Between [txtboxstartdate] And [txtboxenddate])") Also, it appears that you're trying to pull the value from another query. Does this other query only return a single value? If not, then which of the rows do you want the value from? These statements themselves will probably need to be DLookup() statements. Are the values you want here available on the report? If so, just take them from the controls on the report. Also, to use values from the controls on the report, you'll need to concatenate in the value or give the full path to the control. The latter works for forms, I haven't tried it with a report. =DLookUp("[units]","qrysoldunits","[salesperson]=QueryDefs![qrycontactdata]![Salesperson] And [deptartment]=QueryDefs![qrycontactdata]![Department] And ([monthandyear] = #" & [txtboxstartdate] & "# And [monthandyear] = #" & [txtboxenddate] & "#)") The #'s are date delimiters, just as " are string delimiters. The field you have at the end is called MonthAndYear, is it an actual date or just part of one? DLookup() will only return ONE value, the first one it finds in the record set specified by the second argument that matches the criteria in the third argument. If you are wanting to return a list, a list box may be what you're needing with a query as the Row Source. Will you give a better explanation of what you're trying to do? -- Wayne Morgan MS Access MVP "Greg" wrote in message ... i'm trying to get dlookup to pull a value from a query in the details section of my report (which is based off of a different query). this way, it will reflect the value i need it to as the groupings change. right now, i have: =DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]= [qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]= [qrycontactdata]![Department] And ([qrysoldunits]![monthandyear] Between [txtboxstartdate] And [txtboxenddate])) but all i get is an error message (#error). if i remove the bit about the dates being in the correct range: =DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]= [qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]= [qrycontactdata]![Department]) it's not an error for the 1st salesperson, but it only pulls the units value for the first record in the query, that may or may not match that salesperson -- and for every other salesperson, it's an error. i'm pretty sure that this is the right direction for my solution but i must have my criteria wrong. please help. |
#3
|
|||
|
|||
dlookup criteria
DLookup() will only return one value, the first one it finds that matches
the criteria in the third argument. There are other aggregate functions that may do what you want. Specifically, take a look at DCount() and DSum() to see if one of them will get the value you're looking form. DCount will count the number of records that match the criteria and DSum will give you the sum of a specified field for the records that match the criteria you specify. -- Wayne Morgan MS Access MVP "Greg" wrote in message ... depatment was only misspelled in my post. sorry. the salesperson and department fields are text and the monthandyear field is a date formatted mmm/yyyy. the query that i'm trying to pull from has only 4 fields: salesperson, department, monthandyear and units. it looks like this: jon doe 5 10-2005 new jon doe 4 10-2005 used jane doe 6.5 10-2005 new john smith 8.5 10-2005 new john smith 3 10-2005 used i have a report written based on a query that calculates the # of customers each salesperson sees for a user-prompted time period. that report pulls the information from a form that is set up to prompt for about 5 things, run both queries and then print the report. the report was constructed using the wizard off of only one query (qrycontactdata). i later found that i needed a 2nd query to pull the # of sold cars from a different table and that needs to be part of the report, grouped by salesperson and then department (the way the report was done using the wizard) i thought to put my textbox with this dlookup value in the 'details' section of the report so that it will pull the appropriate value for each salesperson and department as it constructs the report. i had already tried joining the queries and tables in all kinds of combinations to get it to work, without success. dlookup 'felt' like the right direction but now you're making me think otherwise. a listbox? what is this listbox of which you speak? |
#4
|
|||
|
|||
dlookup criteria
actually, the problem turned out to be a vba issue. i had it closing the
form automatically when the report opened, so it would not calculate anything past the first page. thanks very much for your help though. the final expression that worked was: =DLookUp("[units]","qrysoldunits","[salesperson]=""" & [Salesperson] & """ AND [Department]=""" & [Department] & """ AND ([monthandyear] Between #" & Format([txtboxstartdate],"mm/dd/yyyy") & "# And #" & Format([txtboxenddate],"mm/dd/yyyy") & "# )") and i got that from someone else on another site. i can tell you what it means and what it does, but the plethora of quotes and ampersands makes the format well out of my skill level. "Wayne Morgan" wrote: DLookup() will only return one value, the first one it finds that matches the criteria in the third argument. There are other aggregate functions that may do what you want. Specifically, take a look at DCount() and DSum() to see if one of them will get the value you're looking form. DCount will count the number of records that match the criteria and DSum will give you the sum of a specified field for the records that match the criteria you specify. -- Wayne Morgan MS Access MVP "Greg" wrote in message ... depatment was only misspelled in my post. sorry. the salesperson and department fields are text and the monthandyear field is a date formatted mmm/yyyy. the query that i'm trying to pull from has only 4 fields: salesperson, department, monthandyear and units. it looks like this: jon doe 5 10-2005 new jon doe 4 10-2005 used jane doe 6.5 10-2005 new john smith 8.5 10-2005 new john smith 3 10-2005 used i have a report written based on a query that calculates the # of customers each salesperson sees for a user-prompted time period. that report pulls the information from a form that is set up to prompt for about 5 things, run both queries and then print the report. the report was constructed using the wizard off of only one query (qrycontactdata). i later found that i needed a 2nd query to pull the # of sold cars from a different table and that needs to be part of the report, grouped by salesperson and then department (the way the report was done using the wizard) i thought to put my textbox with this dlookup value in the 'details' section of the report so that it will pull the appropriate value for each salesperson and department as it constructs the report. i had already tried joining the queries and tables in all kinds of combinations to get it to work, without success. dlookup 'felt' like the right direction but now you're making me think otherwise. a listbox? what is this listbox of which you speak? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Serch folder criteria - To be able to "OR" mulitple conditions. | Joe Sytniak | General Discussion | 1 | August 30th, 2005 04:19 PM |
How to Build Two Queries From One List Box | [email protected] | General Discussion | 0 | February 8th, 2005 04:24 PM |
Complex query criteria - desperate appeal | Ted Allen | Running & Setting Up Queries | 5 | November 17th, 2004 06:14 PM |
Query criteria conflict | dunnotar02 | Running & Setting Up Queries | 6 | November 9th, 2004 07:04 PM |
Duplicating Excel's Autofilter functionality | rgrantz | Running & Setting Up Queries | 0 | November 3rd, 2004 11:06 PM |