=IF(DAY(NOW())<21, FORMULA 1, FORMULA 2)

[Adam]

Hi All,

Apologies if this is a simple query but I'm more an excel guru so I get
my applications mixed up !

I have a query and in the query I have a criteria formula on the date
field. This is below:

Between DateSerial(Year(Date()),Month(Date())-1,21) And
DateSerial(Year(Date()),Month(Date()),21)

This means that it takes searches between the 21st of last month to the
21st of this month.

Unfortunately this falls down as its now June so its searching between
21st May to 21st June, its not the 21st June yet !

What I want is like the below formula (it doesn't actually work
though):

=IF(DAY(now())<21,Between DateSerial(Year(Date()),Month(Date())-2,21)
And DateSerial(Year(Date()),Month(Date())-1,21)ELSE Between
DateSerial(Year(Date()),Month(Date())-1,21) And
DateSerial(Year(Date()),Month(Date()),21))

So if were not at the 21st day of the month yet it takes the 21st of 2
months ago to the 21st of 1 month ago, otherwise do as normal, 21st of
last month to 21st of this month.

Anyone know how to correct my query formula please ??

Adam

 

[Van T. Dinh]

Try something like (untested):

BETWEEN DateSerial(Year(Date()),Month(Date())-1 + (Day(Date()) < 21), 21)
AND DateSerial(Year(Date()),Month(Date()) + (Day(Date()) < 21),
21)

 

[Adam]

Hi, I get an error when I put that formula into the criteria.

I get this error:

"The expression you have entered has an invalid . (dot) or ! operator
or invalid parentheses.

You may have entered an invalid identifier or typed parenthese
following the Null constant."

 

[Van T. Dinh]

You didn't miss the

21)

that was meant for the expression after AND but was shown on the next line
in the post, did you?

 

[Adam]

Even if I enter that first part of the expression I get the error.

 

[Van T. Dinh]

Post the SQL String of your Query.

 

[Adam]

SELECT [Main tbl].[Full Name], [Main tbl].ADDED_DATE, [Main
tbl].AUTHORITY_NO, [Main tbl].CLAIM_NO, [Main tbl].CLAIM_CODE, [Main
tbl].CHASSIS_NO, [Main tbl].AMOUNT_GRANTED, [Main tbl].DEALER_CUST_NO,
[Main tbl].[Advisor Notes], [Main tbl].Concern, [Main tbl].[Concern
Notes]
FROM [Main tbl]
WHERE ((([Main tbl].[Full Name])=[Forms]![Advisor selection]![Combo5])
AND (([Main tbl].ADDED_DATE) Between
DateSerial(Year(Date()),Month(-Date())-1+(Day(Date())<21),21) And
DateSerial(Year(Date()),Month(-Date())+(Day(Date())<21),21)))
ORDER BY [Main tbl].ADDED_DATE;

 

[Van T. Dinh]

Why the minus sign in Month(-Date())???

The BETWEEN ... AND .... expression I posted works fine in Queries / SQL
using A2002 as the test bed.

The minus sign in your expression might give you the wrong result but it
shouldn't give you the error, either.

What is the *exact* error message you got with the posted SQL?

 

[aaron.kempf]

man you're crazy

dont you just need to use the IIF instead of the IF function?

 

[Van T. Dinh]

I hope you read the whole thread before you posted???

 

[aaron.kempf]

you should put your logic in tables instead of in queries

do this with joins

have a table with the 21-- 21 is like the closing of your month; or
what gives?

summarize it with joins not crazy-complex if clause

 

[Van T. Dinh]

I am not sure your reply is directed to the original question or to my
replies.

I guess you should re-read the question from the original poster and answer
that question since I didn't use If or IIf on what I posted???

HTH
Van T. Dinh
MVP (Access)



..

 

[Adam]

I've got this working, it was my fault Van sorry.

But it doesn't seem to do what I need.

I want to to look up between 21st Last month to the 21st of this month
IF the day today is greater or equal to the 21st. If its less than the
21st today when I want it to look between the 21st two months ago to
the 21st of last month.

This doesn't appear to do that.

 

[Van T. Dinh]

* In which way "This doesn't appear to do that."? I can't see your results
and your description doesn't give me any info. to work on.

In my tests, the 2 expressions work fine.

Perhaps, you should post a small sample data set, what the query returns
from the sample AND what you expect to see.

* Do you have non-zero time component is your Date Field values? If you have
non-zero time component, you need to use a slightly different criterion
since

ADate + non-zero time component is > ADate

 

[Adam]

Ok if we run this query today it searches data between 21st last month
to the 21st of this month. So your actually getting data from 21/05/05
to 16/06/05 (dd/mm/yy) as its not the 21st June yet.

On this occassion it should be searching between 21/04/05 to 21/05/05
(uk format dd/mm/yy). When the date today is 21/06/05 it should then
search between 21/05/05 to 21/06/05.

 

[Van T. Dinh]

Check your SQL String again.

The expressions I posted do exactly that. From the Debug window of Access
2002:

?DateSerial(Year(Date()),Month(Date())-1 + (Day(Date()) < 21), 21)
21/04/2005

?DateSerial(Year(Date()),Month(Date()) + (Day(Date()) < 21), 21)
21/05/2005

 

[Adam]

This is the SQL view of the query:

SELECT [Main tbl].[Full Name], [Main tbl].ADDED_DATE, [Main
tbl].AUTHORITY_NO, [Main tbl].CLAIM_NO, [Main tbl].CLAIM_CODE, [Main
tbl].CHASSIS_NO, [Main tbl].AMOUNT_GRANTED, [Main tbl].DEALER_CUST_NO,
[Main tbl].[Advisor Notes], [Main tbl].Concern, [Main tbl].[Concern
Notes]
FROM [Main tbl]
WHERE ((([Main tbl].[Full Name])=[Forms]![Advisor selection]![Combo5])
AND (([Main tbl].ADDED_DATE) Between
DateSerial(Year(Date()),Month(-Date())-1+(Day(Date())<21),21) And
DateSerial(Year(Date()),Month(-Date())+(Day(Date())<21),21)))
ORDER BY [Main tbl].ADDED_DATE;



I'm using Access 97, is that a problem ?

 

[Van T. Dinh]

I wrote before that Month(-Date()) may give wrong answer.

My original suggestion was Month(Date()). Please read my replies /
suggestions carefully.

Should work fine in A97.

 

[Adam]

I'm sorry I'm trying to find that.

How should the criteria go then please ?

 

[Adam]

I've got it, thank you for your help !