Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-17-2008, 09:39 AM   #1
monvani
Newly Registered User
 
Join Date: Jun 2006
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
monvani is on a distinguished road
DLOOKUP not working

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.
Attached Files
File Type: zip db1.zip (32.6 KB, 75 views)

monvani is offline   Reply With Quote
Old 10-17-2008, 10:30 AM   #2
Rabbie
Super Moderator
 
Rabbie's Avatar
 
Join Date: Jul 2007
Location: Returning to Scotland
Posts: 5,906
Thanks: 110
Thanked 76 Times in 68 Posts
Rabbie is a jewel in the rough Rabbie is a jewel in the rough Rabbie is a jewel in the rough
Re: DLOOKUP not working

You need to post your linked table as well
__________________
The best solution is the simplest one that meets all requirements.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Rabbie is offline   Reply With Quote
Old 10-17-2008, 10:42 AM   #3
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Re: DLOOKUP not working

Quote:
Originally Posted by monvani View Post
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.

__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by MSAccessRookie; 10-17-2008 at 10:43 AM. Reason: Add the note:
MSAccessRookie is offline   Reply With Quote
Old 10-17-2008, 11:13 AM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: DLOOKUP not working

It would appear you need to modify your DLookup to deal with dates:

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 10-17-2008, 11:48 AM   #5
monvani
Newly Registered User
 
Join Date: Jun 2006
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
monvani is on a distinguished road
Re: DLOOKUP not working

That worked Bob, thanks!
monvani is offline   Reply With Quote
Old 10-17-2008, 11:53 AM   #6
monvani
Newly Registered User
 
Join Date: Jun 2006
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
monvani is on a distinguished road
Re: DLOOKUP not working

One other thing...when using [f6] in the query, why does the query not automatically know that the field is a date?
monvani is offline   Reply With Quote
Old 10-17-2008, 12:08 PM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: DLOOKUP not working

The query knows, but the DLookup doesn't.


__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookup problems mdcory Forms 1 04-02-2005 11:18 PM
Help with DLookup - not working jdp103 Forms 1 02-26-2005 08:14 PM
[SOLVED] Dlookup Problem grahammm Forms 2 02-14-2005 05:54 AM
DLookup with multiple criteria Florens Reports 2 01-17-2003 12:40 PM
DLookup Problems ShadyOne Forms 2 07-14-2000 10:02 AM




All times are GMT -8. The time now is 03:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World