Problem with sql syntax with JOIN and WHERE in vba (2 Viewers)

Scarlet_Pimp

New member
Local time
Today, 21:32
Joined
Aug 6, 2025
Messages
5
I'm trying to do a lookup in vba with sql where I search for the value of a textbox in two linked tables.

Code:
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.
 
when Planning.Bestelbon is of type "Text" then try

Code:
sql = "SELECT Bestelbon, Productnaam FROM Planning INNER JOIN Tanks "  &  _ "ON Planning.Productcode = Tanks.Productcode WHERE Planning.Bestelbon = '"  & Me.txtSearch & "'"
 
No I've tried that already and it didn't work
 
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
 
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,

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
 
if Planning.Bestelbon is not of type "Text", try a type conversion for Me.txtSearch

Code:
sql = "SELECT Bestelbon, Productnaam FROM (Planning INNER JOIN Tanks " & _<br><br>    "ON Planning.Productcode = Tanks.Productcode) WHERE Planning.Bestelbon = " & clng(Me.txtSearch) & ""
 
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

Code:
 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

Code:
Me.txtResult = DLookup("ProductNaam", "YourQueryName", "Planning.Bestelbon = '" & Me.txtSearch & "'")
 
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
 
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.
 

Users who are viewing this thread

Back
Top Bottom