Solved SQL problem

[John Sh]

I have a series of 5 boolean controls where any one of them being true will cause text in a control to change.
I can't get the code to work without the first criteria being true.
I have tried all manner of variations without success.
Do I need to use a single sql statement for each boolean, or should I write a convoluted function?
This is my last failed attempt.

sQry = "UPDATE (Discrepancies as A ) " & _
            "SET A.Notes = 'Spelling' " & _
            "WHERE OR A.SpellMain = True " & _
            "OR A.SpellRaw = True " & _
            "OR A.SpellSpec = True " & _
            "OR A.SpellG = True;"
     CurrentDb.Execute sQry, dbFailOnError

if I remove the "or" immediately after the WHERE, I only get a result when "spellmain" is true.
Leaving the "or" in results in an error, basically indicating it doesn't like the "or".

 

[GaP42]

You indicate the values are boolean - so do you expect to find the text "true" in each of those? If it is in fact a text field then you need to look in to text delimiters. Certainly no OR before the first criteria
A useful link: https://www.w3schools.com/sql/sql_update.asp

SELECT, FROM, WHERE - missing FROM -- FROM discrepancies

UPDATE A
SET A.Notes = 'Spelling'
FROM Discrepancies as A
WHERE ...

 

[cheekybuddha]

"WHERE OR A.SpellMain = True "

WHERE OR is invalid SQL

 

[cheekybuddha]

Do you really need to set the value of Notes to 'Spelling' at all?

Effectively it's a calculated expression based on any of your boolean fields, so can just be calculated in a query or control source.

 

[cheekybuddha]

Another thing: are all your Spell* booleans independent? (ie if one of them is true then all others must be false)

Or can more than one be true at the same time?

 

[John Sh]

Another thing: are all your Spell* booleans independent? (ie if one of them is true then all others must be false)

Or can more than one be true at the same time?

The booleans are independent of each other and can be either true or false.
They are the result of a series of spell checks. If any one of them is true then "Notes" is set to indicate a spelling error.
The text in the fields that have been spell checked and found to be in error will change to red on white so there are multiple indications of said error.
This is on a continuous form and the checks are being done before the form is opened.
I will try a case statement as it seems to be the easy way out.
but that can only be done once the form is opened.
This screen shot is a small sample of the data. Every record on the screen has an error of some sort.
Record 8664, for instance, has a Family/Genus mismatch and a spelling error in the Main family.
The "Original" records have a duplicate that is not currently shown as the form is indexed on the notes field in this instance.
So the "Notes" column is an identification tool.

 

[cheekybuddha]

So the "Notes" column is an identification tool.

OK,

but if all values in Notes (Match/Spelling/Original) are dependent on other fields in the record then the field is not required. It can be produced 'on the fly'.

When/where do you determine 'Match' and 'Original'?

 

[cheekybuddha]

This is on a continuous form and the checks are being done before the form is opened.

Please show how you are doing the checks

 

[John Sh]

Please show how you are doing the checks

It's complicated, but.
1) run a scan of all the relevant raw files using a recursive function. Every specimen in the collection has been photographed. Each raw file is about 70mb.
2) record the family name of each box of images.
3) record the Accession number of each image in a box, along with the full path, . There are some 14,000 specimens in about 400 boxes.
This information goes into a table.
4)do the same with all of the jpg files derived from the raws. The jpgs are stored in a single directory with 15 sub-directories, each limited to 500 entries. The extras are for growth.
The jpg data is stored in a separate table.
5) the data in these two tables is collated into a single table where it is combined with the data from the main collection table.
6) Through a series of some 30 sql strings the data is checked for errors such as box number variations, spelling errors, data mismatches and so on..
7) any data that is deemed error-less is discarded.
not until all of the checks are completed is the form opened.
The boolean flags are set, or not, during the processes above.
Note that "sQry" is set as private string variable in the declarations section of the module that holds all of the sql strings that run in a set sequence.

sQry = "UPDATE (Discrepancies as A ) " & _
            "SET A.Notes = 'Spelling & Match' " & _
            "WHERE A.nomatch = True " & _
            "AND A.SpellRaw = True ;"
     CurrentDb.Execute sQry, dbFailOnError
   
     sQry = "UPDATE (Discrepancies as A ) " & _
           "SET A.Notes = 'No Match' " & _
           "WHERE A.nomatch = True " & _
           "AND A.BadFamily = False;"
     CurrentDb.Execute sQry, dbFailOnError
           
     sQry = "UPDATE (Discrepancies as A ) " & _
            "SET A.Notes = 'Family' " & _
            "WHERE A.nomatch = False " & _
            "AND A.BadFamily = True;"
     CurrentDb.Execute sQry, dbFailOnError

