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_A TTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (((TRAINING.CLA SS_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.c om 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_A TTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (((TRAINING.CLA SS_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.DateFiel d
FROM Table1
WHERE (Table1.DateFie ld) Between [Enter Start] And [Enter End]) OR _
(Table1.DateFie ld>=[Enter Start] And [Enter End] Is Null) OR _
([Enter Start] Is Null And Table1.DateFiel d<=[Enter End]) OR _
([Enter Start] Is Null And [Enter End] Is Null);

[John Winterbottom]

"Gary Toth via AccessMonster.c om" <fo***@AccessMo nster.com> wrote in message
news:34******** *************** *******@AccessM onster.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_A TTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (((TRAINING.CLA SS_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_A TTD
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_A TTD >= [Enter the beginning date] or [Enter the
beginning date] Is Null
)
and
(
TRAINING.DATE_A TTD <= [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.c om 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.DateFiel d
FROM Table1
WHERE (Table1.DateFie ld) Between [Enter Start] And [Enter End]) OR
(Table1.DateFie ld>=[Enter Start] And [Enter End] Is Null) OR
([Enter Start] Is Null And Table1.DateFiel d<=[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.c om" <fo***@AccessMo nster.com> wrote in message
news:c0******** *************** *******@AccessM onster.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_A TTD
FROM EMP_NAMES INNER JOIN TRAINING ON EMP_NAMES.UID = TRAINING.UID
WHERE (
TRAINING.DATE_A TTD >= [Enter the beginning date] or [Enter the beginning date] Is Null

)
and
(
(
TRAINING.DATE_A TTD <= [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.c om" <fo***@AccessMo nster.com> wrote in message
news:dc******** *************** *******@AccessM onster.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