SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1186570%22%20slang%3D%22en-US%22%3EMs%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1186570%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20developed%26nbsp%3Ban%20Access%202016%20application%20with%20an%20insert%20query%20from%20VBA.%3C%2FP%3E%3CP%3EThis%20insert%20query%20is%20reliant%20on%20a%20select%20query%20for%20input.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20functionality%20works%20correctly%20on%20my%20pc%2C%20but%20the%20select%20query%20generates%20additional%20output%20fields%20not%20included%20in%20the%20query%20which%20causes%20the%20insert%20to%20fail%20due%20to%20a%20mismatch%20in%20columns%20between%20the%20insert%20table%20and%20query%20result%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20this%20be%20a%20bug%3F%3C%2FP%3E%3CP%3EPlease%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EGertdj%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1186570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188454%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188454%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20more%20correctly%20state%20the%20issue...%3C%2FP%3E%3CP%3EThe%20functionality%20works%20correctly%20on%20my%20Dev%20PC%2C%20but%20the%20issue%20occurs%20when%20running%20the%20program%20on%20another%20PC%2C%20i.e.%20generates%20additional%20output%20fields.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189310%22%20slang%3D%22es-ES%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189310%22%20slang%3D%22es-ES%22%3ESame%20application%20version%20and%20systems%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189360%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189360%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564938%22%20target%3D%22_blank%22%3E%40Gertdj%3C%2FA%3E%26nbsp%3BHow%20about%20posting%20the%20query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189381%22%20slang%3D%22es-ES%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189381%22%20slang%3D%22es-ES%22%3EAnd%20details%20of%20versions%20of%20the%20two%20machines...%20But%20start%20by%20looking%20at%20that%20query...%20It%20seems%20to%20be%20a%20good%20first%20step....%20(My%20answer%20is%20just%20supposition%2C%20including%20supposedly%20that%20you%20cons%20it.%20it's%20correct....)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195581%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195581%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%26nbsp%3BWindows%2010%2C%20Office%20365%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195593%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F91451%22%20target%3D%22_blank%22%3E%40Gustav%20Brock%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EstrSql2%20%3D%20%22INSERT%20INTO%20tbl_PayRun%20(%20PerId%2C%20PayDate%2C%20HoursWorked%2C%20KmTravelled%2C%20HourlyRate%2C%20TravelRatePerKm%2C%20TotalWorked%2C%20TotalKmTravelled%2C%20TravelCostAmount%2C%20TotalEarnings%2C%20TaxRate%2C%20Tax%2C%20UIFEmployee%2C%20UIFEmployer%2C%20TotalDeductions%2C%20NettPay%20)%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22SELECT%20Q_Earnings_Captured.PerId%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3EstrPayDate%20%26amp%3B%20%22%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.HoursWorked%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.KmTravelled%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.HourlyRate%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.TravelRatePerKm%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.TotalWorked%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.TotalKmTravelled%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.TotalTRavelCost%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Q_Earnings_Captured.TotalEarnings%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22tbl_Rates.TaxRate%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22%5BTotalEarnings%5D*%5BTaxRate%5D%20AS%20PAYE%22%20%26amp%3B%20%22%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3EUIF_Actual%20%26amp%3B%20%22%20AS%20UIF_EE%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3EUIF_Actual%20%26amp%3B%20%22%20AS%20UIF_ER%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22%5BPAYE%5D%2B%5BUIF_EE%5D%20AS%20TotalDeductions%2C%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22%5BTotalEarnings%5D-%5BTotalDeductions%5D%20AS%20NettPay%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22FROM%20Q_Earnings_Captured%20INNER%20JOIN%20tbl_Rates%20ON%20Q_Earnings_Captured.PerId%20%3D%20tbl_Rates.PerId%22%20%26amp%3B%20vbCrLf%20%26amp%3B%20_%3CBR%20%2F%3E%22Where%20Q_Earnings_Captured.PerId%20%3D%20%22%20%26amp%3B%20rs1!PerId%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195608%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564938%22%20target%3D%22_blank%22%3E%40Gertdj%3C%2FA%3E%26nbsp%3BIt%20could%20be%20%3CEM%3EstrPayDate%3C%2FEM%3E.%20It%20should%20be%20formatted%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3EstrPayDate%20%3D%20%22%23%22%20%26amp%3B%20Format(PayDate%2C%20%22yyyy%5C%2Fmm%5C%2Fdd%22)%20%26amp%3B%20%22%23%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195622%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F91451%22%20target%3D%22_blank%22%3E%40Gustav%20Brock%3C%2FA%3E%26nbsp%3BThank%20you%20-%20I%20will%20try%20that%2C%20but%20want%20to%20state%20again%20that%20the%20code%20works%20perfectly%20on%20two%20pc's%20and%20is%20only%20producing%20extra%20output%20fields%20on%20the%20third...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195628%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564938%22%20target%3D%22_blank%22%3E%40Gertdj%3C%2FA%3E%26nbsp%3BAnd%20which%20are%20those%20additional%20fields%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195634%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F91451%22%20target%3D%22_blank%22%3E%40Gustav%20Brock%3C%2FA%3E%26nbsp%3B-%20Expr1%20and%20Expr2%20-%20both%20with%20a%20value%20of%2048%20that%20is%20not%20anywhere%20in%20my%20dataset%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195676%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564938%22%20target%3D%22_blank%22%3E%40Gertdj%3C%2FA%3E%26nbsp%3BAnd%20where%20are%20the%20positioned%3F%20It%20could%20be%20decimals%20normally%20having%20a%20dot%2C%20now%20a%20comma%20as%20the%20decimal%20separator.%20Always%20convert%20decimals%20to%20text%20with%20%3CEM%3EStr%3C%2FEM%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3ETextValue%20%3D%20Str(DecimalValue)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195697%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F91451%22%20target%3D%22_blank%22%3E%40Gustav%20Brock%3C%2FA%3E%26nbsp%3B-%20Thank%20you!%20That%20makes%20sense%20-%20I%20will%20check%20it%20out%20tonight%20and%20let%20you%20know%20asap.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1199968%22%20slang%3D%22en-US%22%3ERe%3A%20Ms%20Access%20different%20results%20from%20same%20query%20on%20different%20pc's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1199968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F91451%22%20target%3D%22_blank%22%3E%40Gustav%20Brock%3C%2FA%3E%26nbsp%3B%20-%20I%20have%20checked%20the%20system%20settings%20on%20the%20PC%20that%20was%20misbehaving%20and%20found%20that%20the%20decimal%20separator%20was%20indeed%20set%20to%20a%20comma.%20After%20changing%20this%20to%20dot%20all%20is%20working%20as%20expected!%3CBR%20%2F%3EThank%20you%20for%20the%20excellent%20advise!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

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. 

Highlighted
Misma versión de aplicación y sistemas?
Highlighted

@Gertdj How about posting the query?

Highlighted
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....)
Highlighted

Yes, Windows 10, Office 365 

Highlighted

@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

Highlighted

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

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

 

Highlighted

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

Highlighted

@Gertdj And which are those additional fields?

Highlighted

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

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

 

Highlighted

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

Highlighted

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

Related Conversations
Doubts about Access Office 365
Devryk in Access on
1 Replies
Euro 2021 results app project
Diplomike in Access on
0 Replies
Refreshing calculated controls
Storming in Access on
7 Replies
question about access microsoft 365
NancyLWein in Access on
6 Replies