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]

You can convert time using TimeValue(...).

The answer to "another question" depends on when your year starts. You should be able to figure this out yourself using functions like Left(), Right(), Mid(), DateAdd(), DateSerial(), etc.

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

 

[Gaime]

I tried:

CONVERT(datatime, '[tblStoring].[txtEindeStoring]', 108) - CONVERT(datatime, '[tblStoring].[txtBeginStoring]', 108) AS Downtime,


and also:

TIMEVALUE('[tblStoring].[txtEindeStoring]') - TIMEVALUE('[tblStoring].['txtBeginStoring]) As Downtime,


No of them work.
What am I doing wrong?

thx

 

[dhookom]

Access doesn't support CONVERT() which is a SQL Server function. TimeValue() can be used with field names like:
[blue]TIMEVALUE([tblStoring].[txtEindeStoring]) - TIMEVALUE([tblStoring].[txtBeginStoring]) As Downtime,[/blue]

This will result in a number that is the part of a day. For instance .5 represents 12 hours. There is also a DateDiff() function that allows you to specify the time increments such as seconds, minutes,...

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

 

[Gaime]

dhookom,

I already tried the string U said but I get #error in every field.
What string should I use to get for example:
Begintime = 06:35
Endtime = 07:53

=> Downtime is 01:18

How can I create this new column Downtime by using the other two, and getting correct timedifferences.

thx
gaime

 

[dhookom]

Where are you using the expression ie: query, report,...?
Can you open the debug window (Press Ctrl+G) and enter
? TimeValue("07:53")-TimeValue("06:35")
Press enter and you should get a return value of:
0.05416666667
This value is the part of a full day which is how time is stored.


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

 

[Gaime]

Thx,

but is it now possible to show this timevalue in a new created column (Downtime) as 01:18?

In other words, how do I convert this timevalue to hours and minutes?

gaime

 

[dhookom]

Set the format of any control that displays this value to any date/time format you desire. It is already in the same exact format that a date/time is stored. I rarely if ever do any formatting in a query. If you feel you must have your query column look like a time value then:
Format(TimeValue("07:53")-TimeValue("06:35"),"hh:nn")

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

 

[lespaul]

Gaime,

Duane's answer eludes to this, but doesn't come right out and say it. You shouldn't store calculated fields, you should calculate when you need it. Storing a calculated field breaks 3NF.

Leslie

 

[Gaime]

Sorry to ask again,

the query U send worked perfect, but if I try to get time from a column it doesn't work.

this is the query I'm using:

Format(TimeValue("'[tblStoring].[txtEindeStoring]'")-TimeValue("'[tblStoring].[txtBeginStoring]'"),"hh:nn") As Downtime,

What am I doing wrong this time?


thx
gaime

 

[PHV]

And this ?
Format(TimeValue([tblStoring].[txtEindeStoring])-TimeValue([tblStoring].[txtBeginStoring]),"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]

PHV,

I tried your option but then I get #error in every field.

Does anybody know why that happens? (maybe because of the ':' who is set in advance?)


thx,
gaime

 

[dhookom]

When using field names, you don't include all the quotes:
[red]
Format(TimeValue("'[tblStoring].[txtEindeStoring]'")-TimeValue("'[tblStoring].[txtBeginStoring]'"),"hh:nn") As Downtime,[/red]
should be:
[green]
Format(TimeValue([tblStoring].[txtEindeStoring])-TimeValue([tblStoring].[txtBeginStoring]),"hh:nn") As Downtime,[/green]

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

 

[Gaime]

dhookom,

I also tried the string U said, but it gives an #error in every field.

what could be the problem?


thx,
gaime

 

[Gaime]

dhookom,

I also tried the string U said but it gives an "#error" in every field.

What could be the problem? Does anyone know an answer?


thx,
gaime

 

[PHV]

What happens if, for debugging purpose you add this 2 fields ?
TimeValue([tblStoring].[txtEindeStoring]) As EindeStoring
TimeValue([tblStoring].[txtBeginStoring]) As BeginStoring

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

 

[dhookom]

When you state "gives an "#error" in every field" do you actually mean the one field/column in every record?

Have you ever checked your references in a module to make sure there are no missing references?

Maybe you need to reply with the full sql view of your current query.

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

 

[Gaime]

PHV,

If I try your proposal, I get #error in every field of the columns EindeStoring and BeginStoring. So the conversion to time doesn't work.

dhookom,

my query is:


SELECT

[tblStoring].[txtDatum] AS Datum,
[tblMachine].[Cell] AS Cel,
[tblStoring].[txtFCode] AS Foutkode,
[tblStoring].[txtBeginStoring] AS [Begin],
[tblStoring].[txtEindeStoring] AS Einde,

Format(TimeValue([tblStoring].[txtEindeStoring])-TimeValue([tblStoring].[txtBeginStoring]),"hh:nn") AS Downtime,

[tblStoring].[txtVerschijnsel] AS Verschijnsel,
[tblStoring].[txtPlant] AS Plant,
[tblStoring].[txtPloeg] AS Shift,
[tblMachine].[IDMachine] AS tblMachine_IDMachine,
[tblStoring].[IDMachine] AS tblStoring_IDMachine

FROM tblMachine INNER JOIN tblStoring ON [tblMachine].[IDMachine]=[tblStoring].[IDMachine]

WHERE ((([tblStoring].[txtDatum])="4422") And (([tblMachine].[Cell])="10"));



thx,
gaime

 

[PHV]

And this ?
Format(CDate([tblStoring].[txtEindeStoring])-CDate([tblStoring].[txtBeginStoring]),"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]

PHV,

If I use:

Format(CDate([tblStoring].[txtEindeStoring])-CDate([tblStoring].[txtBeginStoring]),"hh:nn") AS Downtime


I get "00:00" in every field of the column Downtime, instead of the real downtimes.

Is there maybe another way to change the type of the field; now text, to time (short time) without loosing all records? Thus changing the core of the problem. I really can't loose any record and there are more than 20.000 records. I already tried it but the records were deleted because the data in the fields EindeStoring and BeginStoring wasn't correct anymore.

thx,
gaime