Improve company productivity with a Business Account.Sign Up

x
?
Solved

Where clause for one or the other

Posted on 2014-04-30
2
Medium Priority
?
226 Views
Last Modified: 2014-04-30
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]));

Open in new window

0
Comment
Question by:cansevin
2 Comments
 
LVL 6

Accepted Solution

by:
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

If CurrentProject.AllForms("MainForm").IsLoaded = True Then
  ZipExists = (CheckZip = [Forms]![MainForm]![txtZipEntered])

ElseIf CurrentProject.AllForms("MemoForm714").IsLoaded = True Then
  ZipExists = (CheckZip = [Forms]![MemoForm714]![txtZipEntered])

End Function

Open in new window



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

WHERE ZipExists(ZipsCA.Zip) = True;
0
 
LVL 50

Assisted Solution

by:Dale Fye
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

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question