date format question

[Mike Hughes]

Don't know if this can be done or not but here goes....

I have a query and , among other things, it tells me the average number of days - is there a way to report the number of days as Years, Months & Days?

Example One of the results of my query has 62 days which I would like to convert to something like 000202. (00 zero years, 02 two months, 02 two days)
Or 366 days which would look like 010001. (01 one year, 00 zero months, 01 one day.

 

[Paul_Bricker]

You can use this expression. The count will not be 100 percent accurate depending on what corrections you make for number of days in a year and number of days in a month. I used 365 days in the year and 30 days in a month.

Format(Int([NumberField]/365), "00") & Format(Int((([NumberField] Mod 365) /30)),"00")& Format(([NumberField] - (Int([NumberField]/365)*365 + Int(([NumberField] Mod 365) /30))*30),"00")

Paul

 

[Mike Hughes]

Paul, Where would this go in a query like this:

SELECT tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS, Avg(DateDiff("d",[DATE RECEIVED],[DATE OF DECISION])) AS DAYS, DateAdd("yyyy",-1,Date())+1 & " - " & Date() AS [REPORT PERIOD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.[DATE OF DECISION])>=DateAdd("yyyy",-1,Date())+1))
GROUP BY tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS
HAVING (((tblAPPLICATIONS.SPECIALIST)=[Enter Specialist]));


Thanks

Mike

 

[Paul_Bricker]

I assume that you want to calculate Days from the query. You may or maynot be able to do it using this because Days is a Calculated field.

SELECT tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS, Avg(DateDiff("d",[DATE RECEIVED],[DATE OF DECISION])) AS DAYS, DateAdd("yyyy",-1,Date())+1 & " - " & Date() AS [REPORT PERIOD] , Format(Int([Days]/365), "00") & Format(Int((([Days] Mod 365) /30)),"00")& Format(([Days] - (Int([Days]/365)*365 + Int(([Days] Mod 365) /30))*30),"00") AS MyDays
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.[DATE OF DECISION])>=DateAdd("yyyy",-1,Date())+1))
GROUP BY tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS
HAVING (((tblAPPLICATIONS.SPECIALIST)=[Enter Specialist]));

Paul

 

[Mike Hughes]

It worked!

Thanks again

Mike

 

[Mike Hughes]

Paul how would I change this query using your example to do the same thing for this as was done in the other:

SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING"));

Thanks

Mike

 

[Paul_Bricker]

Assuming you want to use [Days Old] as the field to test on, it would look like this.

SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
Format(Int([Days Old]/365), "00") & Format(Int((([Days Old] Mod 365) /30)),"00")& Format(([Days Old] - (Int([Days Old]/365)*365 + Int(([Days Old] Mod 365) /30))*30),"00") AS MyDays
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING"));

Paul

 

[Mike Hughes]

I had to put a comma in between [Days Old] and Format
to get it to run, but it doesn't return anything.

 

[Paul_Bricker]

Sorry, I missed the comma, but I'm not sure why it wouldn't return any values. What is [Days Old]. Is it a numeric field? Is it text? Is it calculated in a query and stored in the table tblApplications?

Paul

 

[Mike Hughes]

Numeric field.
It is calculated in the report.
In the property field for Days Old I set the control source to =DATE()-DATE RECEIVED to give me the Days Old.

 

[Paul_Bricker]

It's calculated in the Report. OK, I'm scratching my head here a little. How do you get the value into the table. Do you use an Insert Into statement when the report is run?
When you open the table tblApplications, do you actually have values stored in the [Days Old] field? It's possible you could change the SQL to do the calculation right there

SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD],
Format(Int((Date() - [Date Received])/365), "00") & Format(Int((((Date() - [Date Received]) Mod 365) /30)),"00")& Format(((Date() - [Date Received]) - (Int((Date() - [Date Received])/365)*365 + Int(((Date() - [Date Received]) Mod 365) /30))*30),"00") AS MyDays
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING"));

Paul

 

[Mike Hughes]

Paul,
It worked this time, with only one problem.

When the DAYS OLD are greater than one year (365 days old) it returns something like this:

Days Old MyDays

392 01-00-10559

I believe it should read something like 01-03-08

give or take a few days because of the number of days in a year and number of days in a month.

I don't understand the 10559

Any ideas?

In answer to your question regarding storing the Days Old in the Applications table, I don't have them stored there because I don't know how to go about it.

Thanks Mike

 

[raskew]

Mike-
This may be a little easier for me since I have a reasonable approximation of tblApplications.
In the query grid place this in a new field:

yymmdd: Format([days]\365,"00") & ":" & Format(IIf([days]>=365,([days] Mod 365)\30,[days]\30),"00") & ":" & Format([days] Mod 30,"00")

and set Total to Expression.

Let us know what that returns.

Bob

ADDED: See next post for downloadable zip (Access 97)

 

[raskew]

Here's a small example (Access 97)

 

[Paul_Bricker]

Bob I'll defer to you on this if you've seen it. Plus yours is easier to read.

Paul

 

[Mike Hughes]

I’m not great at explaining this stuff so let me explain what I would like.

This is the query that Paul wrote for a report called Specialist Monthly Reports, and it works great!

SELECT tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS, Avg(DateDiff("d",[DATE RECEIVED],[DATE OF DECISION])) AS DAYS, DateAdd("yyyy",-1,Date())+1 & " - " & Date() AS [REPORT PERIOD], Format(Int([Days]/365),"00-") & Format(Int((([Days] Mod 365)/30)),"00-") & Format(([Days]-(Int([Days]/365)*365+Int(([Days] Mod 365)/30))*30),"00") AS MyDays
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.[DATE OF DECISION])>=DateAdd("yyyy",-1,Date())+1))
GROUP BY tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS
HAVING (((tblAPPLICATIONS.SPECIALIST)=[Enter Specialist]));


What I was asking, is if the “format” section of the Specialist Monthly Reports query could be adapted to another query to give that query the MyDays field.

The query that I want to modify is the Time Aging query shown here, I believe that Raskew may have written it or helped write it. (It is working great as it is; I just wanted to add the MyDays field if possible.

SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING"));

Note: DAYS OLD is calculated in the report and the result is not stored in any table.

Thanks to both of you for all your help and understanding of someone that is just learning.

Mike

 

[Paul_Bricker]

Mike, try this and let me know how it goes. I think maybe there was a typo someplace in the other one.

SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD],
Format(Int((Date - [Date Received]) / 365), "00") & Format(Int((((Date - [Date Received]) Mod 365) / 30)), "00") & Format((Date - [Date Received]) - (Int((Date - [Date Received]) / 365) * 365 + Int((((Date - [Date Received]) Mod 365)) / 30) * 30), "00") AS MyDays
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING"));

Paul

 

[Mike Hughes]

Paul, When I run it I get asked to enter parameter value - DATE




Mike

 

[Paul_Bricker]

It's probably a translation problem. Although I used the Date() function, it probably copied and pasted as just the word Date. Go thru the statement and add the () to the Date part of
Date() - [Date Received]
If that doesn't work then retype the whole function
Date()

Paul

 

[Mike Hughes]

That was it, I should have caught that myself....Thanks

Mike

p.s. I'll try to leave you alone now.