Solved

Where clause for one or the other

Posted on 2014-04-30
2
208 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
Dale Fye 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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