Only half of this Query works

[TheSearcher]

sql2 = "INSERT INTO tbl_Evaluation_Archive (Client_Id, Client_Last_Name, Client_First_Name, F1, F2, F3, F4, F5, F6, F7A, F7B, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, "
sql2 = sql2 & "F19, F20, F21, F22, F23, F24, F25, F26, F27, F28A, F28B, F29, F30, F30_PRE, F30_POST, F31, F31_PRE, F31_POST, F32, F32_PRE, F32_POST, F33, F33_PRE, F33_POST, F34, F34_PRE, "
sql2 = sql2 & "F34_POST, F35, F35_PRE, F35_POST, Comments, Last_Modified_Date, Modified_By, Completed, Completion_Date, Completed_By, New_Score_4, New_Score_16, New_Score_27, New_Score_28A, New_Score_28B) "
sql2 = sql2 & "SELECT tbl_Evaluation.Client_Id, tbl_Evaluation.Client_Last_Name, tbl_Evaluation.Client_First_Name, tbl_Evaluation.F1, tbl_Evaluation.F2, tbl_Evaluation.F3, tbl_Evaluation.F4, tbl_Evaluation.F5, "
sql2 = sql2 & "tbl_Evaluation.F6 , tbl_Evaluation.F7A, tbl_Evaluation.F7B, tbl_Evaluation.F8, tbl_Evaluation.F9, tbl_Evaluation.F10, tbl_Evaluation.F11, tbl_Evaluation.F12, tbl_Evaluation.F13, "
sql2 = sql2 & "tbl_Evaluation.F14, tbl_Evaluation.F15, tbl_Evaluation.F16, tbl_Evaluation.F17, tbl_Evaluation.F18, tbl_Evaluation.F19, tbl_Evaluation.F20, tbl_Evaluation.F21, tbl_Evaluation.F22, "
sql2 = sql2 & "tbl_Evaluation.F23, tbl_Evaluation.F24, tbl_Evaluation.F25, tbl_Evaluation.F26, tbl_Evaluation.F27, tbl_Evaluation.F28A, tbl_Evaluation.F28B, tbl_Evaluation.F29, tbl_Evaluation.F30, "
sql2 = sql2 & "tbl_Evaluation.F30_PRE, tbl_Evaluation.F30_POST, tbl_Evaluation.F31, tbl_Evaluation.F31_PRE, tbl_Evaluation.F31_POST, tbl_Evaluation.F32, tbl_Evaluation.F32_PRE, tbl_Evaluation.F32_POST, "
sql2 = sql2 & "tbl_Evaluation.F33, tbl_Evaluation.F33_PRE, tbl_Evaluation.F33_POST, tbl_Evaluation.F34, tbl_Evaluation.F34_PRE , tbl_Evaluation.F34_POST, tbl_Evaluation.F35, tbl_Evaluation.F35_PRE, "
sql2 = sql2 & "tbl_Evaluation.F35_POST, tbl_Evaluation.Comments, tbl_Evaluation.Last_Modified_Date, tbl_Evaluation.Modified_By, tbl_Evaluation.Completed, tbl_Evaluation.Completion_Date, "
sql2 = sql2 & "tbl_Evaluation.Completed_By, tbl_Evaluation.New_Score_4, tbl_Evaluation.New_Score_16, tbl_Evaluation.New_Score_27, tbl_Evaluation.New_Score_28A, tbl_Evaluation.New_Score_28B "
sql2 = sql2 & "FROM tbl_Evaluation "
sql2 = sql2 & "WHERE tbl_Evaluation.Client_Id = '" & Globals.glb_Client_id & "' and tbl_Evaluation.UID = " & UID

Everything gets inserted perfectly as it should except the following fields: New_Score_4, New_Score_16, New_Score_27, New_Score_28A, New_Score_28B. They are the last 5 fields in the query. They always end up blank after the insert into tbl_Evaluation_Archive. I tried to use a message box to display the query but the query has too many characters and it gets cut off.
Any help will be greatly appreciated.

 

[MarkK]

What is an F17 and how do you distinguish it from, say, an F33 or an F7A?

 

[plog]

Any help will be greatly appreciated.

Fix your tables. You are storing values in field names--that is incorrect. Also, but most likely relatedly, when you start numerating your field names, its time for a new table as well.

Instead of a field for every F#, you should have a record for it. Additionally, all those fields suffixed with PRE and POST should go as well. PRE and POST should be stored with the record as a value in a field, not in the field's name.

You really need to read up on normalization, because the issue you have now (and the other issues you will face) is a symptom of your poor table set up.

 

[Mark_]

For myself, I would put a very large text box on the screen. Prior to executing your SQL statement, set the text box equal to your SQL string. This way you can see EXACTLY what you are doing.

As others have noted, your naming convention and file layout should be addressed.

 

[boerbende]

What I would suggest is to use debug.print SQL2 in the immediate window.
Then copy this result to the query builder and debug the query until it works.
Don't make things more complex but simplify it first before debugging.
Try first to minimize your query to that part which does not work.

 

[Pat Hartman]

Ditto to the other comments.

In addition, have you verified that there is actually NO VALUE in the selected records for those fields?

 

[TheSearcher]

Thank you all for your help!
If a database is created with poor normalization and unconventional field names are used in its tables the queries should still work, right? Assuming, of course, that the syntax is correct.
The issue with my query was that the 5 fields that were blank had not yet been populated in tbl_Evaluation. They became populated only after the query was run. Therefore it makes sense that they were blank!
It was just a logical error.

 

[MarkK]

If a database is created with poor normalization and unconventional field names are used in its tables the queries should still work, right? Assuming, of course, that the syntax is correct.

Yes, and if you build a roof first, then build a house on top of the roof, absolutely, all your doors will swing just fine, as long as you install them plumb and level. Happy move-in day.

 

[TheSearcher]

If you build a roof first and then build a house on top of the roof that would be stupid. But if you build a roof first and place it on top of the house everything should be fine. I inherited this database. It made more sense to work with what I had than to redesign the entire thing. The company that hired me would never have agreed to pay me to completely redesign it.

 

[Mark_]

Ah, so your installing the doors that go into the chimney?

 

[Pat Hartman]

have you verified that there is actually NO VALUE in the selected records for those fields?

AHA!! so I nailed it. I have found that applications that are poorly structured also don't bother with referential integrity, unique indexes, required fields or validation code that is properly placed in the form's BEFOREUPDATE event. If they validate at all, the code is in the wrong events and so isn't effective.

You might want to look into some of those common failures as a way to make the app more robust.

I've been developing software for more than 50 years and one of the things I've learned very painfully, is that it is frequently better to redo at least parts of an app if it is poorly structured and in need of modification. I try not to change things that are not broken and do not need to be enhanced but if I have to touch it, I fix it if I need to.

 

[Mark_]

The company that hired me would never have agreed to pay me to completely redesign it.

Something you may wish to do, start a log of problems that you encounter and note what kind(s) of problems they generate.

For the work you are doing now you can annotate that the poor table design cost N amount of time to work around, revealed X other issues, and can cause Y to happen if not addressed either through a fix of the system or a rework of their training procedures.

Then put in "And I can get you in touch with some people who may be willing to touch this as a rebuild". You don't want to get sucked into "I'll rewrite it for you", but if you can demonstrate that there are issues that could be affecting their bottom line you can angle for getting the job of identifying what is going wrong now. That should lead to "And I can help you write the spec for what you will need to replace this with".

Be the consultant that they have to "convince" should fix it for them. But don't leave them thinking that having linoleum roofing is a good idea.