Time format in a statement

[Steve Soldo]

Hello. Unsophisticated question. I am trying to write a macro that will
report the current date and military time in a control. Trouble is I can
only get the format with hours, minutes and seconds with am or pm next to it.
I need military time.

I want the output to say for example:

Finished on 8/4/2009 at 17:55 where 8/4/2009 and 17:55 are the date and
time formats respectively. Thank you.

 

[Jeff Boyce]

Steve

The following field you'd add to a query is untested:

Untested: "Finished on " & Format([YourDateTimeField], "m/d/yyyy") & "
at " & Format([YourDateTimeField], "hhnn")

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

You could just use expressions. For the date:

=Format(Now(),"d/m/yyyy")

For the time:
=Format(Now(),"hh:mm")

Depending on your regional settings you may need to use the backslash to
indicate a literal character. I'm a little unclear on exactly when this is
necessary, but in any case it seems to do no harm.

=Format(Now(),"d\/m\/yyyy")
=Format(Now(),"hh\:mm")

Now just put it together:

="Finished on " & Format(Now(),"d/m/yyyy") & " at " & Format(Now(),"hh:mm")

This would be in the Control Source of a text box. In a query, replace the
= sign with something like TimeText in a blank column in design view:

TimeText: "Finished on " & Format(Now(),"d/m/yyyy") & " at " &
Format(Now(),"hh:mm")

You can also use VBA, if you prefer that route. Post back if you want
details.

 

[John Spencer]

Format(SomeDateTimeField,"m/d/yyyy")

and

Format(SomeDateTimeField,"hh:nn")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hans Up]

I want the output to say for example:

Finished on 8/4/2009 at 17:55 where 8/4/2009 and 17:55 are the date and
time formats respectively. Thank you.

Consider a single Format statement, instead of two.

format(now(),"m/d/yyyy \at hh:nn")

 

[Steve Soldo]

I have tried all of the suggestions noted above. They do not work.

When I run the macro, I get an "Action Failed" message and it halts.

I can't figure out why. The field is formated as text.

Any suggestions?

 

[BruceM]

Don't use a macro. Use the expressions in either the Control Source of a
text box or as a new field in a query.

 

[Steve Soldo]

Actually when I change the format of the control to "memo" the macro works
fine. Why? I'll never know. This program sure is tedious to use...

 

[BruceM]

Memo is field data type in a table, not a format that can be applied to a
control (at least in Access 2003). It could be it failed with the text
field because it was not large enough to add all of the text. In any case
you should not be storing the statement, but rather assembling it as needed
as suggested in either the Control Source of a text box or in a query.
Nobody who replied imagined you were writing the sentence to a table field.