Help with between formula in access

[ariel20029]

Hi,
I am trying to write an iif statement in an access query if the date from the CATS is between the start and end date from TABLE return WKDate else nothing. Can you please help.
Sharon

CATS!:iif([CATS]![Date] >=

![Start] and< =

![end]

![WK Date] )

 

[Joe4]

Try:

[COLOR=#333333]iif(([CATS]![Date] >= [Table]![Start]) and ([/COLOR][COLOR=#333333][CATS]![Date][/COLOR][COLOR=#333333]<=[Table]![end]),[Table]![WK Date],"")[/COLOR]

 

[jorismoerings]

Hi,

I don't think an IIF would come to the correct answer.
try this instead:

week: DLookUp([wk date];"table";[date] Between

![start] And

![end])

make sure you have add Table to the query as well.

Other option would be to use the date and use the DatePart function to get the weeknumber

week: DatePart("ww",[CATS]![DATE])

Hope this helps.

 

[Joe4]

I was so focused on the construct issues with the formula, I didn't look close enough at what was being asked.
jorismoerings is correct. You cannot "lookup" a value with an IIF statement. An IIF statement will only work like that if the two tables are already joined within the query.
You would need to use a DLOOKUP formula like shown, or possibly create a query between the two tables (doesn't need to be joined), but put the conditions in the WHERE clause.