x
Solved

# Where clause for one or the other

Posted on 2014-04-30
Medium Priority
226 Views
I am using the below where clause in a query. I want it to work if there is a value in MainForm OR MemoForm714. There is only going to be a value in one of the two. It keeps asking me what the second value is in a dialoge box. Is there a way to say "one OR the other" so it doesn't look for both? Thanks!

``````WHERE (((ZipsCA.Zip)=[Forms]![MainForm]![txtZipEntered] Or (ZipsCA.Zip)=[Forms]![MemoForm714]![txtZipEntered]));
``````
0
Question by:cansevin

LVL 6

Accepted Solution

Dulton earned 1000 total points
ID: 40032175
Probabaly not with inline logic like that. You'd probably have to bury that kind of logic in a vba function in a module.

First you'd have to check and find out which form was open, and then only check that value.

``````Public Function ZipExists(CheckZip AS String) AS Boolean

ZipExists = false

ZipExists = (CheckZip = [Forms]![MainForm]![txtZipEntered])

ZipExists = (CheckZip = [Forms]![MemoForm714]![txtZipEntered])

End Function
``````

then in your where clause you just call it like this:

WHERE ZipExists(ZipsCA.Zip) = True;
0

LVL 50

Assisted Solution

Dale Fye earned 1000 total points
ID: 40032222
I have a function that would probably work for that.  Save the function in a public Code module.  Then Call it in your query, something like:

WHERE ZipsCA.Zip = fnMax([Forms]![MainForm]![txtZipEntered], [Forms]![MemoForm714]![txtZipEntered])

Since the function accepts a Variant Array, it can accept NULLs (which it ignores).
``````Public Function fnMax(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) > myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMax = myVal

End Function
``````
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.