DLookup Using Variable

[Gregorys05]

Hi All,
I have the below code that i am trying to referance a query within a Dlookup, now the code works fine when i enter the name of the query into the dlookup(Instead of the SQL) but when using the SQL variable it dosn't recognise that its a query what do i have to do for the Dlookup to recognise it??

Thanks

Private Sub Add_Click()
Dim VersionName As String
Dim SQL As String
VersionName = Forms!Replace!NewImageaddress & "V1"
SQL = "SELECT tblreplace.fldScanName FROM tblreplace WHERE (((tblreplace.fldScanName = " & VersionName & "));"
If DLookup(1, SQL, True) Then
MsgBox "Value there"
Else
MsgBox "Not There"
End If
End Sub

 

[Norie]

You can't use SQL for the 2nd argument of a DLookUp it has to be the name of a table or query.

One thing you could try would be to create a parameter query out of your SQL.

That shouldn't be too hard, just use Forms!Replace!NewImageaddress & "V1" in the criteria.

Or if all you want to do is find out if the value from the form is already in the fldScanName field you could use DCount.

If that returns 0 it's not in the table.

Something like this perhaps:

=DCount("[fldScanName]", "tblreplace", "[fldScanName]='" & Forms!Replace!NewImageaddress & "V1'")

 

[Gregorys05]

I used the Dcount function in the end thanks for that it worked a treat