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

Query open, If Or And condition not working

P: 114
Have a couple of conditions going on that I can't seem to get working correctly.

Have 1 Form only with 3 text boxes that are used in a query, when a command button is clicked using 'Event Procedure'. The VBA Sub that runs (code below) checks to see if 1 or more text boxes had something entered, and if OK the list box row source should be poplulated with a query results. Only need 1 or more of the 3 boxes filled in. For some reason it wants all 3 filled in. Not sure why this is not working the way I want!

If Me.WhatLastName.Value = "" Or IsNull(Me.WhatLastName) And _
Me.WhatFirstName.Value = "" Or IsNull(Me.WhatFirstName) And _
Me.WhatMedRecNo.Value = "" Or IsNull(Me.WhatMedRecNo) Then

MsgBox ("Please enter 1 or more Search values")
GoTo Exit_SearchQuery_Click
Me.SelectPrintItems.RowSource = "Lukup_Query"

End If

Also when this Form is opened for the 1st time the list box is populated with the complete table. Row Source type = Table/Query, Row Source = Lukup_Query. List box should not populate untill after the button has been clicked and the If test passes with no error.

Should I change the Row Source to a VBA coded SQL Query, or is there a way to do this with the button and list box properties!
Dec 31 '06 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,494
You really mean :
Expand|Select|Wrap|Line Numbers
  1. If (Me.WhatLastName.Value = "" Or IsNull(Me.WhatLastName)) And _
  2. (Me.WhatFirstName.Value = "" Or IsNull(Me.WhatFirstName)) And _
  3. (Me.WhatMedRecNo.Value = "" Or IsNull(Me.WhatMedRecNo)) Then ...
This ensures the ORs are processed first and the ANDs only connect the results of those ORs.
In which case you should really code it something like :
Expand|Select|Wrap|Line Numbers
  1. If Nz(Me.WhatLastName,"")="" _
  2. And Nz(Me.WhatFirstName,"")="" _
  3. And Nz(Me.WhatMedRecNo,"")="" Then ...
This shorter version would also work :
Expand|Select|Wrap|Line Numbers
  1. If Nz(Me.WhatLastName,"") & _
  2.    Nz(Me.WhatFirstName,"") & _
  3.    Nz(Me.WhatMedRecNo,"")="" Then ...
Dec 31 '06 #2

P: 114
Works, Thank You.

Have any idea why the list box is loading 1st thing when the form is opened? Is that because the list box is tied to a Query and if so should I remove that query reference and place the SQL query in my VBA code under the _Click code?
Dec 31 '06 #3

Expert Mod 15k+
P: 31,494
Probably because you have the Row Source property set to the query in the design of the form itself.
You haven't given any indication of when your code runs so it's hard to guess what you're expecting to happen.
What do you want to happen exactly?
Dec 31 '06 #4

Post your reply

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