Interesting challenge doing a Delete in VBA w/boolean field values compared to string (1 Viewer)

larrystaley said:

Code:Copy to clipboard

strSQL = "DELETE * FROM hld22CGinRangeSelected WHERE (tblCase.[IncomeAAP?] OR tblCase.[IncomeChildSupport?] OR tblCase[IncomeFosterCare?]) NOT IN (" & strIncomeSource & ")"

For some reason, I am getting a syntax error so can't test it.

Click to expand...

If those field names are reflecting the original source tables and they have those names to disambiguate fields which would otherwise have the same name, they are referred to not as:

but as

BtW It is a terrible idea to put special characters in any object name.

You can't OR a bunch of fieldnames and apply them to an IN clause. Each field must be separately dealt with. OR only applies to values. CJ_London hinted at this in the first reply.

larrystaley said:

strSQL = "DELETE * FROM hld22CGinRangeSelected WHERE (tblCase.[IncomeAAP?] OR tblCase.[IncomeChildSupport?] OR tblCase[IncomeFosterCare?]) NOT IN (" & strIncomeSource & ")"
DoCmd.RunSQL (strSQL)

Click to expand...

Why's the NOT IN clause still in this query? PBaldy recommended:

"Field1 = True OR Field3 = True OR Field7 = True"

Click to expand...

That was my interpretation. However, you are saying then the new code would resemble as follows, as you are indicating NO More "Not In"?

strSQL = "DELETE * FROM hld22CGinRangeSelected WHERE (tblCase.[IncomeAAP?]=True OR tblCase.[IncomeChildSupport?]=True OR tblCase[IncomeFosterCare?]=True)

Again, this is only a sample, using first three income sources based on fields in the table. However, what appears to be missing here is comparison to the input string parameter. Again, in my example, user requests any clients with AAP income, ChildSupport income, or Foster Care Income. In my test case, there are no clients with AAA income, no clients with ChildSupport income, but there are 6 clients with Foster Care income.

While I am not authorized to provide the database the attached document should help. I have scrubbed the names, but this shows my test data, indicating all clients and what incomes they have (based on checkmarks).

Please let me know if that helps.

Well, my suggestion was to "not delete"; it was to select using the user's choices. If you insist on deleting, I guess you'd have to include everything EXCEPT what the user chose.

Hi Paul,

Yes, I am trying straight SELECT now but as this SELECT is now not an action query must open with OPENQUERY. Looks like that should work but

strSQL = "SELECT * FROM hld22CGinRangeSelected WHERE ([IncomeAAP?] = True OR [IncomeChildSupport?] = True OR [IncomeFosterCare?] = True)"

DoCmd.OpenQuery "strSQL"

However, I am now getting an error:
"Kin Database - Version 13 can't find the object 'strSQL'

I have seen others with this error. Not sure what it could be referring unless something with the hld22CGInRangeSelected table ?

In any case, sorry it took a while but I think I see where this approach is coming from. Create a customized string each time as a function of what the user wants. So string would ONLY contain income sources requested by the user.

Thanks again.
L. Staley

String variables in quotes become string literals. Try strSQL without quotes like

I removed the quotes, but made no difference. Received the same error.

However, I tried instead to use this with the OpenReport method as follows:

DoCmd.OpenReport stDocName, acPreview, , ([IncomeAAP?] = True Or [IncomeChildSupport?] = True Or [IncomeFosterCare?] = True)

It ran but the report was null. Was expecting 6 records to be returned.
Thanks again.

There are records that meet the criteria in the table? The argument is a string, thus:

DoCmd.OpenReport stDocName, acPreview, , "[IncomeAAP?] = True Or [IncomeChildSupport?] = True Or [IncomeFosterCare?] = True"

Hi Phil,

Yes, per my test data, there are three clients who have Foster Care income, but again none were reported. Any ideas?

Thanks again.

L. Staley

Hi Phil,

Did some more analysis, on using this WHERE condition with Open Report.

Following report query is first executed:

