Feb 21 2020 03:02 AM
Feb 21 2020 03:02 AM
I have developed an Access 2016 application with an insert query from VBA.
This insert query is reliant on a select query for input.
The functionality works correctly on my pc, but the select query generates additional output fields not included in the query which causes the insert to fail due to a mismatch in columns between the insert table and query result,
Can this be a bug?
Feb 22 2020 01:17 AM
To more correctly state the issue...
The functionality works correctly on my Dev PC, but the issue occurs when running the program on another PC, i.e. generates additional output fields.
Feb 23 2020 04:42 AM
Feb 26 2020 05:11 AM
strSql2 = "INSERT INTO tbl_PayRun ( PerId, PayDate, HoursWorked, KmTravelled, HourlyRate, TravelRatePerKm, TotalWorked, TotalKmTravelled, TravelCostAmount, TotalEarnings, TaxRate, Tax, UIFEmployee, UIFEmployer, TotalDeductions, NettPay )" & vbCrLf & _
"SELECT Q_Earnings_Captured.PerId," & vbCrLf & _
strPayDate & "," & vbCrLf & _
"Q_Earnings_Captured.HoursWorked," & vbCrLf & _
"Q_Earnings_Captured.KmTravelled," & vbCrLf & _
"Q_Earnings_Captured.HourlyRate," & vbCrLf & _
"Q_Earnings_Captured.TravelRatePerKm," & vbCrLf & _
"Q_Earnings_Captured.TotalWorked," & vbCrLf & _
"Q_Earnings_Captured.TotalKmTravelled," & vbCrLf & _
"Q_Earnings_Captured.TotalTRavelCost," & vbCrLf & _
"Q_Earnings_Captured.TotalEarnings," & vbCrLf & _
"tbl_Rates.TaxRate," & vbCrLf & _
"[TotalEarnings]*[TaxRate] AS PAYE" & "," & vbCrLf & _
UIF_Actual & " AS UIF_EE," & vbCrLf & _
UIF_Actual & " AS UIF_ER," & vbCrLf & _
"[PAYE]+[UIF_EE] AS TotalDeductions," & vbCrLf & _
"[TotalEarnings]-[TotalDeductions] AS NettPay" & vbCrLf & _
"FROM Q_Earnings_Captured INNER JOIN tbl_Rates ON Q_Earnings_Captured.PerId = tbl_Rates.PerId" & vbCrLf & _
"Where Q_Earnings_Captured.PerId = " & rs1!PerId
Feb 26 2020 05:20 AM
@Gertdj It could be strPayDate. It should be formatted like:
strPayDate = "#" & Format(PayDate, "yyyy\/mm\/dd") & "#"
Feb 26 2020 05:24 AM
@Gustav Brock Thank you - I will try that, but want to state again that the code works perfectly on two pc's and is only producing extra output fields on the third...
Feb 26 2020 05:29 AM
@Gustav Brock - Expr1 and Expr2 - both with a value of 48 that is not anywhere in my dataset
Feb 26 2020 05:39 AMSolution
@Gertdj And where are the positioned? It could be decimals normally having a dot, now a comma as the decimal separator. Always convert decimals to text with Str:
TextValue = Str(DecimalValue)
Feb 26 2020 05:46 AM
@Gustav Brock - Thank you! That makes sense - I will check it out tonight and let you know asap.
Feb 27 2020 09:46 PM
@Gustav Brock - I have checked the system settings on the PC that was misbehaving and found that the decimal separator was indeed set to a comma. After changing this to dot all is working as expected!
Thank you for the excellent advise!