Problem with sql syntax with JOIN and WHERE in vba

[Scarlet_Pimp]

I'm trying to do a lookup in vba with sql where I search for the value of a textbox in two linked tables.

sql = "SELECT Bestelbon, Productnaam FROM Planning INNER JOIN Tanks " & _ "ON Planning.Productcode = Tanks.Productcode WHERE Planning.Bestelbon = " & Me.txtSearch & ""

But I can't seem to get the syntax right. Any help would be much appreciated.

 

[bbfromgb]

when Planning.Bestelbon is of type "Text" then try

sql = "SELECT Bestelbon, Productnaam FROM Planning INNER JOIN Tanks "  &  _ "ON Planning.Productcode = Tanks.Productcode WHERE Planning.Bestelbon = '"  & Me.txtSearch & "'"

 

[Scarlet_Pimp]

No I've tried that already and it didn't work

 

[bbfromgb]

put a breakpoint in your code after the sql instruction and inspect sql string in the direct window.
copy the output in the SQL section of the Query designer and run as a query

 

[Scarlet_Pimp]

I actually got this code from a query that does work. But when I try to adapt it to vba I now get error "3464 data mismatch in criteria"

Here is my full code,

Private Sub cmdSearch_Click()

 

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim sql As String

  

    If Nz(Me.txtSearch.Value, "") = "" Then

        MsgBox "Geef een bestelbon in !!", vbExclamation

        Debug.Print "Geef een bestelbon in !!"

        Exit Sub

    Else

 

    sql = "SELECT Bestelbon, Productnaam FROM (Planning INNER JOIN Tanks " & _

    "ON Planning.Productcode = Tanks.Productcode) WHERE Planning.Bestelbon = " & Me.txtSearch & ""

        

    Set db = CurrentDb

    Set rs = db.OpenRecordset(sql)

 

    Me.txtResult.Value = rs!Productnaam

 

    rs.Close

  

    End If

  

    Set rs = Nothing

    Set db = Nothing

 

End Sub

 

[bbfromgb]

if Planning.Bestelbon is not of type "Text", try a type conversion for Me.txtSearch

sql = "SELECT Bestelbon, Productnaam FROM (Planning INNER JOIN Tanks " & _<br><br>    "ON Planning.Productcode = Tanks.Productcode) WHERE Planning.Bestelbon = " & clng(Me.txtSearch) & ""

 

[isladogs]

Saying 'it didn't work' is of no help for anyone trying to troubleshoot.

As already stated, if the Bestelbon field is text then you need single quotes as delimiters. You don't need the Bestelbon field in the SELECT clause

 sql = "SELECT Productnaam FROM Planning INNER JOIN Tanks" & _

    " ON Planning.Productcode = Tanks.Productcode WHERE Planning.Bestelbon = '" & Me.txtSearch & "'"

OR if you just want to populate a single field based on Me.txtSearch, you could create a query based on that SQL and use a DLookup instead

Me.txtResult = DLookup("ProductNaam", "YourQueryName", "Planning.Bestelbon = '" & Me.txtSearch & "'")

 

[Scarlet_Pimp]

Damn, you were right about the single quotes. It is a number stored as short text.

But first time I typed the single quotes in the wrong order.

It works now!

Thx a lot

 

[Pat Hartman]

But I can't seem to get the syntax right. Any help would be much appreciated.

When you have trouble with SQL Syntax, use the QBE. You can then either use the querydef directly or convert the SQL to a string.