Marshall
Sorry about the “Nopeâ€â€¦..just meant that your example didn’t accomplish what
I was hoping.
Here is the situation.
The master database is used as a visitor sign in log.
I have, besides, name, address etc. a DATE field with a format as a
date/time Access “Short Date†i.e. 1/1/2007.
I also have a TIME field with a format as date/time Access “Short Timeâ€.
i.e. 12:23
These are both created using a default of Date() and Time() so the visitor
doesn’t have to fill them in. Again they are Date/Time fields not Text fields.
I have a make table query to capture the category of person signing in and
the DATE and TIME fields selecting only the dates I need.
I use this query to create a Crosstab query that has the times down the left
side (these are the times I want to only show as the hour)…..12:00 all
minutes shown as 00.
Across the top I have columns that are created from the date to the DAY OF
THE WEEK 1 through 7, and another that counts the number of records for the
number of visitors that signed into the log.
TIME Count Sun Mon Tue……..
6:00 5 2 1 2
7:00 8 4 4
I can do this by using find and replace in the table created in the make
table query, to replace the minutes after :** with :00.
I use the crosstab query to prepare a report and it only shows the rounded
hour as above.
I was trying to accomplish changing the minutes to zeros using an update
query rather than going through the find and replace routine…which I have in
a Macro and it works, but the user has to type in the characters to find and
replace.
If your solution can be used in an update query to change the minutes to 00
could you please give me a little more guidance, as I am not a SQL person,
and have only used the routines built into Access. What would I put in the
update query “update†box for the field named TIME? This is the only field I
had in the update query.
Thanks again.
Rich
Marshall Barton said:
"Nope" doesn't explain what you tried nor what you don't
like about the result.
Be careful about confusing a value and a format, they are
two very different things. Changing the format does not
change the value. So if you have a time **value** like
19:47:32, you can use one format it to display it as
7:47:32 PM or another format to display it as 17:00 without
changing the value in the table.
What I posted should be usable in an Update query to
permantly change the value in the table, which is what I
understood you wanted to do.
UPDATE table Set timefield = TimeSerial(Hour(timefield),0,0)
Be sure to test in a copy of the table because you can not
undo a query that modifies every record in the table.
OTOH, if you do not want to change the data in the table,
but only how it is displayed in a text box, then set the
text box's format property to
h:00
--
Marsh
MVP [MS Access]
Nope......trying to change any time with minutes in this format 19:45 which
is an Access "short time" format to only show zeros in the minute field.
Want it to look like 19:00, 20:00, 21:00, 06:00. 07:00 etc
