DLOOKUP not working (1 Viewer)

monvani

Registered User.
Local time
Today, 01:55
Joined
Jun 21, 2006
Messages
23
I have a qry based on a table with field [f6] as a date. For example, one record has [f6] with a value of 10/14/2008 (format m/d/yyyy).

I am building a field in that query with a dlookup into tblCalendar which looks like this:

dateStart* dateFinish* Month
8/29/2008 9/25/2008 200809
9/26/2008 10/30/2008 200810
10/31/2008 11/27/2008 200809
*both fields have date format as as m/d/yyyy

I want my query to go into tblCalendar and return the [Month] value where the [f6] falls between, or is equal to, datestart/datefinish.

My statement is:
Expr2: DLookUp("month","tblCalendar","DateStart <= " & [f6] & " And DateFinish >= " & [f6])

My query returns nothing.
 

Attachments

  • db1.zip
    32.6 KB · Views: 112

Rabbie

Super Moderator
Local time
Today, 06:55
Joined
Jul 10, 2007
Messages
5,906
You need to post your linked table as well
 

MSAccessRookie

AWF VIP
Local time
Today, 02:55
Joined
May 2, 2008
Messages
3,428
I have a qry based on a table with field [f6] as a date. For example, one record has [f6] with a value of 10/14/2008 (format m/d/yyyy).

I am building a field in that query with a dlookup into tblCalendar which looks like this:

dateStart* dateFinish* Month
8/29/2008 9/25/2008 200809
9/26/2008 10/30/2008 200810
10/31/2008 11/27/2008 200809
*both fields have date format as as m/d/yyyy

I want my query to go into tblCalendar and return the [Month] value where the [f6] falls between, or is equal to, datestart/datefinish.

My statement is:
Expr2: DLookUp("month","tblCalendar","DateStart <= " & [f6] & " And DateFinish >= " & [f6])

My query returns nothing.


It would seem that your database is linked to an Excel File that was not included in the package. I will need a copy of the file before I can test anything. (Scaled down or mocked up will probably be OK) .

NOTE: It seems that Rabbie beat me to it and already advised you to post the data. We await your reply.
 
Last edited:

boblarson

Smeghead
Local time
Yesterday, 23:55
Joined
Jan 12, 2001
Messages
32,059
It would appear you need to modify your DLookup to deal with dates:

Expr2: DLookUp("month","tblCalendar","DateStart <=#" & [f6] & "# And DateFinish >=#" & [f6] & "#")
 

monvani

Registered User.
Local time
Today, 01:55
Joined
Jun 21, 2006
Messages
23
One other thing...when using [f6] in the query, why does the query not automatically know that the field is a date?
 

boblarson

Smeghead
Local time
Yesterday, 23:55
Joined
Jan 12, 2001
Messages
32,059
The query knows, but the DLookup doesn't.
 

Users who are viewing this thread

Top Bottom