Multi Criteria DLookup syntax problem.

[jonnyboy101]

Hi all, I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.

Here is what I have so far:

1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )

2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )

BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.

eg/

=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')

and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.

Please can anyone help sort this out and tell me how to put in a third criteria too?


Thanks in advance, Jon:banghead:

 

[pr2-eugin]

If it is a Numeric type do not enclose it inside single quotes..

DLookUp("[timedata]![id]","timedata",  "[processdone] = " &  Forms![Mainform]![p11] & " AND [BGSnum] = " &  Forms![Mainform]![BGS])

DLookup General Syntax usage.

 

[jonnyboy101]

Great, that works a treat.

So am I right in thinking that a third criteria would be?

DLookUp("[timedata]![id]","timedata",  "[processdone] = " &  Forms![Mainform]![p11] & " AND [BGSnum] = " &  Forms![Mainform]![BGS] " AND [ABCcriteria] = " &  Forms![Mainform]![XYZvalue])

 

[pr2-eugin]

Depends if ABCcriteria field is a Number, Text or Date. If it is a Number the above should work no problem ! Just make sure you concatenate the Conditions..

DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = " & Forms![Mainform]![BGS] & " AND [ABCcriteria] = " & Forms![Mainform]![XYZvalue])

 

[jonnyboy101]

Fantastic cheers!

 

[pr2-eugin]

No worries ! Good Luck !

 

[jonnyboy101]

Ok, so it worked perfectly when on my message box test but just doesnt want to work when put into VBasic.

BGSnum, processdone and startstop are all numbers

I feel its always a syntax problem

Private Sub update_test_Click()
  
 Dim dbs As Database
    Dim qdf As QueryDef
  
CurrentDb.Execute "UPDATE timedata " _
        & "SET timestampstop = now() " _
        & "WHERE BGSnum = "[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = " & Forms![Mainform]![BGS] & " AND [timedata]![startstop] = -1

End Sub