In these code samples, the first sets the string in Notes if a family name is legitimate, "Bad_Family", set if not. and also checks if the spelling is correct, "SpellRaw", set if not.
The combination of no match and bad spelling would indicate a probable spelling error .

The second one, indicates family and genus are not a pair but the family name is valid.

the third is less likely but would indicate at least two different entries with the same error. I.e the raw file and the main data file have the same error.

This is obviously a small sample , so I hope it helps.
John

 

[June7]

SELECT, FROM, WHERE - missing FROM -- FROM discrepancies

UPDATE A
SET A.Notes = 'Spelling'
FROM Discrepancies as A
WHERE ...

This is not valid UPDATE syntax. FROM belongs with a SELECT.

John, I tested your syntax (without the bad "or") and it works for me. Appropriate records are updated.

 

[The_Doc_Man]

sQry = "UPDATE (Discrepancies as A ) " & _

This is a nit-pick, but ... Access SQL behaves oddly when dealing with parentheses that it technically doesn't need. You didn't bracket the Aliasing clause but you did syntactically isolate it.

The SQL UPDATE clause you showed us only looked at things from the only table named in the UPDATE clause, so you don't need to qualify the field names. Might need brackets [xxx] but not A.xxx for a single-table update. So that is even LESS typing than the alias method you were using.

 

[Isaac]

This is a nit-pick, but ... Access SQL behaves oddly when dealing with parentheses that it technically doesn't need. You didn't bracket the Aliasing clause but you did syntactically isolate it.

The SQL UPDATE clause you showed us only looked at things from the only table named in the UPDATE clause, so you don't need to qualify the field names. Might need brackets [xxx] but not A.xxx for a single-table update. So that is even LESS typing than the alias method you were using.

Don't you mean the exact opposite? Access SQL loves to put parenthesis that it doesn't need

 

[June7]

Sort of agree, Doc. Adding parens shouldn't hurt but not having in complex JOINs can.

And for even less typing, don't need = True or = False if these are truly Yes/No fields.
Semi-colon is also not required.

sQry = "UPDATE Discrepancies " & _
            "SET Notes = 'Spelling' " & _
            "WHERE SpellMain " & _
            "OR SpellRaw " & _
            "OR SpellSpec " & _
            "OR SpellG"

     sQry = "UPDATE Discrepancies " & _
           "SET Notes = 'No Match' " & _
           "WHERE nomatch " & _
           "AND NOT BadFamily"

 

[John Sh]

sQry = "UPDATE Discrepancies " & _
            "SET Notes = 'Spelling' " & _
            "WHERE SpellMain " & _
            "OR SpellRaw " & _
            "OR SpellSpec " & _
            "OR SpellG"

I appreciate the reduction in typing and will implement that. I actually did know that but typed the extra anyway.
Back to my original question.
The code above produces a result only if "SpellMain" is true, regardless of the state of the other flags.
The first criteria of the WHERE clause dictates the behaviour of the rest, such that "Spelling" is only added to "Notes" when "SpellMain" is true.
I also need to add "Spelling" to "Notes" when "SpellMain" is false and any of the other criteria are true.

John

 

[June7]

Again, I tested that criteria and it worked for me.
Could provide you db for analysis.

 

[The_Doc_Man]

Access SQL loves to put parenthesis that it doesn't need

The wizards do that a lot, but I've never seen them isolate an ALIAS clause. Then again, I rarely use the wizards these days so that isn't a particular proof of anything.

 

[The_Doc_Man]

The code above produces a result only if "SpellMain" is true, regardless of the state of the other flags.

Is there a chance that one or more of the Booleans is null? (Although the Inclusive OR is supposed to be able to tolerate a null in either side of the dyadic Boolean operation).

 

[isladogs]

@John Sh
You haven't stated which Access version you are using or whether you are using the Monaco SQL editor
Also you haven't said whether an error message appears when the update fails.
These may all be relevant to your issue

The code you copied in post #14 should work. However, there have been several recent bugs using Monaco involving
e.g. update queries with WHERE filters (which yours is) and with accented characters.
These bugs have been / soon will be fixed

As a first step, try disabling Monaco and see if that solves your issue

 

[CJ_London]

Are all your update queries mutually exclusive? I.e. a specific record can only appear in one update and cannot be updated by a later update?

And have you opened the query to verify all your calculated booleans are populated and correct- logic being if the sql is valid then the issue will be with the data

 

[June7]

Is there a chance that one or more of the Booleans is null? (Although the Inclusive OR is supposed to be able to tolerate a null in either side of the dyadic Boolean operation).

AFAIK, a Yes/No field cannot be Null. It is either True or False (-1 or 0). However, since Null is not -1 it would be treated as False.