Working with Date field in Query

[acnovice]

My Query result is something wrong.

I want to get results;

Query 1([InvDate]+30(days))<=[CurrDate])AND[Paid]Is Null
Query 2:[CurrDate]>=[Paid]

Following is my Query example for 2nd one but the result isn't that I expect.

FROM tblCustOrderLog
WHERE (((Format([CurrDate],'mmddyy')=Format(Date(),'mmddyy'))>=Format([Paid],'mmddyy')))
ORDER BY tblCustOrderLog.Ccode;

I appreciate your help.

 

[jrbarnett]

If I understand the logic in your second query, it checks for:
The Currdate field equal to today's date which is greater than the date in the Paid field. You can rewrite it as:

WHERE Date() >= [Paid]

and then apply formatting on forms/reports in the Format property.

John

 

[SkipVought]

Hi,

You have converted your date to a STRING of the form mmddyy.

However, this STRING will not sort or colate as you expect.

This is how these STRINGS will sort....
[tt]
010103
010104
020103
020104
[/tt]
Change the format to yyyymmdd

4 digits for year!!!!

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[PHV]

And for Query1:
WHERE Date()-30>=InvDate AND Paid Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[acnovice]

jrbarnett ... Thank you for the answer.
SkipVought... Good to know about it.
PHV ... I'm getting many answers from you. It's all precise and short. I appreciate it.