We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Dlookup syntax with dates

terpsichore
terpsichore asked
on
Medium Priority
368 Views
Last Modified: 2012-05-11
Dear experts -

This is probably relatively straightforward, but is driving me nuts...

I have a query and am trying to do a lookup and feed it several criteria... this is what I'm currently using, calling from a report [spm_id] is a field in the report:
=DLookUp("[Vendor_Invoice_query_teamexpense]","sumofamount","[invdate] between #" & [Forms]![Project_Reports1]![txtdatefrom) & "# AND #" & [Forms]![Project_Reports1]![txtdateto] & "# and [srprojmgr_id] = " & [me]![spm_id])

I suspect it has something to do with the use of quotation marks, and I've spent an hour now trying to research, to no avail...

MANY THANKS IN ADVANCE.


Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Try this:

=DLookup("[Vendor_Invoice_query_teamexpense]", "sumofamount", "[invdate] between #" & [Forms]![Project_Reports1]![txtDateFrom] & "# AND #" & [Forms]![Project_Reports1]![txtdateto] & "# and [srprojmgr_id] = " & Me![spm_id])

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
There were missing brackets and extra parens ...

mx
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
What @mx has there looks good.
You had a ) instead of a ]

<[Forms]![Project_Reports1]![txtdatefrom)>

Reports can be squirrly and intellisense WILL lie
<[me]![spm_id])>

If spm_id isn't a control on the report itself, this will look good and just not work.
Hidden controls are good :)
Moreover, if that value <[me]![spm_id])> needs to change with each record, your hidden control needs to go on a part of your report that changes with each record.

Report gotchas.  Been there, done that!

Nick67 :)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"If spm_id isn't a control on the report itself, this will look good and just not work."

That's not really the case.  On a Report, Access will look for ![spm_id] as a field in the underlying table or query. So, in fact it will work.  No need for hidden controls whatsoever.

What IS important on a report is IF ... you plan on referring to a Control, then you need to Name that control differently from the default of the Control Source Name.

So ... if you have a control that is brought onto the report with a Control Source of spm_id, initially it looks like this:

Name:  spm_id
Control Source :spm_id

The Name need to be changed to something like

txtspm_id, otherwise Access WILL get confused.

mx

Author

Commented:
Almost there - you guys are great - the following is almost producing the right result, except it is not finding the right spm_ID for that grouping (this appears in the footer of the major grouping:

=DLookUp("sumamount","[Vendor_Invoice_query_teamexpense]","[invoicedate] between #" & [Forms]![Project_Reports1]![txtDateFrom] & "# AND #" & [Forms]![Project_Reports1]![txtdateto] & "# and [srprojmgr_id] = " & [txtspm_id])

I tried using the me keyword and refering a text box called txtspm_id, I tried simply [spm_id] -- the report is showing me the right value for the FINAL grouping, but not the others for some reason...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
But that is a different DLookup() expression, not the one you originally posted.

Original:

=DLookUp("[Vendor_Invoice_query_teamexpense]","sumofamount",

This one:

DLookUp("sumamount","[Vendor_Invoice_query_teamexpense]",

You have the field and table names reversed.  

mx

Author

Commented:
I renamed the field, that was confusing me. The sum field in the query is now called sumamount. My understanding of dlookup is that the field name needs to come first, not the table/query name, so I fixed that as well...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
As far as the end of the criteria:


and [srprojmgr_id] = " & Me.txtspm_id)

or

and [srprojmgr_id] = " & Me("txtspm_id") )
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
it's giving me an error: "enter parameter value" and then shows "me"...
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
this was my final solution, which is working - thanks to you guys for pointing me in the right direction.

=DSum("amount","[Vendor_Invoice_query_teamexpense]","[invoicedate] between #" & [Forms]![Project_Reports1]![txtDateFrom] & "# AND #" & [Forms]![Project_Reports1]![txtdateto] & "# and [srprojmgr_id] = " & [Report]![txtspm_id])

[instead of a dlookup on a query that had an embedded sum, I used dsum - this also may have had something to do with it]
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
You're right Nick, sorry.  I forgot that does not work on a Report,  only a Form.  I've never understood why either.  Intellisense shows the Fields as properties of the Report/Form, which is the case.  But for whatever reason, you can't do that in a report.

mx
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
No Biggie.

I don't have a couple hundred purple invisible report controls for nothing.
Maybe it doesn't work because the placement of the control matters on a report.
If you put a control in the report header/footer it gets the value of the first record and that's it.

With a form, the control picks up up the value of the current record--but that an non-existent scenario in a report.
I'm not sure.
I just know it drove me bonkers
:)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.