convert text into time 2

[Gaime]

I'm working with somebody else his access database. He has two tables with times showing like this: 23:25. He made it a textfield where only numbers between 00:00 and 23:59 can be entered. Now I need to create a third table which shows the downtimes of a machine. It has to show the timedifferents between the starting time and the ending time. The downtime also has to be shown as xx:xx. But ofcourse everything has to be converted to time instead of text. How do I do this the best way?

Another question: the date is entered as 4414 (today). The first 4 stands voor de 4 in 2004, the next two numbers '41' stands for the week of the year. The last number, here 4, stands for the day of the week. How can I convert this to 7 october 2004.

If anyone knows an answer please guide me ...

Thx
gaime

 

[dhookom]

I am beginning to question what is actually stored in your two fields. How about running this query and pasting some values into a reply?

SELECT [txtBeginStoring], [txtEindeStoring]
FROM [tblStoring];

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Gaime]

dhookom,

the result is got from running your query were:

txtBeginStoring txtEindeStoring
16:40 16:49
09:15 09:22
08:30 16:30
09:30 09:32
09:35 09:36
09:35 09:36
08:30 16:30
08:30 16:30

The way these number were given in: as __:__ in a field of a form. The number has to be betweem 00:00 and 23:59 and the last two numbers have to be between 00 and 59.

Hope this might help.

thx,
gaime

 

[PHV]

Can you please post ALL the properties of the txtBeginStoring field of the tblStoring table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I wonder if an input mask is being used that doesn't store the ":" with the value in the field. What does the following look like?

SELECT txtBeginStoring,
NewField: Replace([txtBeginStoring],":","~")
FROM tblStoring;

If the ":" is stored, you should see values like:
16~40
09~15

What is the input mask property used either in the table or on forms?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

PHV,
We seem to think alike. There are so many ways that Access provides for users to get lost...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

Hey Duane, it was my guess too.
It's why I asked for all the field properties.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaime]

PHV and dhookom,

U were both right,
there has been made use of an imput mask: 00:00
other field:

Field Size: 5
Input mask: 00:00
Validation rule: Left([txtBeginStoring],2)<="23" And Right([txtBeginStoring],2)<="59"
Required: No
Allow zero length: no
Indexed: no
Unicode compression: Yes


dhookom,

the last query U said gave following error:


Syntax error (missing operator) in query expression 'Newfield: Replace([txtBeginStoring],":","~")'


gaime

 

[PHV]

And what about this ?
Format(CDate(Left(txtEindeStoring,2) & ":" & Right(txtEindeStoring,2) & ":00")-CDate(Left(txtBeginStoring,2) & ":" & Right(txtBeginStoring,2) & ":00"),"hh:nn") AS Downtime

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaime]

Cheers mate,

U did it. Works perfect!!!!

Thx to both of U
U made my day

gaime

 

[dhookom]

This justifies my personal preferences to NEVER use these properties of a field:
Input Mask
Caption
Lookup
Format
These can just cause confusion when used in a table definition.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

Duane, I totally agree.
The presentation layer has nothing to do with the storage.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaime]

Yet another question arises;

overnight there might be downtimes from 23.30 to 00.10 (the following day but still counted by the shift of the previous day)

Then we see the time as 23.30 day 4424 (for example) and 00.10 day 4424.
I thougt of using a validation: If Endtime (EindeStoring) < Begintime (BeginStoring) then downtime = (EindeStoring + 24h) - EindeStoring.

How do I translate this best to sql that works with access?


thx,
gaime

 

[Gaime]

It should be:

validation: If EindeStoring < Begintime then downtime = (EindeStoring + 24h) - BeginStoring.

ofcourse


gaime

 

[PHV]

Take a look at the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaime]

This string doesn't work,


IIf([txtBeginStoring]<=[txtEindeStoring], Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"hh:nn"), Format(CDate(Left([txtEindeStoring]+24,2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"hh:nn")) As Downtime,



I'm new at this, can you help me out some more?

thx,
gaime

 

[Gaime]

I guess it's the +24 piece that's wrong

gaime

 

[PHV]

You may try this:
IIf([txtBeginStoring]<=[txtEindeStoring], Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"hh:nn"), Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"hh:nn")) As Downtime,


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaime]

Thx,

works very nice.

gaime