Formula

I

Ian AFFS

Date()-1460>[AVR]>Date()-1095

I have entered this formula into criteria in the 'AVR' field of my query.
Why won't Access recognise it? It seems fine to me and it works if I remove
one argument eg [AVR]>Date()-1095
Perhaps I am being obtuse. Can anyone think of another way to make a query
display records only if their date entry is between two functions of today's
date??
Many Thanks
 
I

Ian AFFS

Think I have found a way around this now . All the same does anyone know why
access doesn't like mutliple arguments like below in formulae? ie more than
one qualification requirement?
 
D

Damien McBain

Ian said:
Think I have found a way around this now . All the same does anyone
know why access doesn't like mutliple arguments like below in
formulae? ie more than one qualification requirement?

Ian AFFS said:
Date()-1460>[AVR]>Date()-1095

I have entered this formula into criteria in the 'AVR' field of my
query. Why won't Access recognise it? It seems fine to me and it
works if I remove one argument eg [AVR]>Date()-1095
Perhaps I am being obtuse. Can anyone think of another way to make a
query display records only if their date entry is between two
functions of today's date??
Many Thanks
Click to expand...
Click to expand...

I think you need:

Date()-1460>[AVR] And [AVR]>Date()-1095
 
D

Douglas J. Steele

It's not just Access: it's SQL in general.

You also can't use WHERE [X] = 1 Or 2 Or 3, which many people try.

As Damien pointed out, you need to use

Date()-1460>[AVR] AND [AVR]>Date()-1095

You could also use

[AVR] BETWEEN (Date() - 1461) AND (Date() - 1094)

(I changed the numbers above because BETWEEN is inclusive)

For the example I gave at the beginning, you'd need to use

[X] IN (1, 2, 3)

Or

[X] = 1 OR [X] = 2 OR [X] = 3

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Ian AFFS said:
Think I have found a way around this now . All the same does anyone know why
access doesn't like mutliple arguments like below in formulae? ie more than
one qualification requirement?

Ian AFFS said:
Date()-1460>[AVR]>Date()-1095

I have entered this formula into criteria in the 'AVR' field of my query.
Why won't Access recognise it? It seems fine to me and it works if I remove
one argument eg [AVR]>Date()-1095
Perhaps I am being obtuse. Can anyone think of another way to make a query
display records only if their date entry is between two functions of today's
date??
Many Thanks
Click to expand...
Click to expand...
 
I

Ian AFFS

Thanks guys everything cushty now!

Douglas J. Steele said:
It's not just Access: it's SQL in general.

You also can't use WHERE [X] = 1 Or 2 Or 3, which many people try.

As Damien pointed out, you need to use

Date()-1460>[AVR] AND [AVR]>Date()-1095

You could also use

[AVR] BETWEEN (Date() - 1461) AND (Date() - 1094)

(I changed the numbers above because BETWEEN is inclusive)

For the example I gave at the beginning, you'd need to use

[X] IN (1, 2, 3)

Or

[X] = 1 OR [X] = 2 OR [X] = 3

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Ian AFFS said:
Think I have found a way around this now . All the same does anyone know why
access doesn't like mutliple arguments like below in formulae? ie more than
one qualification requirement?

Ian AFFS said:
Date()-1460>[AVR]>Date()-1095

I have entered this formula into criteria in the 'AVR' field of my query.
Why won't Access recognise it? It seems fine to me and it works if I remove
one argument eg [AVR]>Date()-1095
Perhaps I am being obtuse. Can anyone think of another way to make a query
display records only if their date entry is between two functions of today's
date??
Many Thanks
Click to expand...
Click to expand...
Click to expand...
 
Top