Why does this query not work? Sample attached.

[CarysW]

It's been a good few months that I've been away from the world of Access, and I get rusty quite quickly!!

I can't understand what's wrong with this query though - if I open it via the query, and choose to leave all of the parameters blank, then it works - it returns all possible outcomes. If I input one or more values in the parameter boxes, I get nothing - no error, just no data returned.

If I try to run the query from the form (with code), I get nothing returned whether I choose a parameter or not.

Is it something silly and stupid that I'm missing? Have I done something wrong somewhere? Is it all completely wrong??

 

[CarysW]

Also wanted to add, it seems to have dropped several fields in the resultant datasheet, what on earth has happened there?

 

[DCrake]

Test your query with only 1 condition and build up from there. It may be you need to include Or Is Null on the condition line.

 

[CarysW]

It may be you need to include Or Is Null on the condition line.

I have done this, it has all jumped to the end (presumed this was normal)

 

[DCrake]

Take a look at this link it may give you a clearer understanding of variable and parameters.

 

[CarysW]

Just read your sig DCrake so have also uploaded my sample DB in 2000-2003 format.

 

[wilpeter]

CarysWW: just read your sig and love that you're a fan of Douglas Adams. Sorry, this is not on topic.

 

[CarysW]

CarysWW: just read your sig and love that you're a fan of Douglas Adams. Sorry, this is not on topic.

Not many people get that.

 

[CarysW]

Hmmm... :-( I've had a good look at your example DCrake and I just don't get it! It doesn't seem to relate to my query....

 

[CarysW]

I'm still no further along with this. It's possibly something really easy that I'm not getting....

 

[DCrake]

Your main problem is that you have too many parameters involved. What you are actually asking for is that each field must match what you have entered in the form as criteria or that the contents are null. It does not contend with items that are not null and do not match what has been entered in the form.

What you need to do is to build up a where condition based on on the items in the form that you ave set criteria against.

For Example:

strWhere = ""

If [Textbox1] Is Not Null Then
StrWhere = strWhere "[Field1]=" & Me.TextBox1 & " And "
End If

If [Textbox2] Is Not Null Then
StrWhere = strWhere "[Field2]=" & Me.TextBox2 & " And "
End If

If [Textbox3] Is Not Null Then
StrWhere = strWhere "[Field3]=" & Me.TextBox3 & " And "
End If

etc

Then drop the last "And"

strWhere = Left(strWhere,Len(strWhere)-5)

This will give you a where condition based only of the fields on the form that have been populated.

There are many examples of this on the forum.