Merge separate date and time fields in a report ?

[Guest]

i have two separate fields, one just the date and one just the time, this
data is entered in a form. the form is from an underlying query.

i want to be able to merge these two fields in a report.

i.e data eneterd
date of analysis 21/07/05
time of analysis 17:20

so that in the report it comes out as

date of analysis 21/07/05 17:20

can this be done in teh report or would i have to do something in the query ?

also i dont have a clue about VB so i steer clear of it

as always - thanks for the helpful advice !

Philip

 

[Guest]

There is no such thing as a 'date field' or 'time field' in Access in fact.
Values of the date/time data type always contain both date and time. A
date/time value is actually an offset from 30 December 1899 00:00:00, stored
as a 64 bit floating point number with the integer part representing days and
the fractional part the time of day, so if you enter a time without a date
you are in fact entering that time on 30 December 1899. Conversely a date
without a time is midnight at the start of that day. What you see is the
value formatted as a date and or time.

To combine your two fields all you need to do is add them together. You can
then format the result however you want, e.g.

Format([MyDate] + [MyTime], "dd mmmm yyyy hh:nn:ss")

If either field might be Null (left blank at data entry) you would need to
take account of this as Nulls propagate (Null + anything = Null), so include
the Nz function in the expression to return zero in place of any Null:

Format(Nz([MyDate],0) + Nz([MyTime],0), "dd mmmm yyyy hh:nn:ss")

You can do this either in the report as a computed control or in the query
as a computed column.

 

[Dirk Goldgar]

i have two separate fields, one just the date and one just the time,
this data is entered in a form. the form is from an underlying query.

i want to be able to merge these two fields in a report.

i.e data eneterd
date of analysis 21/07/05
time of analysis 17:20

so that in the report it comes out as

date of analysis 21/07/05 17:20

can this be done in teh report or would i have to do something in the
query ?

also i dont have a clue about VB so i steer clear of it

as always - thanks for the helpful advice !

Are these both stored in date/time fields? If so, you could just set
the controlsource of a text box on the report to

=[date of analysis] + [time of analysis]

That's assuming those are the names of the fields, of course.

I would not have used separate fields to store this information, since
the date/time field type is designed for the purpose of storing a moment
in time, and could easily have stored both the date and the time in one
field. However, given the fields as they are, you should be able to
combine them by adding them.

If the fields are *not* both date/time fields, then you'll have to do a
bit more to combine them.

 

[Guest]

wondered where december 1899 came from...

thanks tried that and works fine !

There is no such thing as a 'date field' or 'time field' in Access in fact.
Values of the date/time data type always contain both date and time. A
date/time value is actually an offset from 30 December 1899 00:00:00, stored
as a 64 bit floating point number with the integer part representing days and
the fractional part the time of day, so if you enter a time without a date
you are in fact entering that time on 30 December 1899. Conversely a date
without a time is midnight at the start of that day. What you see is the
value formatted as a date and or time.

To combine your two fields all you need to do is add them together. You can
then format the result however you want, e.g.

Format([MyDate] + [MyTime], "dd mmmm yyyy hh:nn:ss")

If either field might be Null (left blank at data entry) you would need to
take account of this as Nulls propagate (Null + anything = Null), so include
the Nz function in the expression to return zero in place of any Null:

Format(Nz([MyDate],0) + Nz([MyTime],0), "dd mmmm yyyy hh:nn:ss")

You can do this either in the report as a computed control or in the query
as a computed column.

i have two separate fields, one just the date and one just the time, this
data is entered in a form. the form is from an underlying query.

i want to be able to merge these two fields in a report.

i.e data eneterd
date of analysis 21/07/05
time of analysis 17:20

so that in the report it comes out as

date of analysis 21/07/05 17:20

can this be done in teh report or would i have to do something in the query ?

also i dont have a clue about VB so i steer clear of it

as always - thanks for the helpful advice !

Philip

 

[Guest]

they are both date and time fields.

that did seem like the logical way of doing things but it throws up an error
(circular reference or something ???)

but i got it to work by using Kens reply butthanks for your input
nonetheless !

i have two separate fields, one just the date and one just the time,
this data is entered in a form. the form is from an underlying query.

i want to be able to merge these two fields in a report.

i.e data eneterd
date of analysis 21/07/05
time of analysis 17:20

so that in the report it comes out as

date of analysis 21/07/05 17:20

can this be done in teh report or would i have to do something in the
query ?

also i dont have a clue about VB so i steer clear of it

as always - thanks for the helpful advice !

Are these both stored in date/time fields? If so, you could just set
the controlsource of a text box on the report to

=[date of analysis] + [time of analysis]

That's assuming those are the names of the fields, of course.

I would not have used separate fields to store this information, since
the date/time field type is designed for the purpose of storing a moment
in time, and could easily have stored both the date and the time in one
field. However, given the fields as they are, you should be able to
combine them by adding them.

If the fields are *not* both date/time fields, then you'll have to do a
bit more to combine them.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

they are both date and time fields.

that did seem like the logical way of doing things but it throws up
an error (circular reference or something ???)

but i got it to work by using Kens reply butthanks for your input
nonetheless !

Ken's suggestion seems to me to be essentially the same as mine, so I'd
guess you did something different in implementing it. You would have
gotten an error doing what I suggested if you'd given the text box the
same name as one of the fields -- maybe that was the problem. Anyway,
I'm glad you got it to work.