how to set query criteria from form input

[John Big Booty]

Sorry, I'm working off the top of my head here. I'll go fire up Access

 

[John Big Booty]

OK just had a play in my sand box DB, and

Like "*" & [forms]![Search Customer]![txtsearch] & "*"

SHOULD return all result if field [forms]![Search Customer]![txtsearch] is left blank.

 

[mafhobb]

Yes it does. I wonder why that was not the case when I tested it earlier. I must not have saved the form/query or might have had it closed instead of opened in the background.


Anyway. That part of the query works. I am now adding a time frame by adding

>Date()-[Forms]![Search Customer ID]![cmbTimeFrame] or >Date()-[Forms]![Search Customer ID]![cmbTimeFrame] is null

to the query in design mode.

I am also having some trouble with this as it tells me that the expression is typed wrong or it is too complex to evaluate. Funny thing is, if both boxes (txtsearch and cmbtimeframe) are empty, the query works and if both have values it does too. It is only when txtsearch is full and cmbtimeframe is empty that I get the error...

I'll keep playing with this until I figure it out.

Would you have any suggestions for a "dumbed down" good read on all of these. I feel like I am always getting stuck in syntax errors.

Thanks for your help!

Mafhobb

 

[John Big Booty]

I think you will need to read up on the IsNull() Function.

 

[Toeying]

I guess you can use the IsNull property of the text box. but if you have put the criteria into the query builder, then you might have to create another query for that situation...
I'll probably use a message box to ask the user to enter something... but of course this depends on ur design... cheers

 

[John Big Booty]

I guess you can use the IsNull property of the text box. but if you have put the criteria into the query builder, then you might have to create another query for that situation...
I'll probably use a message box to ask the user to enter something... but of course this depends on ur design... cheers

He can use the IsNull() function in the criteria of his query, my comments was directed toward the way in which he had structured the criteria and specifically the IsNull() function portion.

 

[mafhobb]

Hi

Well I have tried, but the syntax is just too much for me.

I have changed the criteria in the query to ">Date()-[Time Period in Days?]" and that asks the question to the user. Not quite as user friendly, but it works.

Thanks to all of you for your help!

Mafhobb