SOLVED

Ms Access different results from same query on different pc's

Copper Contributor

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?

Please assist.

 

Regards

Gertdj

13 Replies

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. 

Misma versión de aplicación y sistemas?

@Gertdj How about posting the query?

Y detalles de versiones de las dos maquinas... Pero empezar viendo esa consulta... Parece ser un buen primer paso.... (Mi respuesta es solo suposición, incluyendo supuestamente que la cons. está correcta....)

Yes, Windows 10, Office 365 

@Gustav Brock 

 

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

@Gertdj It could be strPayDate. It should be formatted like:

strPayDate = "#" & Format(PayDate, "yyyy\/mm\/dd") & "#"

 

@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...

@Gertdj And which are those additional fields?

@Gustav Brock - Expr1 and Expr2 - both with a value of 48 that is not anywhere in my dataset

best response confirmed by Gertdj (Copper Contributor)
Solution

@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)

 

@Gustav Brock - Thank you! That makes sense - I will check it out tonight and let you know asap.

@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!

1 best response

Accepted Solutions
best response confirmed by Gertdj (Copper Contributor)
Solution

@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)

 

View solution in original post