one of 6 update queries not working

[liddlem]

Hi All
I am building a tool that will create academic reports for teachers.
The idea is that the teacher assigns a 'generic' comment to each of the grade options in their subject
The following are fake comments to illustrate the point.
Grade 'A' - Sname was an active participant in the class this term. He attended lsn lessons and scored pts points for good behaviour.
Grade 'B' - Sname sometimes took part in class activites. He attended lsn lessons and scored pts points for good behaviour.
Grade 'C' - Sname seldom participated in class activites. He attended lsn lessons and scored pts points for good behaviour.

When the teacher assigns the grade (A,B or C) to the student, their report comment is updated accordingly.
When all grades are assigned, I need to update the comments, replacing
'Sname' with the students first name (working) and
'he' with 'she' (this is working well) and
'him' with 'her' (this is working well) and
'himself' with herself' etc for the girls (Also working well)

I also replace
'lsn' with the count of lessons that the student attended (this is working well) and
and finally, I try to replace
'pts' with the number of points scored during the term (BUT THIS ONE DELETES ALL THE CONTENT OF THE FIELD IN ALL OF THE RECORD)

The only things that can think of are either
1. Something is going wrong when the score is converted from a number to string (but then why does the 'lsn' conversion work?) or
2. It has something to do with the fact that I am updating an RTF (long text) field. (So there may be underlying html tags in the field) but why would that cause me to loose all the comments)

The following are my queries.
'Change gender context from 'he' to 'she' -- This works

UPDATE DBO_FLX_RPT_Results INNER JOIN dbo_STU_STUDENT ON DBO_FLX_RPT_Results.ID_Student = dbo_STU_STUDENT.ID_Student SET DBO_FLX_RPT_Results.Comment = Replace([Comment]," he "," she ")
WHERE (((DBO_FLX_RPT_Results.ID_FLX_Rpt_Subject)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![Get_Subject]) AND ((DBO_FLX_RPT_Results.TT_Yr)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![FindYr]) AND ((DBO_FLX_RPT_Results.TT_Term)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![FindTerm]) AND ((dbo_STU_STUDENT.PERS_Gender)="FEMALE"));

'update the number of lessons attended : -- This works

UPDATE DBO_FLX_RPT_Results INNER JOIN dbo_STU_STUDENT ON DBO_FLX_RPT_Results.ID_Student = dbo_STU_STUDENT.ID_Student SET DBO_FLX_RPT_Results.Comment = Replace([Comment],"lsn",Str([DBO_FLX_RPT_Results].[LessonCount]))
WHERE (((DBO_FLX_RPT_Results.ID_FLX_Rpt_Subject)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![Get_Subject]) AND ((DBO_FLX_RPT_Results.TT_Yr)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![FindYr]) AND ((DBO_FLX_RPT_Results.TT_Term)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![FindTerm]));

HOWEVER - This one deletes all the data in the field - on EVERY RECORD

UPDATE DBO_FLX_RPT_Results INNER JOIN dbo_STU_STUDENT ON DBO_FLX_RPT_Results.ID_Student = dbo_STU_STUDENT.ID_Student SET DBO_FLX_RPT_Results.Comment = Replace([Comment],"pts",Str([DBO_FLX_RPT_Results].[Score]))
WHERE (((DBO_FLX_RPT_Results.ID_FLX_Rpt_Subject)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![Subj_ID]) AND ((DBO_FLX_RPT_Results.TT_Yr)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![FindYr]) AND ((DBO_FLX_RPT_Results.TT_Term)=[Forms]![NAV_FLX]![NavigationSubform].[Form]![FindTerm]));

SQL 2016 back-end / Access 2016 front-end.
I have checked the data types and formats of both the SCORE and LESSONCOUNT fields - They are identical. (in both SQL and Access)

I have also deleted and recreated both the table and the query, several times.
Any ideas what going on ?

 

[RuralGuy]

Boy, they sure look identical. Try *importing* your db into a fresh new db and see if the issue follows. It only takes a minute.
https://btabdevelopment.com/how-to-import-everything-into-a-new-mdb-file/

 

[isladogs]

In the two that work, you reference 'Get_Subject' in the subform.

In the one that doesn't work, you refer to 'Subj_ID' instead for the same field.

Possibly relevant?

 

[liddlem]

@RuralGuy - Thanks, but this didn't make any difference either

@Ridders - well spotted, but (sorry to mess you around) . . . the 'Get_Subject' field is a combo box. I was wondering whether the 'faulty' query might have been mis-interpreting the value of column(0) as I hide the column from the user.
As part of my debug process I then assigned the value to an unbound 'SUBJ_ID' field to see if that made any difference ? . . . . Nope!

 

[isladogs]

OK I'm out of ideas - you seem to have tried lots of things already

Perhaps you can upload a stripped down copy of your db for someone to look at

 

[liddlem]

Hi Ridders (and other contributors)
After importing all the data from SQL to Access I ran some tests anf found it to be working just fine. (imagine my puzzled look here.)
So i though that the issue must have something to do with the SQL back-end.
...
Six hour later (after backup, drop database, rebuild from scratch)....
the problem still existed!
Ok - Lets test record-by-record!
Aha . . . (now don't laugh) . . .
I just ONE record has a SCORE of NULL, then the COMMENT field of ALL records are updated to NULL!
I have fixed the problem with Nz!
Thanks all for your input.

P.S. How do i mark the issue as 'Solved'

 

[isladogs]

Hooray
Glad it didn't take long to find out why....!!!

Might be worth adding Nz to all queries in case other problems arise elsewhere at a later date

Click the down arrow next to Thread Tools to mark it solved

 

[RuralGuy]

Thanks for the update. Boy those Nulls can sure be a pest can't they?