Syntax for Open Report with multiple criteria

[Adam McReynolds]

I am trying to use multiple criteria in the Where clause of OpenReport. What is the proper syntax?

I know I am using the quotes wrong. Can someone please help?

Here are 2 attempts at the same code. Which one is correct?:

reworkWhere = "ReworkTech = " & Me.cmb_tech_daily And Not IsNull(ReworkTimeOut)
reworkWhere = "ReworkTech = " & Me.cmb_tech_daily And ReworkTimeOut = Not IsNull

Here is my complete code. How do I write this in correctly?:

Dim strWhere
Dim reworkWhere
Dim repairWhere
Dim qcWhere

reworkWhere = "ReworkTech = " & Me.cmb_tech_daily And Not IsNull(ReworkTimeOut)
repairWhere = "RepairTech = " & Me.cmb_tech_daily And Not IsNull(RepairTimeOut)
qcWhere = "QC_Tech = " & Me.cmb_tech_daily And Not IsNull(QC_TimeOut)

strWhere = "' & reworkWhere & " Or " & repairWhere & " Or " & qcWhere & '"
DoCmd.OpenReport "RPT_MODULE_REPAIRS", acViewReport, , strWhere

Thanks for the help.

 

[pbaldy]

The first is closer, but both are wrong as you never restart the string. Try

reworkWhere = "ReworkTech = " & Me.cmb_tech_daily & " And Not IsNull(ReworkTimeOut)"

 

[pbaldy]

By the way, the SQL method to test for Null would be

... And ReworkTimeOut Is Not Null

 

[namliam]

Your whole where is a string, i.e. :
reworkWhere = "ReworkTech = " & Me.cmb_tech_daily & "And Not IsNull(ReworkTimeOut) "

etc...

Ah yes good catch on the sql is null syntax paul

 

[Adam McReynolds]

The first is closer, but both are wrong as you never restart the string. Try

reworkWhere = "ReworkTech = " & Me.cmb_tech_daily & " And Not IsNull(ReworkTimeOut)"

Thanks for the reply. I made the changes but I still get error 13 on this line:

strWhere = "' & reworkWhere & " Or " & repairWhere & " Or " & qcWhere & '"

Any suggestions? Thanks again.

 

[pbaldy]

I'd use this to see the final result of the variable:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, you wouldn't want to wrap the whole bit in quotes, as it's throwing everything off. Presuming each variable is built correctly:

strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere

You're probably also going to have a parentheses problem, mixing And & Or like that.

 

[Adam McReynolds]

I'd use this to see the final result of the variable:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, you wouldn't want to wrap the whole bit in quotes, as it's throwing everything off. Presuming each variable is built correctly:

strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere

You're probably also going to have a parentheses problem, mixing And & Or like that.

Used the debug and played with a bit and here is my final working code:

Private Sub cmb_tech_daily_Click()
Dim strWhere
Dim reworkWhere
Dim repairWhere
Dim qcWhere

reworkWhere = "ReworkTech = '" & Me.cmb_tech_daily & "' And Not IsNull(ReworkTimeOut)"
repairWhere = "RepairTech = '" & Me.cmb_tech_daily & "' And Not IsNull(RepairTimeOut)"
qcWhere = "QC_Tech = '" & Me.cmb_tech_daily & "' And Not IsNull(QC_TimeOut)"


strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_MODULE_REPAIRS", acViewReport, , strWhere
Debug.Print strWhere
End Sub

Thanks for the help!

 

[pbaldy]

No problem. Is it really working (correct records selected)? Sometimes Access will get the mix of and/or right, more often it won't. This might work better, presuming it's the desired logic:

strWhere = "(" & reworkWhere & ") Or (" & repairWhere & ") Or (" & qcWhere & ")"

 

[namliam]

By the way, the SQL method to test for Null would be

... And ReworkTimeOut Is Not Null

Dont forget this part Adam