Solved

Where clause for one or the other

Posted on 2014-04-30
2
201 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 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 47

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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