SELECT INTO query pulls all records in defiance of a WHERE clause

[EagleOne]

2003

The following statement causes all records to be transferred in defiance of the WHERE statement.

dBs.Execute "INSERT INTO CHOOSEOffsets SELECT * FROM CHOOSERev" & _
" WHERE " & StartActualAmt & " = " & rs!Amt & " And " _
& "(('" & rs!DOV_NUMBER & " = " & DovNmbr & ") Or (" & rs!DOC_NUMBER & " = " & DocNmbr & "'))"

It seems that the WHERE clause is either ignored or nor allowed in an INSERT INTO statement.

Any assistance appreciated! Thanks

EagleOne

 

[Dirk Goldgar]

2003

The following statement causes all records to be transferred in defiance
of the WHERE statement.

dBs.Execute "INSERT INTO CHOOSEOffsets SELECT * FROM CHOOSERev" & _
" WHERE " & StartActualAmt & " = " & rs!Amt & " And " _
& "(('" & rs!DOV_NUMBER & " = " & DovNmbr & ") Or (" & rs!DOC_NUMBER & " =
" & DocNmbr & "'))"

It seems that the WHERE clause is either ignored or nor allowed in an
INSERT INTO statement.

I guarantee that WHERE clauses do apply to append queries, so if your query
is inserting unexpected records, I suggest that you build the SQL statement
first in a string variable and then inspect the value of that variable to
see what the statement actually says. For example,

Dim strSQL As String

strSQL = _
"INSERT INTO CHOOSEOffsets SELECT * FROM CHOOSERev" & _
" WHERE " & StartActualAmt & " = " & rs!Amt & " And " _
& "(('" & rs!DOV_NUMBER & " = " & DovNmbr & _
") Or (" & rs!DOC_NUMBER & " = " & DocNmbr & "'))"

Debug.Print strSQL '** REMOVE WHEN FINISHED DEBUGGING

dBs.Execute strSQL, dbFailOnError

 

[EagleOne]

Thanks Dirk

I resisted for the last time the urge of not using StrSQL by trying to code direct.

There is a reason why the pros do that.

 

[Ken Sheridan]

Your WHERE clause contains only variables, no column name(s), so if the
expression as a whole evaluates to TRUE all rows from CHOOSERev will be
inserted into CHOOSEOffsets. If StartActualAmt, DovNmbr and DocNmbr are
columns in the CHOOSERev tables whose values will determine which rows are to
be inserted then their names should be part of the literal strings.

If you can explain in more detail the logical basis on which SQL statement
is intended to restrict the rows to be inserted then we'd hopefully be in a
better position to advise.

Ken Sheridan
Stafford, England

 

[EagleOne]

You are correct. I was not referring to the Fields properly, I have since corrected that.

Thank you.