Query with an option

[Gary Toth via AccessMonster.com]

I have a query that searches for classes held between a date range:

SELECT EMP_NAMES.LNAME, EMP_NAMES.FNAME, TRAINING.CLASS_ATTD, TRAINING.DATE_ATTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (((TRAINING.CLASS_ATTD) Like ([Enter it the name of the class])) AND ((TRAINING.DATE_ATTD) Between DateValue([Enter the beginning date]) And DateValue([Enter the cutoff date])));

Currently the user MUST enter a date in the date range boxes. What I would like to happen is: If the user hits the enter key (puts nothing in the date boxes) the query continues and reports information containing class information for ALL dates. I'm sure this is a relatively simple IFF statement but, I'm kinda new at this stuff and don't quite know how to state it.

Thanks for your help in advance.

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...9255f3976e2fa7
*****************************************

[Salad]

Gary Toth via AccessMonster.com wrote:

I have a query that searches for classes held between a date range:

SELECT EMP_NAMES.LNAME, EMP_NAMES.FNAME, TRAINING.CLASS_ATTD, TRAINING.DATE_ATTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (((TRAINING.CLASS_ATTD) Like ([Enter it the name of the class])) AND ((TRAINING.DATE_ATTD) Between DateValue([Enter the beginning date]) And DateValue([Enter the cutoff date])));

Currently the user MUST enter a date in the date range boxes. What I would like to happen is: If the user hits the enter key (puts nothing in the date boxes) the query continues and reports information containing class information for ALL dates. I'm sure this is a relatively simple IFF statement but, I'm kinda new at this stuff and don't quite know how to state it.

Thanks for your help in advance.

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...9255f3976e2fa7
*****************************************

Here is an example. In the where, I test for start and end. If start
and end are not null, the range is selected. Otherwise it checks for
the status of start and end to see if it is null. Thus there are 4
criteria; Start/End filled in, Start filled in only, End filled in only,
or both weren't filled in.

SELECT Table1.DateField
FROM Table1
WHERE (Table1.DateField) Between [Enter Start] And [Enter End]) OR _
(Table1.DateField>=[Enter Start] And [Enter End] Is Null) OR _
([Enter Start] Is Null And Table1.DateField<=[Enter End]) OR _
([Enter Start] Is Null And [Enter End] Is Null);

[John Winterbottom]

"Gary Toth via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:34******************************@AccessMonste r.com...

I have a query that searches for classes held between a date range:

SELECT EMP_NAMES.LNAME, EMP_NAMES.FNAME, TRAINING.CLASS_ATTD,
TRAINING.DATE_ATTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (((TRAINING.CLASS_ATTD) Like ([Enter it the name of the class])) AND
((TRAINING.DATE_ATTD) Between DateValue([Enter the beginning date]) And
DateValue([Enter the cutoff date])));

Currently the user MUST enter a date in the date range boxes. What I
would like to happen is: If the user hits the enter key (puts nothing in
the date boxes) the query continues and reports information containing
class information for ALL dates. I'm sure this is a relatively simple IFF
statement but, I'm kinda new at this stuff and don't quite know how to
state it.

Thanks for your help in advance.

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
*
http://www.accessmonster.com/Uwe/Abu...9255f3976e2fa7
*****************************************

select EMP_NAMES.LNAME, EMP_NAMES.FNAME, TRAINING.CLASS_ATTD,
TRAINING.DATE_ATTD
from EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
where
(
TRAINING.CLASS_ATTD Like [Enter it the name of the class]
)
and
(
(
TRAINING.DATE_ATTD >= [Enter the beginning date] or [Enter the
beginning date] Is Null
)
and
(
TRAINING.DATE_ATTD <= [Enter the cutoff date] or [Enter the
cutoff date] Is Null
)
)
--
Your eyes will probably thank you if you stop using all caps for your table
and column names :)

[Gary Toth via AccessMonster.com]

B I N G O!

Thanks a LOT!!

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/For...s-access/17425
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...7d0cb11a882f92
*****************************************

[Gary Toth via AccessMonster.com]

This works well. However, I'm getting extra date range prompts. The one sent by Salad only works if I DON'T put a date range in.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/For...s-access/17425
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...bc9b19f3e358c5
*****************************************

[Salad]

Gary Toth via AccessMonster.com wrote:

This works well. However, I'm getting extra date range prompts. The one sent by Salad only works if I DON'T put a date range in.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/For...s-access/17425
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...bc9b19f3e358c5
*****************************************

If mine is failing...make sure the _ are removed. I have OR _ in my
code. In a query builder you don't want them.

Create a table called Table1. It should have 1 field; DateField, type
Date. Enter 5 records; 1/1/2004 to 1/5/2004. Now create a new query.
Go to ViewSQL from the menu and drop this in

SELECT Table1.DateField
FROM Table1
WHERE (Table1.DateField) Between [Enter Start] And [Enter End]) OR
(Table1.DateField>=[Enter Start] And [Enter End] Is Null) OR
([Enter Start] Is Null And Table1.DateField<=[Enter End]) OR
([Enter Start] Is Null And [Enter End] Is Null);

It works for me. You'll notice there are no underbars

[John Winterbottom]

"Gary Toth via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:c0******************************@AccessMonste r.com...

This works well. However, I'm getting extra date range prompts. The one
sent by Salad only works if I DON'T put a date range in.

Make sure the startt and end date range parameters are all equivalent -
probably a typo somewhere

[Gary Toth via AccessMonster.com]

Checked it over and can't find anything.

FWIW, the prompt in the second box is cut of so it says "Enter the"

If I just hit enter through the second set of prompts, it works fine.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/For...s-access/17425
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...5fbb5801033904
*****************************************

[Gary Toth via AccessMonster.com]

This is how I got it working...

SELECT EMP_NAMES.LNAME, EMP_NAMES.FNAME, TRAINING.CLASS_ATTD, TRAINING.DATE_ATTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (
TRAINING.DATE_ATTD >= [Enter the beginning date] or [Enter the beginning date] Is Null

)
and
(
(
TRAINING.DATE_ATTD <= [Enter the cutoff date] or [Enter the cutoff date] Is Null
)
and
(
TRAINING.CLASS_ATTD Like [Enter the name of the class]
)
);
Thanks tons!!

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/For...s-access/17425
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...b353e810e6b26e
*****************************************

[John Winterbottom]

"Gary Toth via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:dc******************************@AccessMonste r.com...

Checked it over and can't find anything.

FWIW, the prompt in the second box is cut of so it says "Enter the"

Line-wrap is the problem

[Salad]

Gary Toth via AccessMonster.com wrote:

This is how I got it working...

SELECT EMP_NAMES.LNAME, EMP_NAMES.FNAME, TRAINING.CLASS_ATTD, TRAINING.DATE_ATTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (
TRAINING.DATE_ATTD >= [Enter the beginning date] or [Enter the beginning date] Is Null

)
and
(
(
TRAINING.DATE_ATTD <= [Enter the cutoff date] or [Enter the cutoff date] Is Null
)
and
(
TRAINING.CLASS_ATTD Like [Enter the name of the class]
)
);
Thanks tons!!

Good you got it to work. Frankly, if it were me, I'd build a form. I'd
have a dropdown to display the classes. I'd then have 2 date fields;
from and to. I'd have a dbl-click to present a calendar. I'd have 2
buttons; OK or Cancel.

If OK is pressed, I'd then build the Where clause...the run the select
or build the recordsource or whatever you are doing.

If I were a user, I'd tire of popups constantly harrassing me for
classnames or dates.

But that's my preference.