Solved Variable not defined in sql string

[John Sh]

In the attached sql string, the alias "A" is not recognised at the first instance of "A.accession"
"Accession" is a valid field in the table "Discrepancies".
If I remove the alias and use the table name I get the same "variable not defined" error on "Discrepancies"
I have tried enclosing table and field names in [] to no avail.

Public Function Corrected()
    sQry = "DELETE FROM Discrepancies as A " & _
           "WHERE A.Main_Jpg = '" & A.Accession & "' & AND " & _
           "A.Main_Raw = '" & A.Accession & "' &  AND " & _
           "A.Main_Family = '" & A.Raw_Family & "' &  or " & _
           "A.Main_Family = '" & A.Spec_Family & "' &  AND " & _
           "A.Main_Box = '" & A.Raw_Box & "' &  AND " & _
           "A.SpellRaw = '" & False & "' &  AND " & _
           "A.SpellMain = '" & False & "' &  AND " & _
           "A.Spellspec = '" & False & "' &  AND " & _
           "A.nomatch = '" & False & "' &  AND " & _
           "A.SpellG = '" & False * "' & ;"
    CurrentDb.Execute sQry, dbFailOnError
End Function

 

[sonic8]

You are enclosing some of the references to A.Something in text delimiters ('). These terms will be treated as literals and not as references to table columns.
Only discrete text and date literals should be enclosed in delimiters.

 

[CJ_London]

use debug.print to see what your actual sql looks like. Why not build it in the query builder first?

Seems to me you are mixing ands and ors without brackets, making indescriminate use of delimiters and have at least one syntax error

 

[Eugene-LS]

I have tried enclosing table ...

Try:

Public Function Corrected()
' Why function??? - it does not returns any thing!
Dim sQry As String
    sQry = "DELETE FROM Discrepancies as A " & vbCrLf & _
           "WHERE ((A.Main_Jpg = 'A.Accession') AND " & _
           "(A.Main_Raw = 'A.Accession')" & vbCrLf & " AND " & _
           "((A.Main_Family = 'A.Raw_Family') OR (A.Main_Family = 'A.Spec_Family'))" & vbCrLf & " AND " & _
           "(A.Main_Box = 'A.Raw_Box') AND " & _
           "(A.SpellRaw = 0) AND " & _
           "(A.SpellMain = 0)" & vbCrLf & " AND " & _
           "(A.Spellspec = 0) AND " & _
           "(A.nomatch = 0) AND " & _
           "(A.SpellG = 0));"
    'Debug.Print sQry
    CurrentDb.Execute sQry, dbFailOnError
End Function

 

[cheekybuddha]

Eugene-LS has the answer - you forgot to declare variable sQry

 

[CJ_London]

you don't actually need to alias since there is only the one table

 

[John Sh]

Try:

Public Function Corrected()
' Why function??? - it does not returns any thing!
Dim sQry As String
    sQry = "DELETE FROM Discrepancies as A " & vbCrLf & _
           "WHERE ((A.Main_Jpg = 'A.Accession') AND " & _
           "(A.Main_Raw = 'A.Accession')" & vbCrLf & " AND " & _
           "((A.Main_Family = 'A.Raw_Family') OR (A.Main_Family = 'A.Spec_Family'))" & vbCrLf & " AND " & _
           "(A.Main_Box = 'A.Raw_Box') AND " & _
           "(A.SpellRaw = 0) AND " & _
           "(A.SpellMain = 0)" & vbCrLf & " AND " & _
           "(A.Spellspec = 0) AND " & _
           "(A.nomatch = 0) AND " & _
           "(A.SpellG = 0));"
    'Debug.Print sQry
    CurrentDb.Execute sQry, dbFailOnError
End Function

First, might I say a big thank you to all. Your assistance is invaluable.
Why the function? it's the only only to test in the immediate window. It doesn't respond to subs.
"Sqry" is a private sub in this module which holds a number of sql strings. I declared it private once, rather than declare it for each string.

I am now getting a type mismatch error. I have tried both forms of the string below with the same result.
debug print only show a copy of the test with no indication of the fault location.
I have tried the five boolean clauses with 0', false and 'false' all with the same error.
Note that the "or" statement is now "and"
I have included a screen shot of the table fields.

Public Function Corrected()
'    sQry = "DELETE FROM Discrepancies as A " & _
'           "WHERE ((A.Main_Jpg = 'A.Accession') AND " & _
'           "(A.Main_Raw = 'A.Accession') AND " & _
'           "((A.Main_Family = 'A.Raw_Family') OR (A.Main_Family = 'A.Spec_Family')) AND " & _
'           "(A.Main_Box = 'A.Raw_Box') AND " & _
'           "(A.SpellRaw = 0) AND " & _
'           "(A.SpellMain = 0) AND " & _
'           "(A.Spellspec = 0) AND " & _
'           "(A.nomatch = 0) AND " & _
'           "(A.SpellG = 0));"


    sQry = "DELETE FROM Discrepancies as A " & _
           "WHERE A.Main_Jpg = 'A.Accession' " & _
           " AND A.Main_Raw = 'A.Accession' " & _
           " AND A.Main_Family = 'A.Raw_Family' " & _
           " AND A.Main_Family = 'A.Spec_Family' " & _
           " AND A.Main_Box = 'A.Raw_Box' " & _
           " AND A.SpellRaw = 'No' " & _
           " AND A.SpellMain = 'No' " & _
           " AND A.Spellspec = 'No' " & _
           " AND A.nomatch = 'No' " & _
           " AND A.SpellG =  'No' ;"
           Debug.Print sQry
    CurrentDb.Execute sQry, dbFailOnError
End Function

 

[John Sh]

you don't actually need to alias since there is only the one table

Noted.
Thanks

 

[cheekybuddha]

sQry = "DELETE FROM Discrepancies as A " & _
           "WHERE A.Main_Jpg = A.Accession " & _
           " AND A.Main_Raw = A.Accession " & _
           " AND A.Main_Family = A.Raw_Family " & _
           " AND A.Main_Family = A.Spec_Family " & _
           " AND A.Main_Box = A.Raw_Box " & _
           " AND A.SpellRaw = False " & _
           " AND A.SpellMain = False " & _
           " AND A.Spellspec = False " & _
           " AND A.nomatch = False " & _
           " AND A.SpellG =  False ;"
           Debug.Print sQry

 

[CJ_London]

AND A.Main_Family = A.Raw_Family " & _ " AND A.Main_Family = A.Spec_Family " & _ " AND A.Main

Don’t think it can be equal to two different values? Unless raw and spec both have the same value,

 

[June7]

Why would multiple fields hold same data? Want to provide sample data and desired result? Attach file or build tables in post.

Why were you concatenating field references in original code?

Boolean constants should never be within text delimiters as they are numeric and field actually contains a number.

 

[John Sh]

I figured this out about 10 minutes after I switched the compute off. My 86 Y.O. brain doesn't always work as expected.
By way of explanation, this is the last step in compiling a list of errors in an old DB.
We have about 14,000 plant specimens that have a raw image and a jpg image each.
I scan the entire location for raw and jpg images, keeping a record of the image name and physical location, including the relevant box number, storage bay and shelf.
Each specimen has a genus and species name which are sometimes misspelled or incorrectly named.
The original db was all text fields with no checks and not a single dropdown.
So, the comparisons are against a reference list for spelling/naming errors and against an accession number when checking for images that are either not there or duplicated.
The resulting form consists of anything that has an error of some sort.
Needless to say that all critical data is now drawn from a dropdown list and dates must be valid.
While much of the data can be checked by anyone, a degree of it can only be verified by someone qualified in taxonomy, hence all this work to make their life easier.

I thank all who have contributed.
John