DLOOKUP on a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, here goes.

I have a report based on a Crosstab Query with dates that vary.

I want to check each date to see if it is on the Holidays table
(T:Holidays), and return it's ID from that table if it is.

Across the top of the report I use the [Today] field (=date()) to set a
start date, then I use consecutive fields [Today1], [Today2], etc. to
generate the days after by setting their control sources to =[Today]+1,
=[Today]+2, etc.

The field I am using to do the check on the report is Today# where # is 1
through 14 (or I can make it [Today]+#).

Here is my contorl source for this field:
=DLookUp("[ID]","T:Holidays","[Holiday]=[Today4]")

I just get a blank field in return. I should be getting back 5 for the 4th
of July, but it's blank.

Any ideas?

THX!
 
I have a report based on a Crosstab Query with dates that vary.
I want to check each date to see if it is on the Holidays table
(T:Holidays), and return it's ID from that table if it is.

Across the top of the report I use the [Today] field (=date()) to set a
start date, then I use consecutive fields [Today1], [Today2], etc. to
generate the days after by setting their control sources to =[Today]+1,
=[Today]+2, etc.

The field I am using to do the check on the report is Today# where # is 1
through 14 (or I can make it [Today]+#).

Here is my contorl source for this field:
=DLookUp("[ID]","T:Holidays","[Holiday]=[Today4]")

I just get a blank field in return. I should be getting back 5 for the
4th
of July, but it's blank.
Click to expand...

Try this:

=DLookUp("[ID]","T:Holidays","[Holiday]=" & [Today4])

or this

=DLookUp("[ID]","T:Holidays","[Holiday] = #" & [Today4] & "#")

Tom Lake
 
Put the report's textbox name outside the " string:

=DLookUp("[ID]","T:Holidays","[Holiday]=" & [Today4])

By the way, using : in a table name is not recommended. See these Knowledge
Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
 
Tom/Ken,

THX for the replies...through trial and error I had entered both of those
and it wasn't working. I tried this

=DLookUp("[ID]","[T:Holidays]","[Holiday]=#" & date()+4 & "#")

and it suddenly started working. From that I determined that I wasn't
comparing Date to Date, but rather Date to Time. In a query much farther
back I based the [Today] value on now() instead of date().

So thanks for reaffirming my syntax was correct, now if I can just remember
to keep away from now() when I want date()!!

THX again!
 
Back
Top