DST to UTC append query creates a type conversion error in access

Question

I have a table I'm using as a source for an append query that calls upon a table query, which calls upon a union query to effectively adjust the eastern prevailing time to spring forward and fall back while converting to utc. there are only three fields in the table but I keep getting "access did not import .... due to type conversion". Please Help Me out!!! Thank you in advance

below is the access sql:

{append query}

INSERT INTO somePrice ( price )
SELECT DTQuery.Price
FROM DTQuery
WHERE (((DTQuery.EPT)<>[2ndsunday]));

{DTQuery}

SELECT 
      TransposeQuery.Field3 AS [Zone]
    , DateSerial(Left([field1],4),Left(Right([field1],4),2),Right([field1],2))+[TransposeQuery]![Hour]/24 AS EPT, Val([Field8]) AS Price
    , DateValue(DateSerial(Year([EPT]),3,14))-(Weekday(DateValue(DateSerial(Year([EPT]),3,14)),1)-1)+3/24 AS 2ndSunday
    , DateValue(DateSerial(Year([EPT]),11,7))-(Weekday(DateValue(DateSerial(Year([EPT]),11,7)),1)-1)+3/24 AS 1stSunday
FROM TransposeQuery
ORDER BY 
      TransposeQuery.Field3
    , DateSerial(Left([field1],4),Left(Right([field1],4),2),Right([field1],2))+[TransposeQuery]![Hour]/24, Val([Field8]);

Stephen Turner Stephen Turner · Accepted Answer · 2011-12-17T21:07:43

First some general stuff: If you want to convert between timezones, which involves adding or subtracting a number of hours you might want to use to DateAdd function.

DateAdd("h", -2, [SourceDateTime])

This simply takes two hours off the SourceDateTime field.

Also rather than using the Left(Right( combination you can use Mid(string, start, length)

Mid("1234567890", 2, 4)

Returns 2345, but if you are dealing with dates just use DatePart

DatePart("h", "17/12/2011 08:10")

Returns 8.

As for the type conversion error, it's hard to say as you haven't given us the types of the fields in the destination table.

If you run the append query without the first line this will rule out the destination table. If it still fails then it might be in the where clause, so move the fields into the results set and make sure they are the same type. If it still fails then it must be in the source query so check DTQuery opens without any problems.

DST to UTC append query creates a type conversion error in access

1 Answers