Multiple where condition (numeric + string) (1 Viewer)

[Capitala]

Sorry for disturb!
I need to open a report from a current form with two where conditions; first is numeric and the second is string.
DoCmd.OpenReport ("abc"), acViewPreview , , (where condition)
Thanks in advance

 

[Pat Hartman]

It is easiest to build complex strings in variables so you can have an easy way to print them to the debug windo if you have trouble.

Dim strWhere as String

strWhere = "fld1 = '" & Me.textfield & "' AND fld2 =" & Me.numericfield

This assumes that the text field does not contain embed single quotes. If the text field is a name then the embedding of the double quotes gets more complicated. In all my apps I create a constant named QUOTE

Public Const QUOTE = """"

Then the statement would be:

strWhere = "fld1 = " & QUOTE & Me.textfield & QUOTE & " AND fld2 =" & Me.numericfield

 

[Capitala]

It is easiest to build complex strings in variables so you can have an easy way to print them to the debug windo if you have trouble.

Dim strWhere as String

strWhere = "fld1 = '" & Me.textfield & "' AND fld2 =" & Me.numericfield

This assumes that the text field does not contain embed single quotes. If the text field is a name then the embedding of the double quotes gets more complicated. In all my apps I create a constant named QUOTE

Public Const QUOTE = """"

Then the statement would be:

strWhere = "fld1 = " & QUOTE & Me.textfield & QUOTE & " AND fld2 =" & Me.numericfield

It works, thanks alot

 

[Capitala]

It works, thanks alot

Pat Hartman​

I appreciate a lot if you give me the syntax for: 1) two text 2) two numbers.
Sorry for bothering you

 

[Pat Hartman]

strWhere = "fld1 = " & QUOTE & Me.textfield & QUOTE & " AND fld2 =" & Me.numericfield
strWhere = strWhere & " AND fld3 =" & QUOTE & Me.textfield2 & QUOTE & " AND fld4 = " & Me.numericfield2
strWhere = strWhere & " AND fld5 = #" & Me.SomeDate & "#"

I use the QUOTE word because it is much easier to see where the quotes are being embedded to surround a string. The & concatenates the smaller strings that comprise the total string.

NOTE. If you are NOT in the US where m/d/y is the standard date field order, whenever you embed a STRING date value, it MUST be in US date format or the unambiguous y/m/d. So

strWhere = strWhere & " AND fld5 = #" & Format(Me.SomeDate, "mm/dd/yyyy") & "#"

create your code and put a stop on the line after you build the where clause. When the code stops, print the where clause to the imedate window. You should see something like:

fld1 = "xyz" AND fld2 = 4566 AND fld3 = "abc" AND fld4 = 876 AND fld5 = #10/23/2021#

 

[Capitala]

Great! many thanks

 

[Pat Hartman]

You're welcome