Using AND and OR in strLinkCriteria

P

Paul B.

Hello all....

I need to use AND and OR to search two fields in 4 tables.

"[tblUnit1.Region]='" & Me.Region & "' AND [tblUnit1.Station]='" &
Me.Station & "'_
OR "[tblUnit2.Region]='" & Me.Region & "' AND [tblUnit2.Station]='" &
Me.Station_ & "'

I haven't figured out the proper coding, most likely my quotes are out of
place or missing altogether.

Any help would be appreciated.

Cheers
 
M

Marshall Barton

Paul said:
I need to use AND and OR to search two fields in 4 tables.

"[tblUnit1.Region]='" & Me.Region & "' AND [tblUnit1.Station]='" &
Me.Station & "'_
OR "[tblUnit2.Region]='" & Me.Region & "' AND [tblUnit2.Station]='" &
Me.Station_ & "'

I haven't figured out the proper coding, most likely my quotes are out of
place or missing altogether.
Click to expand...


The quotes and the square brackets are definitely wrong.
You should also use some parenthesis to clarify the
calculation order.

Assuming all the table fields are Text fields:

strLinkCriteria = "([tblUnit1].[Region]='" & Me.Region _
& "' AND [tblUnit1].[Station]='" & Me.Station _
& "' ) OR ([tblUnit2].[Region]='" & Me.Region _
& "' AND [tblUnit2].[Station]='" & Me.Station_ & "' )"

If any of the fields are a numeric type, then remove its
correspnding apostrophes.
 
J

Judy Ward

Paul,

I hesitated to answer because I am definitely not an MVP. But I do build
queries in forms quite a bit. I find it helpful to keep it simple rather
than elegant. I can see in your code below that you are missing a quote
before the OR and you have an underscore after the last Me.Station.

The reason you even need the single quotes is because Me.Region is a String
type, so I separate those from the regular double quotes (I use a single
quote inside double quotes separately). This is what I would use:

"[tblUnit1.Region]=" & "'" & Me.Region & "'" & " AND [tblUnit1.Station]=" &
"'" & Me.Station & "'" _
"OR [tblUnit2.Region]=" & "'" & Me.Region & "'" & " AND [tblUnit2.Station]="
& "'" & Me.Station & "'"

I also typically save this criteria in a String variable (strSQL) and use a
Debug.Print strSQL to see if what I have built is really what I intended.

I hope this helps,
Judy
 
V

Van T. Dinh

Judy

Your answers / responses / contributions in these *public* are always
welcome ... whether MVP or not ...

Note also that "MVP" are simply awards from Microsoft in recognition of
participation / contributions to the forums and most MVPs, if not all,
participate and response to questions long before being rewarded with the
MVP award ...
 
Top