Handle Blank User Input

[tiger0268 via AccessMonster.com]

I am working on a query to pull up all records that meet certain conditions
and is at least three months old. I figured that part out; however, I want
to add a condition for a user input that would cutoff the records for a
certain number of months. Everything works well until I enter a null value
in the user input.

The Where condition I got so far is:

<DateSerial(Year(Date()),Month(Date())-3,1) And >DateSerial(Year(Date()),
Month(Date())-([CutOff Number]+3),0)

If I enter a null value into the user input, I would like for it to pull up
all records that are at least three months old ( <DateSerial(Year(Date()),
Month(Date())-3,1) ).

 

[MGFoster]

I am working on a query to pull up all records that meet certain conditions
and is at least three months old. I figured that part out; however, I want
to add a condition for a user input that would cutoff the records for a
certain number of months. Everything works well until I enter a null value
in the user input.

The Where condition I got so far is:

<DateSerial(Year(Date()),Month(Date())-3,1) And >DateSerial(Year(Date()),
Month(Date())-([CutOff Number]+3),0)

If I enter a null value into the user input, I would like for it to pull up
all records that are at least three months old ( <DateSerial(Year(Date()),
Month(Date())-3,1) ).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should use the DateAdd() function instead of the DateSerial 'cuz
substracting 3 months may cause the year to change. The way you've set
up DateSerial() it would not use the correct year.

To get the condition you want (untested):

< DateAdd("m", -3, date() - day(date()) + 1
AND > DateAdd("m", -Nz([CutOff Number],0) + 3, date() - day(date()) )

date() - day(date()) gets the last date of the previous month. By
adding 1 to it we get the 1st date of the current month.

I'm assuming that [Cutoff Number] is the user input.

Nz([CutOff Number],0) will return 0 if the user doesn't enter anything
(NULL). This will cause the last date to be "at least three months old"
[ -(0) + 3 = -3 ].

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJrXPYechKqOuFEgEQJ6ngCfY9XfvvqM1oskHBauxdNXzNNQuDYAoI9C
2uDCuvcl5Em9TR1kK9p3OJGy
=FK2u
-----END PGP SIGNATURE-----

 

[tiger0268 via AccessMonster.com]

Thanks...It works now.

I am working on a query to pull up all records that meet certain conditions
and is at least three months old. I figured that part out; however, I want

[quoted text clipped - 10 lines]

all records that are at least three months old ( <DateSerial(Year(Date()),
Month(Date())-3,1) ).

You should use the DateAdd() function instead of the DateSerial 'cuz
substracting 3 months may cause the year to change. The way you've set
up DateSerial() it would not use the correct year.

To get the condition you want (untested):

< DateAdd("m", -3, date() - day(date()) + 1
AND > DateAdd("m", -Nz([CutOff Number],0) + 3, date() - day(date()) )

date() - day(date()) gets the last date of the previous month. By
adding 1 to it we get the 1st date of the current month.

I'm assuming that [Cutoff Number] is the user input.

Nz([CutOff Number],0) will return 0 if the user doesn't enter anything
(NULL). This will cause the last date to be "at least three months old"
[ -(0) + 3 = -3 ].

 

[Marshall Barton]

I am working on a query to pull up all records that meet certain conditions
and is at least three months old. I figured that part out; however, I want
to add a condition for a user input that would cutoff the records for a
certain number of months. Everything works well until I enter a null value
in the user input.

The Where condition I got so far is:

<DateSerial(Year(Date()),Month(Date())-3,1) And >DateSerial(Year(Date()),
Month(Date())-([CutOff Number]+3),0)

If I enter a null value into the user input, I would like for it to pull up
all records that are at least three months old ( <DateSerial(Year(Date()),
Month(Date())-3,1) ).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should use the DateAdd() function instead of the DateSerial 'cuz
substracting 3 months may cause the year to change. The way you've set
up DateSerial() it would not use the correct year.

To get the condition you want (untested):

< DateAdd("m", -3, date() - day(date()) + 1
AND > DateAdd("m", -Nz([CutOff Number],0) + 3, date() - day(date()) )

date() - day(date()) gets the last date of the previous month. By
adding 1 to it we get the 1st date of the current month.

I'm assuming that [Cutoff Number] is the user input.

Nz([CutOff Number],0) will return 0 if the user doesn't enter anything
(NULL). This will cause the last date to be "at least three months old"
[ -(0) + 3 = -3 ].

Actually, DateSerial handles month and year boundaries as
one would hope it would. For example,

?DateSerial(2000, 1, 2365)
6/22/2006

?DateSerial(2000, 80, -39)
6/22/2006

?DateSerial(2100, -1120, -39)
6/22/2006

The only drawback to getting completely carried away with
this kind of weird date calculation is that the arguments
are Integers instead of Longs.