Converting Quarters, years or months to days? (1 Viewer)

[fluid]

Hi,

I have two fields, one is a numerical value, and one is a date code
(example: "2","ww" meaning 2 weeks, or "4","m" meaning 4 months)

I want to sort these in a query by shortest to longest duration. I was thinking of converting all of them to days and sorting by days. I don't know of an easier way. Is there a way to convert these values back to days? Or an even easier way to sort them?

Thanks.

 

[plog]

You would build a table to convert your date codes to days:

CodeDays
cd_Code, cd_Description, cd_Days
d, Day, 1
ww, Week, 7
m, Month, 30
q, Quarter, 91
y, Year, 365

Then you build a query, link them by the code and multiply. Just be warned, a lot of those units don't have static definitions in terms of days (e.g. Month=28, 29, 30 & 31 days)

 

[Ranman256]

you can calculate any value based on date.

DateDiff("d",[dateFld], Date()) 'will give you time in days
DateDiff("m",[dateFld], Date()) 'will give you time in months

you can sort how you like.

 

[pbaldy]

I think you're on the right track getting them to a common interval. Something like:

NumericalField * IIf(CodeField = "ww", 7, 30)

if those are the only options.

 

[fluid]

Got it working, Here is what I did...
I added a calculated field column to the query with the following:

NumOfDays: dateadd([TimeMult],[TimeVal], date()) - date()


Because the DateAdd() function always spits out it's answer in days, I used that to my advantage. It just adds my values to today's date, then subtracts today's date, leaves me with a value in days equalling my original value.

The [NumOfDays] column in my query now equals the number of days. Then I just sort by that.