Use [StartDate] to get [StartTime] as text field

[Ruth]

I'm using Access 2K working in an existing database.

My client is working with another contractor who has developed a web
interface for time entry. I'm responsible for integrating their work
into the existing DB. The web form writes the data (on save) to a
table in the Access DB. The new table ([Entry])does not follow the
structure/format of the previous time entry table ([TechHours])so I'm
having to work around some issues to import the old data into the new
table (so that historical data will still be available for reporting,
investigation, etc.).

The contact person here specified that they did not want to have to
enter a colon in time entry-- using a "true" military time format.
(0400, rather than 04:00). While Access will allow you to display
"true" military time, it forces you to enter the colon during data
entry. The other contractor wanting to oblige the client separated
time and date (current table uses one "Start" field to display time
and date values). In order for the field to NOT require a colon, he
made it a number field. I am trying use the [StartDate] field to
create a number value for the [StartTime] field in military format.
Any type of update query I've tried has resulted in data mismatch
errors.

Any suggestions?

Thanks,
Ruth

 

[Dirk Goldgar]

In

I'm using Access 2K working in an existing database.

My client is working with another contractor who has developed a web
interface for time entry. I'm responsible for integrating their work
into the existing DB. The web form writes the data (on save) to a
table in the Access DB. The new table ([Entry])does not follow the
structure/format of the previous time entry table ([TechHours])so I'm
having to work around some issues to import the old data into the new
table (so that historical data will still be available for reporting,
investigation, etc.).

The contact person here specified that they did not want to have to
enter a colon in time entry-- using a "true" military time format.
(0400, rather than 04:00). While Access will allow you to display
"true" military time, it forces you to enter the colon during data
entry. The other contractor wanting to oblige the client separated
time and date (current table uses one "Start" field to display time
and date values). In order for the field to NOT require a colon, he
made it a number field. I am trying use the [StartDate] field to
create a number value for the [StartTime] field in military format.
Any type of update query I've tried has resulted in data mismatch
errors.

I'm not completely sure I understand you. Is [StartDate] a date/time
field that contains both date and time, e.g. #5/29/2007 11:20 PM# ? And
do you want to extract the time in miltary format, convert it to a
number, and store it in [StartTime], a number field?

If that's the case, I think you could use an assignment along these
lines:

StartTime = CInt(Format([StartDate], "hhmm"))

That will give an error if the [StartDate] field is Null, so you want to
exclude those records from the assignment.

 

[Ruth]

Hi Dirk,
Unfortunately, it wasn't as straightforward as that. We've resolved,
so I'll post my solution for anyone else's reference.

The new [StartTime] field is stored as a number with formatting of
"0000" applied to show military time. For example, 0600 is stored in
the table as 600, but with formatting displayed properly as 0600. This
arrangement keeps the user from having to enter the colon in the time
field during data entry. I captured the time by doing the following:

Created calculated fields in the query grid for Hour and Minute.
Used the Hour and Minute VB commands on the fields to capture the hour
and minute as integers
Created an IIf statement that added a leading zero for numbers <10.
Created another calculated field, StartTime, to join the Hour and
Minute fields
Ran result as a Make Table query-- StartTime field result is text
Opened the new table in design view and changed StartTime field type
to Number. I lost the leading zeros, but as I said before that's being
handled with formatting.

I know it sounds like a long way around the world, but sometimes you
have to do those things when fitting your work with another's
design.

Thanks for your suggestion!

Ruth

 

[Dirk Goldgar]

In

Hi Dirk,
Unfortunately, it wasn't as straightforward as that.

Why not, I wonder? I would have thought you could simply create the
number field [StartTime], and then run an update query along the lines
of

UPDATE YourTable
SET StartTime = CInt(Format([StartDate], "hhmm"))
WHERE StartDate Is Not Null;

That didn't work?

We've resolved,
so I'll post my solution for anyone else's reference.

I'm glad you found a solution. Thanks for posting it.

 

[Ruth]

Nope. I had tested something similar and had the same result as I had
when I just tested this option. There's something about the date field
that wants to "keep" it as a date. What should have extracted as 0900
ended up being 6/18/1902 (even with the "hhmm" formatting). :-/ Crazy
old Access! lol.

 

[Dirk Goldgar]

In

Nope. I had tested something similar and had the same result as I had
when I just tested this option. There's something about the date field
that wants to "keep" it as a date. What should have extracted as 0900
ended up being 6/18/1902 (even with the "hhmm" formatting). :-/ Crazy
old Access! lol.

Sounds like your date field may not be defined as a Date/Time type, or
your time field may not be defined as a Number type, or there's
something else undisclosed about your table setup. Oh well, you found a
way to get what you want.