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

Capitala

Member
Local time
Today, 06:32
Joined
Oct 21, 2021
Messages
58
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

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2002
Messages
35,925
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.
Code:
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
Code:
Public Const QUOTE = """"
Then the statement would be:
Code:
strWhere = "fld1 = " & QUOTE & Me.textfield & QUOTE & " AND fld2 =" & Me.numericfield
 

Capitala

Member
Local time
Today, 06:32
Joined
Oct 21, 2021
Messages
58
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.
Code:
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
Code:
Public Const QUOTE = """"
Then the statement would be:
Code:
strWhere = "fld1 = " & QUOTE & Me.textfield & QUOTE & " AND fld2 =" & Me.numericfield
It works, thanks alot
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2002
Messages
35,925
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#
 

Users who are viewing this thread

Top Bottom