Format Conversion

[jodo]

I have a database that is being converted to XP. I have a field on a report that is called DURATION. DURATION is the difference between COMPLETE and FIRST_TC in minutes. In ACCESS 97, on the report I formatted the field to (=Format([DURATION],"hh:nn")). This forces the minutes to be converted into hours and minutes and it works great.

Well, in ACCESS XP, when you put the formatting in (=Format([DURATION],"hh:nn")) it automatically converts it to =Format([DURATION],"Short Time"), obviously because "HH:NN" is the defined "Short Time". The problem is that it doesn't work, it gives the famous #ERROR in that field when the report is ran. Also, I have tried the input mask and the format property in the properties box.

If anyone has any suggestions, I would appreciate it!!

Thanks in advance.



jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!

 

[psemianonymous]

What is the data type of [DURATION]? An integer-style number? Double/currency/date type?

Format([],"hh:nn") shouldn't work unless the field is stored as a date type. So to store it as a date type, try

Format(CDate(CDbl([DURATION])/(60*24)),"hh:nn")

and see if that works.

 

[jodo]

foolio12, DURATION is a created field in the record source of the report that looks like this (DURATION: DateDiff("n",[FIRST_TC],[COMPLETE])). This way, I can just use DURATION on the report instead of typing in the whole thing. I tried, using your suggestion (Format(CDate(CDbl([DURATION])/(60*24)),"hh:nn")), but the problem is, ACCESS converts the last part "hh:nn" to "Short Time". Another thing, I'm assuming that DURATION is a date data type because both FIRST_TC and COMPLETE are date data types.

You also mentioned that the format ([],"hh:nn") shouldn't work unless the field is stored as a date type. It does work, because I've used it in ACCESS 97 and 2000.

Also, when ACCESS converts the statement (Format(CDate(CDbl([DURATION])/(60*24)),"hh:nn")) to (Format(CDate(CDbl([DURATION])/(60*24)),"Short Time")), the data displayed on the report when it is ran is 00:00 in every record.

Any other suggestions??
Thanks for your help.

jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!

 

[jodo]

foolio12, I used this whole format and it worked. Thanks for your help.

=Format(CDate(CDbl(DateDiff("n",[FIRST_TC],[COMPLETE]))/(60*24)),"Short Time")

jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!