DLookUp

[Anhjan]

In writing a formula in my query...and I keep getting an error...

Projected: IIf(Weekday(Now())-1=0,0,[SALES_PIECES_WTD]/DLookUp("[day_cum_perc]","tbl Day Percent","[Week]=[Week_Number] and Weekday(Now())-1"))

The error I keep getting is with the DLookUp part. I have the Week_Number declared in the query, but when I run the query I get an error "MS Access can't find the name [Week_Number] you entered in the expression" If I hard code the week number it runs the query correctly...

Any ideas how to get around this...

Thanks,
Jan

 

[sambo]

Here are two possible problems...

1. I don't think that Dlookup recognizes Table Names that are more than 2 words. If I'm reading your Code right, then you are looking up a value from a table comprised of 3 words, "tbl Day Percent"... Try renaming your table to, tblDayPercent.
I think it is a good idea to use the single word naming convention.

2. You wrote "[Week]=[Week_Number] ... " for your criteria. This looks to me like you are using a field as criteria to look up another field. This is kind of confusing, and I don't think it will work. You would usually use a Form Control Value as criteria.
For Example... "[Week] = '" & Me.txtWeekNumber & "'"

Good luck..