Hi,
I have just migrated the back end of my application from an Access DB to an Azure SQL instance. The transition has mostly gone fine but I am encountering a problem with a VB function stored in the front-end that I cannot fix.
I have the following code in the function:
Code:
Dim StartYear as Int
Dim Itemqry as String
Dim itemRS as Recordset
itemqry = "Select lineitemID, purchaseprice, dateofpurchase from lineitems WHERE ExcludeFromOH = False"
Set itemsrs = dbs.OpenRecordset(itemqry, dbOpenDynaset, dbSeeChanges)
itemsrs.MoveLast
totalcount = itemsrs.RecordCount
itemsrs.MoveFirst
With itemsrs
Do Until .EOF
StartYear = Year(itemsrs.Fields(2))
Do some more stuff
End with
The code is failing with a type mismatch error on the
Code:
StartYear = Year(itemsrs.Fields(2))
line, where I take the year component of the "DateofPurchase" column in the LineItems table and store it in the StartYear variable for use later in the function.
Doing some debugging, I have noticed that the datatype for this column in the SQL database has been set to "datetime2" by the migration tool, whereas it is just "date/time" in the Access DB. I have dumped the value of itemsrs.fields(2) into a debug window in both systems and in the Access DB it is returning, for example:
Whereas in the SQL DB it is returning:
Code:
15/12/2022 00:00:00.000000
Which is where I suspect the problem is.
Does anyone have any suggestions for how I overcome this? Is there a different way I should be using to extract the year component from the date field? Or do I somehow need to update all of the values in the SQL DB column to remove the trailing zeros?
Hoping someone can help