Passing Criteria to a Query From a Form

[Carol]

First, thanks in advance for your help!

I want to create a form that will pass criteria to a query
to give me the results I want for several different sets
of circumstances.

I have run into some problems, though. For instance, I
have a StartDate and EndDate field on the form. The
criteria expression I used is "Between[forms]![formname]!
[StartDate]and[forms]![formname]![EndDate]." This works
fine if I specify dates, but if not, I get no results. So
I added and "or" criteria to specify either of these
fields could also be null. That works great. But when I
do this with every field, I get no results.

Can someone tell me what I'm doing wrong or if there's a
better way to do this?

Thanks,
Carol

 

[Roger Carlson]

On my website are several small sample databases which address how to create
queries with variable parameters. Most start with "CreateQueries" Take a
look and see if they help.

 

[George Lob]

Instead of Between... use "> [Forms]![formname]![StartDate] AND <
[Forms]![formname]![StartDate]". This will work for dates or numbers.

 

[Carol]

Yes, that works as long as you enter dates in the test
boxes on the form, but if no dates are entered (you want
all dates), no results are returned.

The same holds true for evey criteria. I need to be able
to write something that takes into account that the text
box may be empty if you don't want the query results
affected by that criteria.

Did that make sense?

Thanks,

Carol

-----Original Message-----
Instead of Between... use "> [Forms]![formname]! [StartDate] AND <
[Forms]![formname]![StartDate]". This will work for dates or numbers.

First, thanks in advance for your help!

I want to create a form that will pass criteria to a query
to give me the results I want for several different sets
of circumstances.

I have run into some problems, though. For instance, I
have a StartDate and EndDate field on the form. The
criteria expression I used is "Between[forms]! [formname]!
[StartDate]and[forms]![formname]![EndDate]." This works
fine if I specify dates, but if not, I get no results. So
I added and "or" criteria to specify either of these
fields could also be null. That works great. But when I
do this with every field, I get no results.

Can someone tell me what I'm doing wrong or if there's a
better way to do this?

Thanks,
Carol

.

 

[Newbie]

Could you change you query to include an if(isnull) statement
something like (not tested)

Between if(isnull([forms]![formname]![StartDate]), CVDate("01/01/1900"),
[forms]![formname]![StartDate]) and if(isnull([forms]![formname]![EndDate]),
CVDate("31/12/2099"), [forms]![formname]![EndDate])

HTH

Yes, that works as long as you enter dates in the test
boxes on the form, but if no dates are entered (you want
all dates), no results are returned.

The same holds true for evey criteria. I need to be able
to write something that takes into account that the text
box may be empty if you don't want the query results
affected by that criteria.

Did that make sense?

Thanks,

Carol

-----Original Message-----
Instead of Between... use "> [Forms]![formname]! [StartDate] AND <
[Forms]![formname]![StartDate]". This will work for dates or numbers.

First, thanks in advance for your help!

I want to create a form that will pass criteria to a query
to give me the results I want for several different sets
of circumstances.

I have run into some problems, though. For instance, I
have a StartDate and EndDate field on the form. The
criteria expression I used is "Between[forms]! [formname]!
[StartDate]and[forms]![formname]![EndDate]." This works
fine if I specify dates, but if not, I get no results. So
I added and "or" criteria to specify either of these
fields could also be null. That works great. But when I
do this with every field, I get no results.

Can someone tell me what I'm doing wrong or if there's a
better way to do this?

Thanks,
Carol

.

 

[Treebeard]

Take the form references out of the query and pass the filters using the
OpenReport and OpenForm methods.