Converting Short Text to Time

[robsmith]

Hi,

I have some csv files that contain start times for events. Some records have no start time. I have pasted an extract below.

Access 2016 imports them as short text so I am trying to convert to a time format. I already have a macro that converts dates from a separate field using CDate:

CurrentDb.Execute "UPDATE Matches SET Matches.ConvDate = CDate(Date)", dbFailOnError

I'm not clear whether I can use CDate for this or if I need to use TimeValue.

Can anyone point me in the right direction?

Thanks


startTime
18
14.3
18


19.4
19.2
19.2
13.3
18
14.3
18
18
18
18
10
14.3
14.3
18
18
18
18
14.3
14.3
18
18
18
18
10
14.3
18
18
18
18
18
14.3
14.3
14.3
18
18
19
15
15
19
19.4
19.1
19.4
18.1
18.15
19.1
19.1
19.1
19.1
18.1
18.4
18.1
18.15
19.1
18.1
19.1
16.1
18.4
19.1
19.1
19.4
19.4
18.4
19.4
18.1
18.15
19.1
19.4
19.4
18.4
16.2
19.25

[pbaldy]

Do those represent the hour of the day? For starters, your SQL is using today's date, not the field value. Perhaps:

CDate(ConvDate / 24)

[CJ_London]

also need to clarify whether 14.3 id 2:30pm or 2:20pm

[robsmith]

Thanks guys. Apologies, my post wasn't clear.

startTime is a separate field to Date. I've tried

CurrentDb.Execute "UPDATE Matches SET Matches.ConvTime = CDate(startTime / 24)", dbFailOnError

but I get a 3061 error saying too few parameters.

Ajax, 14.3 is 2.30. And 18 is 6.00

[pbaldy]

"doesn't work" doesn't really help. In what way doesn't it work? If 14.3 is 2:30, you're probably going to need to parse the data. Anything left of the decimal plus ":" plus anything right of the decimal. I might create a public function that took the string as an input and returned a date/time.

[robsmith]

Hi Paul,

I think I must have been editing my post when you posted.

It is a 3061 error saying too few parameters that I get.

[pbaldy]

Likely either something is spelled wrong or one of the fields specified doesn't exist in that table. Basically that's Access telling you it can't find something.

[robsmith]

Thanks Paul. That must be the empty fields.

[pbaldy]

If so you could add a criteria:

CurrentDb.Execute "UPDATE Matches SET Matches.ConvTime = CDate(startTime / 24) WHERE startTime Is Not Null", dbFailOnError

That assumes they don't have a zero length string in them.

[CJ_London]

Ajax, 14.3 is 2.30

in which case your formula won't provide the right time

?cdate(14.3/24)
14:18:00

?cdate(19.25/24)
19:15:00


you need to use something like this to covert .3 to 30 minutes

?cdate(format(14.3,"0.00"))
14:30:00

?cdate(format(19.25,"0.00"))
19:25:00

[pbaldy]

Interesting, I wouldn't have expected the Format() function to affect the CDate() like that. Apparently it makes assumptions based on the string.

?cdate("14.30")
2:30:00 PM
?cdate("14.3")
2:03:00 PM

[robsmith]

Thanks for all your help with this guys. I couldn't get anything to work unfortunately but have come up with a workaround. Thanks again.