By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

OpenForm WhereCondition 'Or' Syntax

P: 8
Hi,
I am using access 2003 and have some code that opens a form and filters it using the werecondition, the code works apart from the "Measure" i need it to filter it for any of the 3 options 'loft' Or 'boards' Or 'Loft & Boards' which filters fine when manually entered into the filter but am having problems finding the correct syntax for it to put this in automatically, the code i have currently which all works other than the "Measure =" is:
Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2.  
  3. stDocName = "Main Table"
  4. DoCmd.OpenForm stDocName, acNormal, , "[Postalcode] Like '" & PostalAreatxt & "*' and Measure= & 'loft' & " Or " & 'boards' & " Or " & 'Loft & Boards' and [Cancel Contract?] =False and IsNull([Completion Date]) and IsNull([Fit Scheduled Date1]) and IsNull([Fitters Names1])"
  5.  
Mar 10 '10 #1

✓ answered by TheSmileyCoder

Welcome to Bytes

That is because each OR condition must contain its own True/false evaluation.
Example of Bad:
X=1 or 2 or 3
Example of Good:
X=1 or X=2 or X=3

Using the _ will allow you to break up those long code lines to several lines to increase readability and maintain overview
You also do not need & between single quotes '
I have also added a set of parenthesis around your Or statements, as I presume they are meant as a group.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm stDocName, acNormal, , _
  2.     "[Postalcode] Like '" & PostalAreatxt & "*' and (" & _
  3.     "Measure= 'loft' Or " & _
  4.     "Measure= 'boards' Or " & _
  5.     "Measure= 'Loft & Boards') and " & _
  6.     "[Cancel Contract?] =False and " & _
  7.     "IsNull([Completion Date]) and " & _
  8.     "IsNull([Fit Scheduled Date1]) and " & _
  9.     "IsNull([Fitters Names1])"

Finally to add some extra confusion you could also do:
Expand|Select|Wrap|Line Numbers
  1. Measure In ('loft','boards','Loft & Boards)
(You might have to replace , with ; based on regional settings)

Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Welcome to Bytes

That is because each OR condition must contain its own True/false evaluation.
Example of Bad:
X=1 or 2 or 3
Example of Good:
X=1 or X=2 or X=3

Using the _ will allow you to break up those long code lines to several lines to increase readability and maintain overview
You also do not need & between single quotes '
I have also added a set of parenthesis around your Or statements, as I presume they are meant as a group.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm stDocName, acNormal, , _
  2.     "[Postalcode] Like '" & PostalAreatxt & "*' and (" & _
  3.     "Measure= 'loft' Or " & _
  4.     "Measure= 'boards' Or " & _
  5.     "Measure= 'Loft & Boards') and " & _
  6.     "[Cancel Contract?] =False and " & _
  7.     "IsNull([Completion Date]) and " & _
  8.     "IsNull([Fit Scheduled Date1]) and " & _
  9.     "IsNull([Fitters Names1])"

Finally to add some extra confusion you could also do:
Expand|Select|Wrap|Line Numbers
  1. Measure In ('loft','boards','Loft & Boards)
(You might have to replace , with ; based on regional settings)
Mar 10 '10 #2

P: 8
This worked perfectly, thanks so much this had been frustrating me for days!
Mar 10 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
As close to a perfect answer as you're likely to find anywhere.

Nice one Smiley :)
Mar 10 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.