Date formatting

  • Thread starter Thread starter Shane Nation
  • Start date Start date
S

Shane Nation

I am using Access XP. I have two date fields "01/01/04 20:20" format in a
query.

I have subtracted one from the other to give me the time between them, how
ever I need to force Access to show if it is greater that 24 hours.
i.e "02/01/04 10:10" less "01/01/04 11:00" I need the calculated column in
my query to show 25:10

Please can anyone tell me how to do this, I use the format [hh]:mm in Excel
but it doesn't seem to work in Access?

Help

Cheers

Shane
 
Try changing the calculated field in the query to:

FieldName: Format(DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#)\60 &
DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#) Mod 60, "00\:00")

Also, this won't give 25:10 with the dates/times you have listed.

?Format(DateDiff("n", #01/01/04 11:00#, #02/01/04 10:10#)\60 & DateDiff("n",
#01/01/04 11:00#, #02/01/04 10:10#) Mod 60, "00\:00")
743:10
 
Sorry about the incorrect date meant to be 01/02/04 10:00 to 02/02/04 11:10

Thank you this works OK. but with dates 05/02/04 11:58 05/02/04 12:58 comes
back as 00:10 not 01:00.
And 05/02/2004 09:06:00 to 05/02/2004 19:06:00 comes back as 01:00

Please can you advuse?

Thanks
Shane
Wayne Morgan said:
Try changing the calculated field in the query to:

FieldName: Format(DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#)\60 &
DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#) Mod 60, "00\:00")

Also, this won't give 25:10 with the dates/times you have listed.

?Format(DateDiff("n", #01/01/04 11:00#, #02/01/04 10:10#)\60 & DateDiff("n",
#01/01/04 11:00#, #02/01/04 10:10#) Mod 60, "00\:00")
743:10

--
Wayne Morgan
Microsoft Access MVP


Shane Nation said:
I am using Access XP. I have two date fields "01/01/04 20:20" format in a
query.

I have subtracted one from the other to give me the time between them, how
ever I need to force Access to show if it is greater that 24 hours.
i.e "02/01/04 10:10" less "01/01/04 11:00" I need the calculated column in
my query to show 25:10

Please can anyone tell me how to do this, I use the format [hh]:mm in Excel
but it doesn't seem to work in Access?

Help

Cheers

Shane
Click to expand...
Click to expand...
 
Ok, it appears we'll have to format each section separately.

?DateDiff("n", #05/02/04 11:58#, #05/02/04 12:58#)\60 &
Format(DateDiff("n",#05/02/04 11:58#, #05/02/04 12:58#) Mod 60, "\:00")
1:00

If you want leading zeros on the hours:

?Format(DateDiff("n", #05/02/04 11:58#, #05/02/04 12:58#)\60, "00") &
Format(DateDiff("n",#05/02/04 11:58#, #05/02/04 12:58#) Mod 60, "\:00")
1:00

--
Wayne Morgan
MS Access MVP


Shane Nation said:
Sorry about the incorrect date meant to be 01/02/04 10:00 to 02/02/04 11:10

Thank you this works OK. but with dates 05/02/04 11:58 05/02/04 12:58 comes
back as 00:10 not 01:00.
And 05/02/2004 09:06:00 to 05/02/2004 19:06:00 comes back as 01:00

Please can you advuse?

Thanks
Shane
Try changing the calculated field in the query to:

FieldName: Format(DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#)\60 &
DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#) Mod 60, "00\:00")

Also, this won't give 25:10 with the dates/times you have listed.

?Format(DateDiff("n", #01/01/04 11:00#, #02/01/04 10:10#)\60 & DateDiff("n",
#01/01/04 11:00#, #02/01/04 10:10#) Mod 60, "00\:00")
743:10
Click to expand...
in
a
Click to expand...
column
in
my query to show 25:10

Please can anyone tell me how to do this, I use the format [hh]:mm in Excel
but it doesn't seem to work in Access?

Help

Cheers

Shane
Click to expand...
Click to expand...
Click to expand...
 
Mine looks like this as I am using filed names for the time vaules
ProcessTime-Main!Arrival

?DateDiff("n", Main!Arrival,ProcessTime)\60 &
Format(DateDiff("n",Main!Arrival,ProcessTime) Mod 60, "\:00")
Click to expand...

Could this be cuasing problems.

You help is great thank you.

Shane


Wayne Morgan said:
Ok, it appears we'll have to format each section separately.

?DateDiff("n", #05/02/04 11:58#, #05/02/04 12:58#)\60 &
Format(DateDiff("n",#05/02/04 11:58#, #05/02/04 12:58#) Mod 60, "\:00")
1:00

If you want leading zeros on the hours:

?Format(DateDiff("n", #05/02/04 11:58#, #05/02/04 12:58#)\60, "00") &
Format(DateDiff("n",#05/02/04 11:58#, #05/02/04 12:58#) Mod 60, "\:00")
1:00

--
Wayne Morgan
MS Access MVP


Shane Nation said:
Sorry about the incorrect date meant to be 01/02/04 10:00 to 02/02/04 11:10

Thank you this works OK. but with dates 05/02/04 11:58 05/02/04 12:58 comes
back as 00:10 not 01:00.
And 05/02/2004 09:06:00 to 05/02/2004 19:06:00 comes back as 01:00

Please can you advuse?

Thanks
Shane
Try changing the calculated field in the query to:

FieldName: Format(DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#)\60 &
DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#) Mod 60, "00\:00")

Also, this won't give 25:10 with the dates/times you have listed.

?Format(DateDiff("n", #01/01/04 11:00#, #02/01/04 10:10#)\60 & DateDiff("n",
#01/01/04 11:00#, #02/01/04 10:10#) Mod 60, "00\:00")
743:10

--
Wayne Morgan
Microsoft Access MVP


I am using Access XP. I have two date fields "01/01/04 20:20" format
Click to expand...
in
a
query.

I have subtracted one from the other to give me the time between
Click to expand...
them,
how
ever I need to force Access to show if it is greater that 24 hours.
i.e "02/01/04 10:10" less "01/01/04 11:00" I need the calculated
Click to expand...
column
in
my query to show 25:10

Please can anyone tell me how to do this, I use the format [hh]:mm in
Excel
but it doesn't seem to work in Access?

Help

Cheers

Shane
Click to expand...
Click to expand...
Click to expand...
 
To refer to fields in a query, use the syntax

[TableName].[FieldName]

The table needs to be one of the tables in the top of the query design grid.

--
Wayne Morgan
MS Access MVP


Shane Nation said:
Mine looks like this as I am using filed names for the time vaules
ProcessTime-Main!Arrival

?DateDiff("n", Main!Arrival,ProcessTime)\60 &
Format(DateDiff("n",Main!Arrival,ProcessTime) Mod 60, "\:00")
Click to expand...

Could this be cuasing problems.

You help is great thank you.

Shane


Ok, it appears we'll have to format each section separately.

?DateDiff("n", #05/02/04 11:58#, #05/02/04 12:58#)\60 &
Format(DateDiff("n",#05/02/04 11:58#, #05/02/04 12:58#) Mod 60, "\:00")
1:00

If you want leading zeros on the hours:

?Format(DateDiff("n", #05/02/04 11:58#, #05/02/04 12:58#)\60, "00") &
Format(DateDiff("n",#05/02/04 11:58#, #05/02/04 12:58#) Mod 60, "\:00")
1:00
Click to expand...
11:00#)\60
&
DateDiff("n", #02/01/04 10:10#, #02/02/04 11:00#) Mod 60, "00\:00")

Also, this won't give 25:10 with the dates/times you have listed.

?Format(DateDiff("n", #01/01/04 11:00#, #02/01/04 10:10#)\60 &
DateDiff("n",
#01/01/04 11:00#, #02/01/04 10:10#) Mod 60, "00\:00")
743:10

--
Wayne Morgan
Microsoft Access MVP


I am using Access XP. I have two date fields "01/01/04 20:20"
Click to expand...
format
in
a
query.

I have subtracted one from the other to give me the time between them,
how
ever I need to force Access to show if it is greater that 24 hours.
i.e "02/01/04 10:10" less "01/01/04 11:00" I need the calculated column
in
my query to show 25:10

Please can anyone tell me how to do this, I use the format [hh]:mm in
Excel
but it doesn't seem to work in Access?

Help

Cheers

Shane
Click to expand...
Click to expand...
Click to expand...
 
Back
Top