Solved

Where clause for one or the other

Posted on 2014-04-30
2
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 250 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 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

710 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