• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Dlookup syntax with dates

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.


0
terpsichore
Asked:
terpsichore
  • 7
  • 4
  • 3
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
There were missing brackets and extra parens ...

mx
0
 
Nick67Commented:
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 :)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
 
terpsichoreAuthor 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...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
 
terpsichoreAuthor 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...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
As far as the end of the criteria:


and [srprojmgr_id] = " & Me.txtspm_id)

or

and [srprojmgr_id] = " & Me("txtspm_id") )
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"My understanding of dlookup is that the field name needs to come first"
Correct

Then:

DLookup("sumamount", "[Vendor_Invoice_query_teamexpense]", "[invoicedate] between #" & [Forms]![Project_Reports1]![txtDateFrom] & "# AND #" & [Forms]![Project_Reports1]![txtdateto] & "# and [srprojmgr_id] = " & Me("txtspm_id") )
0
 
terpsichoreAuthor Commented:
it's giving me an error: "enter parameter value" and then shows "me"...
0
 
Nick67Commented:
Sorry mang
_______________________________________
<"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.
__________________________________________

That's flat out wrong in Access 2003 on every one of my machines, at least
Sample to demo attached
This is wrong under Access 2003 on all of my machines as well
______________________________________________________
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.
________________________________________________________

Every single bound control I have keeps the field name as the control name
Unbound controls get hungarian notation.
Nothing gets confused.

MIND YOU, Access 2003 and later
Pre-2003 was never my thing
Test.mdb
0
 
terpsichoreAuthor 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]
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)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
0
 
Nick67Commented:
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
:)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now