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

Scarlet_Pimp

New member
Local time
Today, 17:25
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 & "'"
 
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
 
Damn, you were right about the single quotes. It is a number stored as short text.

Many of the things we call numbers are not really numbers, in that they have no ordinal or cardinal significance. In many cases they are really encoding systems. ISBN and Zip codes are examples. These should be stored in a column of Short Text data type therefore. In expressions such values must be delimited by quotes characters.

Although it makes no difference in this case I nearly always represent literal quote characters as a pair of contiguous double quote characters rather than single quote characters, e.g. "LastName = """ & txtLastName & """". This allows for string expressions which contain apostrophes. My name in its original non-anglicized form, Cináed O'Siridean, is an example. It's more likely that a string expression will contain apostrophes than literal double quotes characters, particularly personal names.

Where a string expression might contain single and/or double quotes characters, for comparison purposes the latter can be replaced with a high ascii character such as the tilde character by calling the Replace function in the expression.
 

Users who are viewing this thread

  • plog
Back
Top Bottom