DLookup with Mulitple AND statements

[Snowflake68]

Hi have a text box on a form that needs to display the name of a Shift from a lookup table based upon the values in 8 different combo boxes containing a time value (eg. 08:00:00 formatted to Short Time, so displays only 08:00).

I can get the criteria to work separately using the following 8 lines but I need to join them together using AND as all values need to match to display the correct Shift description.

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_AmShiftStart] =[cboAmShiftStart]")

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_AmShiftEnd] =[cboAmShiftEnd]")

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_PmShiftStart] =[cboPmShiftStart]")

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_PmShiftEnd] =[cboPmShiftEnd]")

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_Fri_AmShiftStart] =[cboFriAmShiftStart]")

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_Fri_AmShiftEnd] =[cboFriAmShiftEnd]")

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_Fri_PmShiftStart] =[cboFriPmShiftStart]")

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_Fri_PmShiftEnd] =[cboFriPmShiftEnd]")

I have tried to join them together using the first two lines as an example (see below) but it returns the wrong Shift Description so I must have the syntax wrong.

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_AmShiftStart] = [cboAmShiftStart]" And "[Set_AmShiftEnd] = [cboAmShiftEnd]")

Can someone help me with the correct syntax please.

Thanks in advance
Caz

 

[arnelgp]

remove the " between the AND.
also qualify your controlname with form name:

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_AmShiftStart] = Forms!FormName![cboAmShiftStart] And [Set_AmShiftEnd] = Forms!FormName![cboAmShiftEnd] ... ")

 

[Snowflake68]

Problem solved.

I managed to sort this out myself. I think I was trying to over complicate it to begin with after reading so many different posts but all sorted now.
Only needed the quotes at the beginning and end of the criteria as the AND was part of the criteria. Doh!

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_AmShiftStart] =[cboAmShiftStart] And [Set_AmShiftEnd]= [cboAmShiftEnd] And [Set_PmShiftStart] =[cboPmShiftStart] And [Set_PmShiftEnd] =[cboPmShiftEnd] And [Set_Fri_AmShiftStart] =[cboFriAmShiftStart] And [Set_Fri_AmShiftEnd] =[cboFriAmShiftEnd] And [Set_Fri_PmShiftStart] =[cboFriPmShiftStart] And [Set_Fri_PmShiftEnd] =[cboFriPmShiftEnd]")

Hope this helps others though.

 

[Snowflake68]

remove the " between the AND.
also qualify your controlname with form name:

=DLookUp("[Shift]","[LookupPreSetShifts]","[Set_AmShiftStart] = Forms!FormName![cboAmShiftStart] And [Set_AmShiftEnd] = Forms!FormName![cboAmShiftEnd] ... ")

Thanks arnelgp. I did manage to sort it out but would like to thank you for taking the time out to assist me. I didnt need to qualify the form name it works perfectly without it.