Formula

[Guest]

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

 

[Guest]

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?

 

[Damien McBain]

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?

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 think you need:

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

 

[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)

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?

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

 

[Guest]

Thanks guys everything cushty now!

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)

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?

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