Merge Date and Time fields to be able to sort correctly

[Silvio]

How can I merge a date field and a time field in a single field and be able
to sort the new field correctly?

I have: Installed: [Installation Date] & " " & Format([Time
Installed],"hh:mm")

However, the date part does not appear to sort properly

 

[Marshall Barton]

How can I merge a date field and a time field in a single field and be able
to sort the new field correctly?

I have: Installed: [Installation Date] & " " & Format([Time
Installed],"hh:mm")

However, the date part does not appear to sort properly

Another difficulty caused by having separate date and time
fields. You should have one field with the date and time.
If you are too far down the wrong road to correct the
design, then your query field can combine them using the
expression:
Installed: [Installation Date] + [Time Installed]
That will work for Access data, but is not a good solution
because it requires knowledge of how Access stores date/time
values internally.

Note: the reason your expression does not work is because
the Format function and concatenation result in text values,
which sort differently from date/time values (and numbers).

 

[Silvio]

Hi Marsh, the problem is that the date and time are manually entered and not
stamped by the system as now(). I do have a calendar where the users can pick
a date but then how can I then handle the time? Obviously right now the
system sorts data as Text. What do you recommend?

How can I merge a date field and a time field in a single field and be able
to sort the new field correctly?

I have: Installed: [Installation Date] & " " & Format([Time
Installed],"hh:mm")

However, the date part does not appear to sort properly

Another difficulty caused by having separate date and time
fields. You should have one field with the date and time.
If you are too far down the wrong road to correct the
design, then your query field can combine them using the
expression:
Installed: [Installation Date] + [Time Installed]
That will work for Access data, but is not a good solution
because it requires knowledge of how Access stores date/time
values internally.

Note: the reason your expression does not work is because
the Format function and concatenation result in text values,
which sort differently from date/time values (and numbers).

 

[Marshall Barton]

Did you try my suggested alternate expression? If you did,
what about it didn't work?
--
Marsh
MVP [MS Access]

Hi Marsh, the problem is that the date and time are manually entered and not
stamped by the system as now(). I do have a calendar where the users can pick
a date but then how can I then handle the time? Obviously right now the
system sorts data as Text. What do you recommend?

How can I merge a date field and a time field in a single field and be able
to sort the new field correctly?

I have: Installed: [Installation Date] & " " & Format([Time
Installed],"hh:mm")

However, the date part does not appear to sort properly

Another difficulty caused by having separate date and time
fields. You should have one field with the date and time.
If you are too far down the wrong road to correct the
design, then your query field can combine them using the
expression:
Installed: [Installation Date] + [Time Installed]
That will work for Access data, but is not a good solution
because it requires knowledge of how Access stores date/time
values internally.

Note: the reason your expression does not work is because
the Format function and concatenation result in text values,
which sort differently from date/time values (and numbers).

 

[Silvio]

Yes Marsh it appears to work fine after a minor tweaking since not all
records have a time (Removed: IIf(IsNull([Removal Time]),[Removal
Date],[Removal Date]+[Removal Time]).

The format however is not what I really like, what I get is for example:
02/10/2009 10:09:57 PM

what I would like to see instead is: 02/10/09 22:06

Could you help me with the formatting?

Did you try my suggested alternate expression? If you did,
what about it didn't work?
--
Marsh
MVP [MS Access]

Hi Marsh, the problem is that the date and time are manually entered and not
stamped by the system as now(). I do have a calendar where the users can pick
a date but then how can I then handle the time? Obviously right now the
system sorts data as Text. What do you recommend?

Silvio wrote:

How can I merge a date field and a time field in a single field and be able
to sort the new field correctly?

I have: Installed: [Installation Date] & " " & Format([Time
Installed],"hh:mm")

However, the date part does not appear to sort properly


Another difficulty caused by having separate date and time
fields. You should have one field with the date and time.
If you are too far down the wrong road to correct the
design, then your query field can combine them using the
expression:
Installed: [Installation Date] + [Time Installed]
That will work for Access data, but is not a good solution
because it requires knowledge of how Access stores date/time
values internally.

Note: the reason your expression does not work is because
the Format function and concatenation result in text values,
which sort differently from date/time values (and numbers).

 

[Bob Barrows]

Date/Times are not stored with any formatting - they are stored as
Doubles: the whole number represents the number of days since the seed
date and the decimal indicates the time (.5 = noon).
You will need to use the Format function in your forms to display the
dates as you want them.

Yes Marsh it appears to work fine after a minor tweaking since not all
records have a time (Removed: IIf(IsNull([Removal Time]),[Removal
Date],[Removal Date]+[Removal Time]).

The format however is not what I really like, what I get is for
example: 02/10/2009 10:09:57 PM

what I would like to see instead is: 02/10/09 22:06

Could you help me with the formatting?

Did you try my suggested alternate expression? If you did,
what about it didn't work?
--
Marsh
MVP [MS Access]

Hi Marsh, the problem is that the date and time are manually
entered and not stamped by the system as now(). I do have a
calendar where the users can pick a date but then how can I then
handle the time? Obviously right now the system sorts data as Text.
What do you recommend?

:

Silvio wrote:

How can I merge a date field and a time field in a single field
and be able to sort the new field correctly?

I have: Installed: [Installation Date] & " " & Format([Time
Installed],"hh:mm")

However, the date part does not appear to sort properly


Another difficulty caused by having separate date and time
fields. You should have one field with the date and time.
If you are too far down the wrong road to correct the
design, then your query field can combine them using the
expression:
Installed: [Installation Date] + [Time Installed]
That will work for Access data, but is not a good solution
because it requires knowledge of how Access stores date/time
values internally.

Note: the reason your expression does not work is because
the Format function and concatenation result in text values,
which sort differently from date/time values (and numbers).

 

[John W. Vinson]

Could you help me with the formatting?

Just set the Format property of any form or report textbox containing this
information.

 

[Marshall Barton]

Yes Marsh it appears to work fine after a minor tweaking since not all
records have a time (Removed: IIf(IsNull([Removal Time]),[Removal
Date],[Removal Date]+[Removal Time]).

The format however is not what I really like, what I get is for example:
02/10/2009 10:09:57 PM

what I would like to see instead is: 02/10/09 22:06

Could you help me with the formatting?

Since this calculation is being done in a query, formatting
there is irrelevant. When the data is eventually displayed
in a form or report text box, just set the text box's Format
property to whatever style you like. The format you want
would be specified by:
mm/dd/yy hh:nn

See Format Property in VBA Help for all the things you can
do using various format codes.