SQL Error 13

mischa

Registered User.
Local time
Today, 17:02
Joined
Jul 25, 2013
Messages
63
I need some help from you guys.
I got the following SQL which I would like to run with vba but it gives an error (errror 13 type mismatch).

The SQL does work when I use it in a query but not in vba:banghead:.

Code:
    Dim SQLobsoleteperc As String
    
    SQLobsoleteperc = "SELECT tblDateObs.DateObs, tblStatisticRef.RefPercObsolete" & _
        "FROM tblDateObs LEFT JOIN tblStatisticRef ON tblDateObs.DateID = tblStatisticRef.DateID" & _
        "GROUP BY tblDateObs.DateObs, tblStatisticRef.RefPercObsolete" & _
        "HAVING (((tblDateObs.DateObs) Like " * " & [Forms]![frmObsolescenceMain]![txtObsolescenceUpdate] & " * "));"
 
That SQL does not work anywhere. What you think is the same SQL might run in a query, but that SQL you are compiling does not. Here's how you can verify that--spit out SQLObsoleteperc and paste it in a query.

That's how you need to debug SQL in VBA -- spit out the code and see that it actually is what you think it is and produces what you think it does.

With that said, the issue is this line:

"HAVING (((tblDateObs.DateObs) Like " * " & [Forms]![frmObsolescenceMain]![txtObsolescenceUpdate] & " * "));"

You've got an * hanging out in no man's land. You start the line with a double quote, then after the Like you have another double quote which the systems takes as the end of that first double quote. You either need to escape that double quote, or change it to a single quote. The same issue appears with that other *.
 
G'd evening,
You also may want to add spaces to your concatenated string like this
Code:
SQLobsoleteperc = "SELECT tblDateObs.DateObs, tblStatisticRef.RefPercObsolet[B][COLOR="Red"]e [/COLOR][/B]" & _
        "FROM tblDateObs LEFT JOIN tblStatisticRef ON tblDateObs.DateID = tblStatisticRef.DateI[B][COLOR="red"]D [/COLOR][/B]" & _
        "GROUP BY tblDateObs.DateObs, tblStatisticRef.RefPercObsolet[B][COLOR="red"]e [/COLOR][/B]" & _
        "HAVING (((tblDateObs.DateObs) Like " * " & [Forms]![frmObsolescenceMain]![txtObsolescenceUpdate] & " * "));"
G'd luck
 
Thank you Estuardo!

I did what you said but I'm still getting the same error:banghead:
 
Thank you Estuardo!

I did what you said but I'm still getting the same error:banghead:
Read the answer from plog and when you still get error show the sql string with the change.
 
it should even be VBA throwing the error, not SQL... Seeing as this is a VBA coding problem not an SQL problem.

Hint: " starts or terminates a string
 
Me and mornings..
Didn't see the post from blog at first :$ (Shame on me).
Thank you very much Plog for the elaborate answer which I think resolved my issue:D.
 
The SQL code works fine after deleting both "*" (which I didn't need anyways)

I'm figuring out how I can run this SQL and use DFIRST to extract one value and show it in a textbox. I know RunSQL does not work because it is not an action query, which is why I used the DoCmd.OpenQuery statement.

Code:
    DoCmd.OpenQuery SQLobsoleteperc
    ObsRefPerc = DFirst("DateObs", SQLobsoleteperc)
    Me.txtMaxRefPerc.Value = ObsRefPerc

The code gives me the following error message:
Error 7874, object cannot be found

If I remove the OpenQuery statement then I'm getting the following error message:
Error 3163, the field is to small for the information which has to be shown
Which I find strange because if I make a query with the same SQL and use the same DFirst statement it does work properly.
 
Use DLookup instead.. Not DFirst..

PS: You can use Domain functions on Queries too !

EDIT: I just re-read your post. Using a Domain function does not require you to open the Query.. To make life easier; You can save the SQL in the Query Builder with the name SQLobsoleteperc and just use as..
Code:
Me.txtMaxRefPerc = DLookup("DateObs", "SQLobsoleteperc")
Or if you intend to use VBA, you need to use Recordset.. A good documentation I normally refer to for learning and using Recordset : www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners
 
Last edited:
Changed DFirst to DLookup but I'm still getting the same error.
After running the SQL only one row will be returned with a simple integer (between 0 and 100).
 
If you have the SQL in place, try something like:
Code:
Dim rs as Dao.Recordset
set rs = Currentdb.openrecordset("YourSQL")
Debug.print rs!YourColumn
debug.print rs!AnotherColumn
rs.close
set rs = nothing
 

Users who are viewing this thread

Back
Top Bottom