Re: Time in VBA for Excel

[Daniel Kasak]

Dan Wareham wrote:
Hello,
I have a table in my database with two fields set as Time types.
The issue I seem to have is when I use an ADO connection in VBA for 
Excel to select the two fields.

Instead of the expected format HH:MM:SS held within my recordset, I 
get the current date in the format DD/MM/YYYY. Then should it reach a 
record where the time has been set to the default 00:00:00 I get an 
error "Invalid Use of Null"

I tried outputting the data from the database to a webpage using 
scripting and I'm returned with the correct data as entered into the 
database. This is also confirmed when I view the data in the Query 
Browser.

I've gone through every VBA site possible and found no clues so 
thought there might be a MySQL wizz that knows what is going on....

Any help or pointers would be greatly appreciated!!
Thanks in advance
[EMAIL PROTECTED]

I'm not sure about Excel, but Access doesn't understand Time. You have 
to give it DateTime, in the format: '30-12-1899 HH:MM:SS'. All DateTime 
values starting with '30-12-1899' are interpreted as being a Time field. 
Pretty strange stuff. Having said that, we have a number of scripts that 
pull Time values into Excel without the issue you're experiencing. In 
some places, we just chuck a single quote at the start of the Time value:

select concat('\'', SomeTimeColumn) as MyTime
You could also try casting your values as text:
select cast(SomeTimeColumn as varchar(20) as MyTime
... or something like that. Can't remember if that's exactly how cast() 
works, but this is the general idea.

Otherwise post your table def / code and I'll test it out.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]