Dlookup Date range

redxtb

Registered User.
Local time
Today, 16:37
Joined
Feb 11, 2014
Messages
16
Trying to look up fiscal month from another table. looking at Event Date and fiscal table has a start and end date. Where is my formula incorrect??

fMonth: DLookUp("[fiscalMonth]","tblFiscalDates","[EVENT DATE]<=[tblFiscalDates]![datWeekend] And [EVENT DATE]>=[tblFiscalDates]![datWeekStart]")

thanks in advance!!
 
"[EVENT DATE]<=[tblFiscalDates]![datWeekend] And [EVENT DATE]>=[tblFiscalDates]![datWeekStart]"

There are 2 elements to constructing a string.

Literals -- Everything between quotes is literally put into the string as it appears

Variables -- the value of the variable is put into the string

You have no variables in your crriteria string because its all one long literal string. You need to determine which of those pieces are to be used as variables (without quotes around them) and which pieces are literals (with quotes around them)
 
you can use BETWEEN
 
ok so i tried this....


fMonth: DLookUp("[fiscalMonth]","tblFiscalDates",[EVENT DATE] Between " [tblFiscalDates]![datWeekend]" And " [tblFiscalDates]![datWeekStart]")

and got an #error
 
That's close, but is this code:

[EVENT DATE] Between

a variable?

Also, when you want to combine literals and variables you need to use ampersands between them:

X=7
"The variable X is equal to " & X
 
thanks Plog and Ranman!! i appreciate the help but i am not picking up what you are laying down!!

[EVENT DATE] has 364 days in it and needs to know which Fiscal month it belongs to.
[tblFiscalDates]![datWeekend] and [tblFiscalDates]![datWeekStart] is in a table that has fiscalmonth fiscalweek the date that starts the week and the date that ends the week.

fMonth: DLookUp("[fiscalMonth]","tblFiscalDates",[EVENT DATE] Between " [tblFiscalDates]![datWeekend]" & And & " [tblFiscalDates]![datWeekStart]")

i know it shouldn't be this hard!!
 
Still not right;


fMonth: DLookUp("[fiscalMonth]","tblFiscalDates","[EVENT DATE] Between " & [tblFiscalDates]![datWeekend] & " And " & [tblFiscalDates]![datWeekStart] &" ")

I can't remember but you might also need to surround the dates with #1/1/1900#
 
now i get a parameter Value for the datWeekEnd & datWeekStart and get Syntax Error (missing Operator) in Query Express [EVENT DATE] Between and '. and This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to the variables.

Here is a question. tblFiscalDates should not be part of the query correct? There should be no join.....

i know i look like a real dummy on this, but i have done many complicated queries and vba. i have never been able to wrap my head around this dlookup function. i know when this clicks it will be very valuable.

Thank you all for being patient and helpful!!!
 
Why use DLookup at all? Simply "join" the two tables...
Code:
Select
From yourtable, tblFiscalDates
where [Event date] between [tblFiscalDates].[datWeekend]  And [tblFiscalDates].[datWeekStart]

Note, using spaces in column names is a bad idea or atleast bad practice.
 
fMonth: DLookUp("[fiscalMonth]","tblFiscalDates","#" & Format([EVENT DATE],"mm\/dd\/yyyy") & "# Between [datWeekStart] And [datWeekend]")
 
Thanks Namliam and arnelgp!! Both of these worked!!
 

Users who are viewing this thread

Back
Top Bottom