Dynamic Query using VBA

[jbroge]

Hello all,

I am attempting to create a dynamic query based on form driven criteria using VBA. I found a few tutorials out there, but I'm receiving an error saying

Run-time error '13':
Type mismatch.

Here is the code I am using

Private Sub cmd_RunQuery_Click()

Dim strsql As Variant
Dim qbf As QueryDef

strsql = "SELECT * FROM WorkOrderLogAll WHERE RetestLocation =" & "'" & Forms!CustomerRetestDatabaseSearch!RetestLocation & "'" And CustomerName = " & " '" & Forms!CustomerRetestDatabaseSearch!CustomerName & "'"

Set qbf = CurrentDb.CreateQueryDef("DynamicQuery", strsql)
DoCmd.OpenQuery qbf.Name


Set qdf = Nothing
Set db = Nothing


End Sub

When I try to run using just one criteria "RetestLocation" or "CustomerName", it returns results. Its only when I try to use two or more criteria I get the error.

Both fields are Short Text Data Types in the table. The form uses drop boxes for both fields.

Any help would be appreciated!

 

[spikepl]

It's because your strsql is BS.

Use http://www.baldyweb.com/immediatewindow.htm to sort out what is what.

 

[plog]

Let me help you help yourself:

What's strsql resolve to?

 

[obeylele]

Without details I have to guess RetestLocation is a number field and you are passing value as string since you are using &'&.

 

[apr pillai]

Sample code segment to create a Select Query through VBA.

dim db as database
dim qrydef as Querydef
dim strSql as string

strSql = "Select Table1.* From Table1;"
set db = currentdb
set qrydef =db.CreateQueryDefs("Query1")
qrydef.SQL = strSql
db.Querydefs.Append qrydef
db.Querydefs.Refresh

This is a SELECT Query. You cannot run it using DoCmd.OpenQuery command. Action queries are (Append, Delete, Update etc.) run with DoCmd.

Set rst = db.OpenRecordset("Query1")

is the statement used to open the Query and to process the record in it.

 

[apr pillai]

The following statement is also valid.

set rst = db.OpenRecordset(strSql)

You don't need to create Query1.

 

[jbroge]

Thanks [FONT=&quot]apr pillai. I apologize for my ignorance but this is all new to me, and what better way to learn than to ask questions.[/FONT]