Dlookup Issue in Query Criteria

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

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.

DLookUp("Date()","[Vehicensus DaysTbl]","[Tuesday]")

My table has a list of dates on the far left 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
 
Dave K said:
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.

DLookUp("Date()","[Vehicensus DaysTbl]","[Tuesday]")
Click to expand...

The first parameter should be the name of the field whose value you're
looking for.
There's no field in the Vehicensus DaysTbl named Date().
My table has a list of dates on the far left 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.
Click to expand...

You can only search one column per DLookup. What column (field) would you
like to search?
If it's called Monday, you can do this:

DLookUp("[Tuesday]","[Vehicensus DaysTbl]","[Monday] = #" & Date() & "#")

This will return the value in the field Tuesday that corresponds to the
field Monday which has today's
date in it.

Tom Lake
 
The DLookUp
DLookUp("[FieldName]","[TableName]","[Criteria]")

I don't understand why you are using DLookUp to look for todays date, just
use Date(), that will return the current date.

If you want to return a value from a table, when the filer will be the
current ate
DLookup("[Tuesday]","[Vehicensus DaysTbl]","[FieldName] = #" & Date() & "#")
 
Your a *STAR* Ofer!

Thanks a lot!
Davey

Ofer said:
The DLookUp
DLookUp("[FieldName]","[TableName]","[Criteria]")

I don't understand why you are using DLookUp to look for todays date, just
use Date(), that will return the current date.

If you want to return a value from a table, when the filer will be the
current ate
DLookup("[Tuesday]","[Vehicensus DaysTbl]","[FieldName] = #" & Date() & "#")

--
\\// Live Long and Prosper \\//


Dave K said:
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.

DLookUp("Date()","[Vehicensus DaysTbl]","[Tuesday]")

My table has a list of dates on the far left 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
Click to expand...
Click to expand...
 
Glad I could help, Good luck

--
\\// Live Long and Prosper \\//


Dave K said:
Your a *STAR* Ofer!

Thanks a lot!
Davey

Ofer said:
The DLookUp
DLookUp("[FieldName]","[TableName]","[Criteria]")

I don't understand why you are using DLookUp to look for todays date, just
use Date(), that will return the current date.

If you want to return a value from a table, when the filer will be the
current ate
DLookup("[Tuesday]","[Vehicensus DaysTbl]","[FieldName] = #" & Date() & "#")

--
\\// Live Long and Prosper \\//


Dave K said:
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.

DLookUp("Date()","[Vehicensus DaysTbl]","[Tuesday]")

My table has a list of dates on the far left 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
Click to expand...
Click to expand...
Click to expand...
 
On Thu, 5 Jan 2006 13:31:03 -0800, Dave K <Dave
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.

DLookUp("Date()","[Vehicensus DaysTbl]","[Tuesday]")
Click to expand...

This will attempt to look up today's date from the table where the
field [Tuesday] contains TRUE. It makes no sense, sorry!
My table has a list of dates on the far left and 4 other columns, 1 being
"Tuesday".
Click to expand...

If you have a field named Tuesday, your table structure is almost
certainly wrong.
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.
Click to expand...

Then you have it backwards. The first argument is the value which will
be returned from the table; the second argument (which you have
correct) is the name of the table or query; the third argument is a
valid SQL WHERE clause without the word WHERE. If your "extreme left"
field is named Datelist, try

=DLookUp("[Tuesday]", "[Vehicensus DaysTbl]", "[Datelist] = #" &
Date() & "#")

The # is a required delimiter for Date/Time fields.

John W. Vinson[MVP]
 
Back
Top