DLookup with Multiple Criteria

[RandyWatson]

Hello all,

I am struggling with adding multiple criteria with a DLookup formula. Here is the initial criteria:

=DLookUp("Date","qryDatePosition","DOWPosition='1Saturday' ")

I am trying to add two additional criteria.

"Month" = Forms![frmCalendar]![cboMonth]
"Year" = Forms![frmCalendar]![cboYear].

Ultimately it should return the 1st Saturday of a Month and Year specified in frmCalendar but I am getting lost after the first AND. Very frustrating :banghead:.

Any help is greatly appreciated!

Thank you!

 

[almahmood]

Hi,

Use this:

=DLookUp("Date","qryDatePosition","DOWPosition='1S aturday' AND Month=" & Forms![frmCalendar]![cboMonth] & " AND Year=" & Forms![frmCalendar]![cboYear])

Please note: If Month or Year is a string datatype, you have to pass the criteria from form within apostrophe ('') like you have mentioned for DOWPosition.

 

[RandyWatson]

Hi,

Use this:

=DLookUp("Date","qryDatePosition","DOWPosition='1S aturday' AND Month=" & Forms![frmCalendar]![cboMonth] & " AND Year=" & Forms![frmCalendar]![cboYear])

Please note: If Month or Year is a string datatype, you have to pass the criteria from form within apostrophe ('') like you have mentioned for DOWPosition.

Thanks for the quick response. When I tried the formula it returned a #Error message. Do you know what I am doing wrong?

=DLookUp("Date","qryDatePosition","DOWPosition='1S aturday' AND Month=" & Forms![frmCalendar]![cboMonth] & " AND Year=" & Forms![frmCalendar]![cboYear])

 

[almahmood]

Okay, is any of your control has null value? Could you please post the datatype of the criteria fields?

 

[RandyWatson]

Actually, I retyped your formula and it works perfectly. The error was on my end.

Thank you so much for the help!!