Using Between in criteria

  • Thread starter Thread starter T.
  • Start date Start date
T

T.

Hi all,

I need to pull records with a query that will show me all records that are
older than six months and then one that shows me all older that 12 months.
I can pull all of the ones in the last six months fine using a BETWEEN
clause but these are the ones that I need to be excluded.

I am sure this is SUPER simple but it is giving me grief!! Can anyone
help???

Thanks,

T.
 
T.

I don't think that BETWEEN is going to give you what you want, as it
essentially sets a range of dates with a lower limit and an upper limit and
returns dates which fall within these two limits.

You can use the following expression in the Criteria row: of your
significant Date field and the query will prompt you to enter the number of
months that your date field should be "older than"

< DateAdd("m",-[Return dates older than this number of months:],
Date())

If you input 6, records will be returned which have a Date field older than
9/16/2003; enter 12 and the query will return records which have a Date
field older than 3/15/2003.
 
Thanks Cheryl, this does help...but...this needs to be an automated function
with no user input. Do you or anyone else have any ideas on how to automate
this a bit more??

Thanks,

T.


Cheryl Fischer said:
T.

I don't think that BETWEEN is going to give you what you want, as it
essentially sets a range of dates with a lower limit and an upper limit and
returns dates which fall within these two limits.

You can use the following expression in the Criteria row: of your
significant Date field and the query will prompt you to enter the number of
months that your date field should be "older than"

< DateAdd("m",-[Return dates older than this number of months:],
Date())

If you input 6, records will be returned which have a Date field older than
9/16/2003; enter 12 and the query will return records which have a Date
field older than 3/15/2003.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Hi all,

I need to pull records with a query that will show me all records that are
older than six months and then one that shows me all older that 12 months.
I can pull all of the ones in the last six months fine using a BETWEEN
clause but these are the ones that I need to be excluded.

I am sure this is SUPER simple but it is giving me grief!! Can anyone
help???

Thanks,

T.
Click to expand...
Click to expand...
 
Then, to return records where the significant date field is older than 6
months ago, use the following as your criteria:

< DateAdd("m", -6, Date())

For records where the significant date field is older than 12 months ago,
use:

< DateAdd("m", -12, Date())

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


T. said:
Thanks Cheryl, this does help...but...this needs to be an automated function
with no user input. Do you or anyone else have any ideas on how to automate
this a bit more??

Thanks,

T.


Cheryl Fischer said:
T.

I don't think that BETWEEN is going to give you what you want, as it
essentially sets a range of dates with a lower limit and an upper limit and
returns dates which fall within these two limits.

You can use the following expression in the Criteria row: of your
significant Date field and the query will prompt you to enter the number of
months that your date field should be "older than"

< DateAdd("m",-[Return dates older than this number of months:],
Date())

If you input 6, records will be returned which have a Date field older than
9/16/2003; enter 12 and the query will return records which have a Date
field older than 3/15/2003.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Hi all,

I need to pull records with a query that will show me all records that are
older than six months and then one that shows me all older that 12 months.
I can pull all of the ones in the last six months fine using a BETWEEN
clause but these are the ones that I need to be excluded.

I am sure this is SUPER simple but it is giving me grief!! Can anyone
help???

Thanks,

T.
Click to expand...
Click to expand...
Click to expand...
 
Just wanted to show what the final Criteria ended up at.....

Between DateAdd("d",-365,Date()) And DateAdd("d",-183,Date())

Hope this helps someone else some day!

T.
 
Back
Top