Access 2013: run-time error 13, type mismatch

[DKaur]

Hello all,

Not sure exactly what is wrong with my code, it keeps coming back with error 13 (type mismatch). This is a simplified version of my code with the line causing the error highlighted:

Sub Loop_Criteria_UnionQUERY()
'define names for the database, query table and the SQL string
Dim db As Database
Dim qdf As QueryDef
Dim def As TableDef
Dim strName As String
Dim strSQLcriteriaquery As String
Dim strUnionQuery As String

'look into the current database
Set db = CurrentDb

'Loop through all tables in the file.
For Each def In db.TableDefs
'This if statement ensures the inbuilt tables are not affected.
If Left(def.Name, 4) <> "MSys" Then
strName = def.Name & "Query2"
If CurrentDb.TableDefs(def.Name).Fields.Count = 8 Then
strSQLcriteriaquery = "SELECT [" & def.Name & "].[a], [" & def.Name & "].[b, [" & def.Name & "].[c], [" & def.Name & "].[d], 'null' as [e], 'null' as [f], 'null' as [g], [" & def.Name & "].[h], [" & def.Name & "]. FROM [" & def.Name & "] WHERE ((([" & def.Name & "].b)='test1' Or ([" & def.Name & "].b)='test2' Or ([" & def.Name & "].b)='test3' Or ([" & def.Name & "].b)='test4' Or ([" & def.Name & "].b)='test5'));"
Else
strSQLcriteriaquery = "SELECT [" & def.Name & "].[a], [" & def.Name & "]., [" & def.Name & "].[c], [" & def.Name & "].[d], [" & def.Name & "].[e], [" & def.Name & "].[f], [" & def.Name & "].[g], [" & def.Name & "].[h], [" & def.Name & "]. FROM [" & def.Name & "] WHERE ((([" & def.Name & "].b)='test1' Or ([" & def.Name & "].b)='test2' Or ([" & def.Name & "].b)='test3' Or ([" & def.Name & "].b)='test4' Or ([" & def.Name & "].b)='test5')) OR ((([" & def.Name & "].[f]) Like " * test6 * ") AND (([" & def.Name & "].[g])='test7'));"
End If
strUnionQuery = strUnionQuery & Left(strSQLcriteriaquery, (Len(strSQLcriteriaquery) - 1)) & " UNION ALL "
'Create a new query: CreateQueryDef("name to assign to the query table", SQLstringname)
Set qdf = db.CreateQueryDef(strName, strSQLcriteriaquery)
'Open the new query
DoCmd.OpenQuery strName
End If
Next def

strUnionQuery = Left(strUnionQuery, (Len(strUnionQuery) - 10)) & ";"
Set qdf = db.CreateQueryDef("UnionQuery", strUnionQuery)
DoCmd.OpenQuery "UnionQuery"

End Sub

Any help/insight would be appreciated.

Many thanks,
D

 

[TerryHogarth21]

When you debug this in the Immediate window for strSQLcriteriaquery - what does the string come up with?

Can you paste that back here to the forum?

 

[Kyle123]

Shouldn't the wildcards be in the string?

"].[f]) Like " * test6 * ") AND

Presently you are trying to multiply strings

 

[DKaur]

Terry, I'm quite new to this all and not sure what you mean by 'debug in the immediate window'. Happy to take direction though on how to do this.

 

[DKaur]

Kyle, that was exactly the problem - so simple!! I had copied the SQL string from a working query and completely forgot about the quotation marks. They have been converted to apostrophes and it works now!

Thank you both for your help

 

[TerryHogarth21]

Control + G brings up the immediate window while you are in the VB Editor.

If you step through this by pressing F8 in the VB editor it'll run line by line.

It's command driven so if you type in ?now it would give you the date/time .

If you type in ?strSQLcriteriaquery it will give you the results of your variable that VBA is building when you are debugging. Or you could hover over your variable as well when running through line by line but that doesn't help too much since you can't copy/paste it.

It likely is related to what Kyle is suggesting in his post for the wildcards but getting the string result you will see it easier so you know where to fix it.

 

[DKaur]

Thanks Terry, just tested the immediate window now. Brilliant tip, will definitely use this in the future.