Format Time

[brucey54]

Hi Folks, need some help here, I am using Access 2007.



How do you change the following format 20.30 to a time format like 20:30!


If I try to change the format within the table design option Date/Time I end up losing some data i.e. all the times starting at 17:00 onward seems to be missing?


Any help would much appreciated

 

[pr2-eugin]

You might need an UPDATE query.. Change it by using CDate..

 

[brucey54]

You might need an UPDATE query.. Change it by using CDate..

Is CDate not for changing a field to a date field?

 

[pr2-eugin]

Yes CDate takes in a String and outputs to Date/Time type.. It should preserve the Time unlike Changing the Data type (of course in Theory)..

 

[brucey54]

Yes CDate takes in a String and outputs to Date/Time type.. It should preserve the Time unlike Changing the Data type (of course in Theory)..

My time field format is 20.31 would the following syntax work?

cdate(left([Fieldname], 1,2 & ":" & Mid([Fieldname], 4, 2)?

 

[pr2-eugin]

Hmm.. There are many ways that lead to Rome..

[COLOR=SeaGreen]'I think what you wanted to use was [B]Mid[/B] functions, you had Left and Mid.. [/COLOR]
? CDate(Mid("20.30", 1, 2) & ":" & Mid("20.30", 4, 2))
20:30:00 

[COLOR=SeaGreen]'This works too..[/COLOR]
? CDate(Left("20.30", 2) & ":" & Right("20.30", 2))
20:30:00 

[COLOR=SeaGreen]'Or simply..[/COLOR]
? CDate("20.30")
20:30:00

 

[brucey54]

Hmm.. There are many ways that lead to Rome..

[COLOR=SeaGreen]'I think what you wanted to use was [B]Mid[/B] functions, you had Left and Mid.. [/COLOR]
? CDate(Mid("20.30", 1, 2) & ":" & Mid("20.30", 4, 2))
20:30:00 

[COLOR=SeaGreen]'This works too..[/COLOR]
? CDate(Left("20.30", 2) & ":" & Right("20.30", 2))
20:30:00 

[COLOR=SeaGreen]'Or simply..[/COLOR]
? CDate("20.30")
20:30:00

I'm trying to run the syntax in a select query, RegTime:CDate("20.30")
20:30:00

but no joy?

 

[pr2-eugin]

I am a bit I thought 20:30:00 is the time format you wanted? Am I missing something here?

 

[brucey54]

Yes, but I would like to run a query to display the the old time format 00.00 next to the new format 00:00, so I can check the data
i.e. so the query would look some like tthis

Old Format New format
00.00 00:00
22.04 22:24
15.29 15:29

Just want to change the data within the query, ta

 

[pr2-eugin]

Okay, if you wanted only to display the value, the you could make use of Format function..

? Format("20.30", "hh:nn")
20:30

 

[brucey54]

Okay, if you wanted only to display the value, the you could make use of Format function..

? Format("20.30", "hh:nn")
20:30

Still no joy, the times are not the same, I have attached the database any help would be much appreciated, I'm losing the plot here?

 

[pr2-eugin]

Okay.. So if the Time is set as 8.1 then is it 08:01:00? I have modified the code, added a Function to convert the date, see if that helps..

 

[brucey54]

Okay.. So if the Time is set as 8.1 then is it 08:01:00? I have modified the code, added a Function to convert the date, see if that helps..

Massive THANK YOU, I will study the code really

 

[pr2-eugin]

Good.. The code can be a bit more robust.. What I gave you is just a basic idea.. Hope it helps..

 

[CazB]

sorry for leaping in on your thread brucey but thanks Paul - I was looking for something similar to convert eg 9.45 to 00:09:45 and I've managed to cobble your code to do just that

 

[pr2-eugin]

sorry for leaping in on your thread brucey but thanks Paul - I was looking for something similar to convert eg 9.45 to 00:09:45 and I've managed to cobble your code to do just that

Ha ha.. The main idea of this Forum is many users will be able to be benefited from this.. I am glad to see the Forum being a success.. Happy to help CazB..

 

[Brianwarnock]

I cannot open accdb so cannot see Paul's code, but could you not have just used TimeSerial, perhaps with Format if necessary.

Brian

 

[pr2-eugin]

Hello Brian, I thought I changed the Format before I uploaded.. Apparently not.. Well I do not disagree that you could use TimeSerial.. As I mentioned there are so many ways you could achieve it.. This is the file in mdb format..