Dlookup Issue in Query Criteria

[Guest]

Hi All,

My Problem seems pretty simple but i just dont seem to be able to get my
head round it.
I am trying to use Dlookup in a query criteria to find a specific number
from a table dependent on todays date as the lookup reference.

The 2 Strings i have tried are (Baring in mind i have never used dlookup):

1) DLookUp("Date()","[Vehicensus DaysTbl]","[Tuesday]")
2) DLookUp("Date()","[Vehicensus DaysTbl]![Todays Date]","[Tuesday]")

My table has a list of dates on the far left (Titled "Todays Date") and 4
other columns, 1 being "Tuesday".
I was hoping it would be able to find todays date in the table using
"Date()" and return the value from "Tuesday" in the criteria.

All suggestions would be greatfully recieved but i know about as much about
sql as i do rocket engineering, so if you could keep it resonable simple
would be amazing!

Thanks v much!
Dave

 

[Guest]

Hi,
I think you are going about this wrong.
DLookup is to lookup information and you specify a field, table and criteria.
BUT...you don't even need a table for this. You shouldn't store any of these
values as you can just calculate them at runtime.
If you want to filter by the day name then look at the WeekDayName function,
it will return you the name of the current date if you set it up correctly.
Maybe you can collaborate a little more what it is you are trying to
achieve, because as mentioned I think you are going about this the wrong way.
HTH
GOod luck

 

[Van T. Dinh]

Both of the expressions you posted are incorrect but I am not sure what to
suggest. The first argument needs to be a Field name in your Table and I am
not sure "Date()" is a Field name in your Table. The first argument
certainly cannot be the VBA function Date(). The 3rd argument (optional)
certainly looks incorrect also.

Without seeing your database, I may be wrong but there are a number of
potential problems in your Table design as it sounds like you have repeating
groups of Fields in your Table.

Suggest you post your Table structure (Field names, what each Field suppose
to store, etc ...) so that others can check and suggest alternatives ...