Can quotes be used in IIF statement?

[lwilly]

I am running a query that is trying to get parameters from a form. It bases the selection criteria on the value of an option group. I believe it needs to include quotes to make it a valid criteria. This is the statement I am using:
IIf(([Forms]![frmSalesHistory]![ReportOptions]=3),"1-0-25-3000",(Not Like "1-0-25-3000&quot)

I have also created a function that would return the correct selection criteria but it included quotes I did not want.

Can anyone help?

 

[kathryn]

How about

IIf(([Forms]![frmSalesHistory]![ReportOptions]=3),"1-0-25-3000","Not Like '1-0-25-3000'&quot

The only change is that I added a set of quotes around your Not Like statement.

If this doesn't work, could you give us an idea of what happens when you try it?

 

[lwilly]

Kathryn, using the statement either way will return 0 records (this is incorrect). When I put the statement in code and look at the results in the debug window I get this, "Not Like 1-0-25-3000". What I need is Not Like "1-0-25-3000"

I had to modify the statement a little bit to make it work in code. Here is what I used

Dim strAdtrend As String

If [Forms]![frmSalesHistory]![ReportOptions] = 3 Then
strAdtrend = "1-0-25-3000"
Else
strAdtrend = "Not Like ""1-0-25-3000"
End If
AdtrendSales = strAdtrend

 

[kathryn]

So did changing it that way in the code solve your problem?

 

[lwilly]

No, the result includes the quotes before the Not Like. I think this is my problem, but I don't know how to eliminate the first quotes. This is what is returned:
"Not Like "1-0-25-3000" and this is what I need:
Not Like "1-0-25-3000" any ideas.

 

[kathryn]

Your function is returning a text string, which is why you are getting quotes, I would guess.

At this point, the way I would handle this is to build the entire SQL in code and execute it in code. The beauty of code is the breakpoint, which will allow you to see exactly where your SQL syntax problem lies.

Does the form with the option group have a button which you push which runs the query? If so, you could modify that code to create the query and run it in code.

Does that sound like an option?

 

[lwilly]

That would be an option, but how will it effect performance? I understand (and might be mistaken)that when running a query in code it has to compile every time it runs, where an Access query will compile the first time it runs and remains compiled until modifications are made.

 

[kathryn]

You are correct, I believe. How about if you only use the code to pass the query the parameter it needs?

Something like this

***begin example
dim qdf as querydef

set qdf = currentdb.QueryDefs("YourQueryName&quot

If [Forms]![frmSalesHistory]![ReportOptions] = 3 Then
qdf.Parameter("YourParameterName&quot = "1-0-25-3000"
Else
qdf.Parameter("YourParameterName&quot = "Not Like '1-0-25-3000'"
End If

***end example

 

[lwilly]

Oh BTW since I haven't said thanks for your help yet, thanks, it is greatly appreciated.

I tried running the query in code but my parameter still shows up with quotes in the wrong places. Using an SQL statement may not be the most efficient but it will work.

 

[kathryn]

OK, you need to set a break point on the line where you set the parameters.

When the code breaks, go to the immediate window (Ctrl-G) and type:

?qdf.parameters("yourparametername&quot

that will tell you exactly what is being passed. You should be able to fix the parameter then.

Feel free to post the code you are using.