How do I write Aged queries for a specific "as of" date?

[bmoberly8888]

I have some aged time queries for current, 30-60 days, 60-90 days, 90-120 days and over 120 days. I am currently using Between Date() and Date()-30, Between Date()- 31 and Date()-60, etc.

However, we would like to run the reports with as "as of" date - for March 31, or whatever month we are reporting on. How do I use a prompt for the "as of" date - so all aging will be from the date I choose - i.e. 3/31/2005?

 

[traingamer]

Hmm. Replace all occurances of Date() with a parameter (either manual or form based), so it uses the same date throughout the query.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[bmoberly8888]

I tried taking out the Date () and replacing all of them with [enter date] but I get an error saying it was entered incorrectly or is too complex - so I'm not sure what I'm doing wrong.

 

[traingamer]

That should have worked. Try replacing them one at a time and testing between each change. Save original query first...

 

[CharlieT302]

A Parameter query would look something like:

Between [1st date] and [2nd date]

or

<= [Enter Date here]

The brackets take the place of the value that would normally appear there. Within the brackets, place a message indicating what the use is to do. When the query (or form attached to the query) is ran, a message box will appear. The value typed in will be used in the query. Hope this helps.

 

[bmoberly8888]

I guess my problem is that I'm using whatever date i.e. 3/31/2005 and that date -31 or -60 or -90 (not specific dates). I am writing it like 30Days: Sum(IIf([ARBillDate] Between [enter date] and Date()-30.... or Between [enter date]-31 and Date()-60 - I have also tried ...Between [enter date]-31 and [enter date]-60... but so far nothing is working.

 

[traingamer]

try using DateAdd (with a negative addend) (tested)

WHERE (((yourtable.yourdate) Between [Enter date] And DateAdd("d",-31,[Enter date])))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[bmoberly8888]

This is one of my actual fields for 60-90 days - Can you fix
it for me?

60Days: Sum((IIf([ARBillDate] Between Date()-31 And Date()-60,([ARMFeeBld]-[ARMFeeRcvd]+[ARMFeeAdj]+[ARMCshExpBld]-[ARMCshExpRcvd]+[ARMCshExpAdj]+[ARMNCshExpBld]-[ARMNCshExpRcvd]+[ARMNCshExpAdj]+[ARMIntBld]-[ARMIntRcvd]+[ARMIntAdj]),0)))

I am wanting it to age from 3/31/2005.

 

[CharlieT302]

This is not correct syntax:

Between [enter date]-31 and [enter date]-60

Your original question:

"However, we would like to run the reports with as "as of" date - for March 31, or whatever month we are reporting on. How do I use a prompt for the "as of" date - so all aging will be from the date I choose - i.e. 3/31/2005?"

This should have been answered by the parameter queries. How exactly would you like the dates to be returned? Or, maybe a better question is what does -31 relate to?
Maybe I am not understanding

 

[traingamer]

just like this. Your other queries will be similar

60Days: Sum((IIf([ARBillDate] Between [COLOR=red]DateAdd("d", -31, [Enter date])[/color] And [COLOR=red]DateAdd("d", -60,[Enter date])[/color], ([ARMFeeBld] - [ARMFeeRcvd] + [ARMFeeAdj] + [ARMCshExpBld] - [ARMCshExpRcvd] +[ARMCshExpAdj]+ [ARMNCshExpBld] - [ARMNCshExpRcvd] + [ARMNCshExpAdj] + [ARMIntBld]-[ARMIntRcvd]+[ARMIntAdj]),0)))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[bmoberly8888]

Thanks for your help - that is exactly what I needed!