DLookup HELP! Run-Time Error 2471

[PaulDixie]

Hi all,

Can you help me? I'm trying to work a DLookup in Access VBA, where I want to lookup the value of two strings (concatenated together) and find a matching value in a table.

This is part of the VBA but can't work out why the DLookup Isn't working - it recognises the 'UniqueRef' string okay with the right value, but it gives me Run-Time Error 2471.

Can someone tell me where I'm going wrong? I've looked all over the net and tried everything, but nothing works...

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UniqueRef = ACOINo & ACOINo2

If DLookup("[ApiUniqueRef]", "[qryApiAPCAData]", "[ApiUniqueRef]='" & UniqueRef & "'") Then

'Confirm Record
Do
RV = WD_SendKey(100, "@2")
Loop Until RV = 0
RV = 4

Else

Msgbox "No Record"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Thanks
Paul

 

[pedie]

If UniqueRef is a Number then change your syntax to " & UniqueRef & " and not '" & UniqueRef & "'

 

[PaulDixie]

Thanks. What would be the whole peice of code then? I have changed the code, but it tells me I have a "Compile error Expected: list separator or)" code I have now is...

If DLookup("[ApiUniqueRef]", "[qryApiAPCAData]", "[ApiUniqueRef] = " & UniqueRef & ") Then

 

[pedie]

if ACOINo & ACOINo2 are going to be numbers only then define it as long

Dim UniqueRef As Long
If DLookup("ApiUniqueRef", "qryApiAPCAData", "ApiUniqueRef = " & UniqueRef & "") Then

else may be string...
Dim UniqueRef As String
If DLookup("ApiUniqueRef", "qryApiAPCAData", "ApiUniqueRef = '" & UniqueRef & "'") Then

try this code.

[/FONT]
[FONT=Courier New]Dim UniqueRef As Long
UniqueRef = ACOINo & ACOINo2
If DLookup("ApiUniqueRef", "qryApiAPCAData", "ApiUniqueRef = " & UniqueRef & "") Then
Do
RV = WD_SendKey(100, "@2")
Loop Until RV = 0
RV = 4
Else
MsgBox "No Record"
End If[/FONT]
[FONT=Courier New]

 

[Norie]

<s>Where do you get the error?</s>
Misread question.