CreateQueryDef with Where Clause

[xlStar]

I am trying to extract result from an existing query but with or without where clause.

The query is if myDepartment is * then display everything else just display certain department name.

The codes below seems to work BUT only where myDepartment contain only one word i.e. "Sales" but when it contained a space i.e. "Human Resources" the query is displaying everything. "Sales" just lists Sales only



WHRstr = "WHERE [DEPARTMENT] >= '" & myDepartment & "'"
QRYstr = "[Proposals - " & dbChoice(i) & "]"

If myDepartment = "*" Then
SQLstr = "SELECT * FROM " & QRYstr
Else
SQLstr = "SELECT * FROM " & QRYstr & WHRstr
End If
Set TBLobj = db.CreateQueryDef("tempQ", SQLstr)


What have I done wrong?

 

[dhookom]

It looks like you need to add a space to the left of "WHERE..." or right of "]".

Duane
Hook'D on Access
MS Access MVP

 

[FancyPrairie]

xlStar,

Diagnosing a problem like yours is pretty simple to discover. Simply place the word Stop above the last line of code:

Stop
Set TBLobj = db.CreateQueryDef("tempQ", SQLstr)


Then execute the code. Execution of the code will stop when it encounters the word stop. Then, in debug's Immediate window, type:

?SQLstr

You should then be able to see the contents of SQLstr. You could then copy and paste the results in a new query. Note: press F5 to continue execution, press F8 to stop through the code 1 line at a time.