SELECT qry22CGInRangeSelected.*, Left([Gender],1) AS ShortGender, refEthnicity.EthnicityAbbreviation AS Eth1, refEthnicity_1.EthnicityAbbreviation AS Eth2, refEthnicity_2.EthnicityAbbreviation AS Eth3
FROM ((qry22CGInRangeSelected LEFT JOIN refEthnicity ON qry22CGInRangeSelected.Ethnicity1 = refEthnicity.Ethnicity) LEFT JOIN refEthnicity AS refEthnicity_1 ON qry22CGInRangeSelected.Ethnicity2 = refEthnicity_1.Ethnicity) LEFT JOIN refEthnicity AS refEthnicity_2 ON qry22CGInRangeSelected.Ethnicity3 = refEthnicity_2.Ethnicity;

qry22CGInRangeSelected is as follows:

SELECT hld22CGInRangeSelected6.*
FROM hld22CGInRangeSelected6 INNER JOIN qry22CGInRangeSelectedUnderlyingB ON hld22CGInRangeSelected6.CGID = qry22CGInRangeSelectedUnderlyingB.CGID;

Therefore this is the one where the WHERE condition should apply. The hld22CGInRangeSelected6 table (or copy of hld22CGInRangeSelected) is exactly where the filtering should occur. Yet it did not. Any ideas?

Thanks again.
L. Staley

Also, I just checked the hld22CGInRangeSelected6 table. The 18 records are present, 3 of which I saw identified with -1, for positive value, in the Boolean field for IncomeFosterCare?. Agree, I would not have used the ? special character in name, but I inherited that!

I feel like I should wait for Phil to answer.

Have I asked if you could attach the db here, or a representative sample?

Without trying to read the SQL, my guess is this. When applying Where conditions to fields involved in queries with Outer Joins, the need for the extra condition on a field "Or Is Null" is often overlooked.

This applies to any conditions because a Null (eg because there is no record in one of the tables) cannot be subjected to the ordinary condition.

Without it, any record that does not have a corresponding record in the table with the condition won't be included.

Hi Galaxiom and Paul,

Not authorized to show DB. However, attached is a document (IncomeSourceTesting2) that should help. It is my testbed with names scrubbed. For all 13 incomes, ones checked apply to that client.

For the point on the Null, could you please show me how that is coded?
Finally, I could try and set SELECT back into my VBA but keep getting that error that the DB cannot reference the object - here refers to that same Select statement. Could it be the hld22CGInRangeSelected table? Does not make sense.

All,

Almost there as I have the basic string down that will work, but need some help now with string manipulation. Following is the query I found that works:

strSQL = "SELECT * INTO hld22CGInRangeSelected6_bkup FROM hld22CGInRangeSelected6 WHERE (" & strIncomeSource & ")"
DoCmd.RunSQL (strSQL)

In this instance I had the strIncomeSource hard coded as such:

strIncomeSource = "([IncomeSS?]=True OR [IncomeWages?]=True OR [IncomeTANFCalWorks?]=True)"

So now I need some help to get my current string into the above format or just do the strIncomeSource over. Currently my logic follows to develop the initial strIncomeSource:
Private Sub IncomeSource_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strSQL As String
strIncomeSource = vbNullString

For Each varItem In Me!IncomeSource.ItemsSelected
strIncomeSource = strIncomeSource & "," & Chr(39) & Me!IncomeSource.ItemData(varItem) & Chr(39)
Next varItem
If Len(strIncomeSource) = 0 Then
strIncomeSource = ""
Exit Sub
End If

strIncomeSource = Right(strIncomeSource, Len(strIncomeSource) - 1)
End Sub

Currently from list box the strIncomeSource (say if Social Security, wages, and TANFCalWorks income was requested) ends up like the following:

"'SS','Wages','TANFCalWorks'"

So essentially need a string algorithm to look for each instance, and if one found append "[Income" before each instance, append "?]=True" after each instance, and then have all instances (or call them substrings) concatenated back into the form showed earlier, or for this example:

"([IncomeSS?]=True OR [IncomeWages?]=True OR [IncomeTANFCalWorks?]=True)"

This is just a little beyond my string knowledge. But if someone can just show me a little code that can work, I can take it from there.

Thanks again.

L. Staley

Well, the listbox has to return field names instead of values, then:


and you trim the trailing 4 characters instead of the leading 1.