Query to convert EST to BST - using SWITCH to return a Date/Time (AC2007) (1 Viewer)

[AOB]

Hi guys,

Trying to import some data from a linked Excel spreadsheet into a local table. One of the fields is a Date/Time type and is recorded in EST (Eastern Standard Time). I want to keep this field for posterity but also add a separate field with the corresponding time as per BST

For clarity, daylight savings time comes into effect this year on 26th Oct in the UK and 2nd Nov in the US. So generally, there is a 5 hour difference between the two time zones, apart from the period between these two dates, when it is only 4 hours.

Here is my query - I am using a SWITCH function to create the BST field

INSERT INTO tblTransactions
 
SELECT ltbPayments.ID AS Reference, ltbPayments.VALUEDATE AS ValueDate, ltbPayments.LOCALAMOUNT AS Amount, ltbPayments.USDAMOUNT AS AmountUSD, tblAccounts.AccountID AS AccountID, ltbPayments.TRANSACTIONTIME AS TransactionTimeEST, 
 
[COLOR=blue]SWITCH(DateValue(ltbPayments.TRANSACTIONTIME) < DateSerial(2014,10,26) Or DateValue(ltbPayments.TRANSACTIONTIME) >= DateSerial(2014,11,2), ltbPayments.TRANSACTIONTIME + TimeSerial(5,0,0),[/COLOR]
[COLOR=blue]       DateValue(ltbPayments.TRANSACTIONTIME) Between DateSerial(2014,10,26) And DateSerial(2014,11,2), ltbPayments.TRANSACTIONTIME + TimeSerial(4,0,0)) AS TransactionTimeBST[/COLOR]
 
FROM (ltbPayments 
 
INNER JOIN tblCurrencies ON ltbPayments.CURRCODE = tblCurrencies.CurrencyCode) 
INNER JOIN tblAccounts ON (tblCurrencies.CurrencyID = tblAccounts.CurrencyID) AND (TRIM(ltbPayments.BANKACCOUNT) = tblAccounts.AccountNumber);

When I run the query, after it tells me how many rows I'm going to append, I get the following warning :

Microsoft Office Access set 146044 field(s) to Null due to a type conversion failure

...namely, the TransactionTimeBST field.

So - how do I explicitly specify the output of the SWITCH function to be in Date/Time format (I presume, by default, it's returning Text, which contradicts the table properties of tblTransactions & the TransactionTimeBST field?...)

Many thanks!

Al

 

[AOB]

...seems SWITCH doesn't like Date/Time types... according to this anyway...

So I switched it (pun intended) for a nested IIf instead :

IIf(DateValue(ltbPayments.TRANSACTIONTIME) Is Null, Null, 
  IIf(DateValue(ltbPayments.TRANSACTIONTIME) Between DateSerial(2014,10,26) And DateSerial(2014,11,2), 
  DateAdd("h", 4, ltbPayments.TRANSACTIONTIME), 
  DateAdd("h", 5, ltbPayments.TRANSACTIONTIME))) AS TransactionTimeBST