Interesting challenge doing a Delete in VBA w/boolean field values compared to string

larrystaley

Registered User.
Local time
Today, 10:37
Joined
Sep 19, 2016
Messages
20
I am attempting to do a delete based on a selection from a table for Boolean and then comparing to a string.
However, it needs more work as my report is null indicating all records are getting deleted.
First, I have to select certain income sources on which to report. In this case I want to report ONLY on income sources where the client had TANF or Wages. The string as referred to strIncomeSource is correct and coming in as "'TANF/CalWorks','Wages'". I then want to look at all columns where a client may or may not have a particular form of income. In my delete statement, to make it simpler, I simply include IncomeSS? and IncomeWages? and these are again Boolean fields, represented as Yes / No (client either has that form of income or does not).
My SQL statement is as follows:

strSQL = "DELETE * FROM hld22CGinRangeSelected WHERE (Cstr([IncomeSS?]) OR (Cstr([IncomeWages?]))) NOT IN (" & strIncomeSource & ")"
DoCmd.RunSQL (strSQL)
I believe as a result of the Cstr, the resulting string conversion for SS income or Wages income is only a Yes, or No, or True, False. In any case, I believe those values cannot be compared to their equivalent string sources, as I noted in strIncomeSource. Is there any way in the statement to assign a positive result? So for example, if a client does have wages, via the Cstr, I can assign a value of 'Wages' and then that will match the string value. If they do not I would likely set it to null.
I would appreciate any input, especially if you have had run into anything like this yourself.
Thanks again.
L. Staley
 
assuming everything else is right your sql needs to be

strSQL = "DELETE * FROM hld22CGinRangeSelected WHERE Cstr([IncomeSS?]) NOT IN (" & strIncomeSource & ")" OR Cstr([IncomeWages?]) NOT IN (" & strIncomeSource & ")"
DoCmd.RunSQL (strSQL)
 
I don't think it will work at all. CStr() against a Boolean field isn't going to return anything that can be compared to "'TANF/CalWorks','Wages'". It's going to return "-1", "True" or "Yes".

My first thought is that having a bunch of Boolean fields is a normalization issue.
 
If you wanted to convert a boolean like IncomeSS to either "Wages" or Null you could do that with something like:

Code:
IIf([IncomeSS?], "Wages", Null)

But this would be kind of weird. Could you tell us a little more about what you are trying to do and the data that's involved. Maybe we can suggest a better approach. Posting you database might help too.
 
Oh, and I forgot to mention I moved your thread out of the introductions forum.
 
I am doing modification to an existing database. In the original database it was set up with comboboxes for IncomeSource. Pick one income source, say 'SS' and only get transaction results for clients who had SS income. I have modified to ListBoxes so we can enter multiple sources of income, for example I am interested in all clients with 'SS' income and 'Wages'. So I have created a delete statement, following running of serveral queries, to simply look, in this case, for any client who had 'SS' income or any client with 'Wages' income and include them all on the original report. Would be same with a string for say three sources of income - clients with social security, clients with wages, or clients with private pensions. Report would then include clients with any of these incomes. Challenge is the income fields are all Boolean. So I need a way to 1. make sure the particular income exists, and 2. if it exists, and it is one included in the string, it gets reported.

Hopefully that helps.
L. Staley
 
Hi Paul,

So are you suggesting a different string, built along the Boolean rather than my current text string? Currently the one I build now is: "'AAP','ChildSupport','FosterCare'......."

I actually tried experimenting with some code similar to what you suggested but determined when the query was run, all records were being deleted. Again, there are a large number of string permutations spanning some 13 income types. It appears a delete going beyond warranted income types for that string maybe reason all records get deleted.

Thanks again.
L. Staley
 
I'm suggesting not doing a delete at all, but basing the report on a query that returns all records, and filtering it as described in the link with OpenReport.
 
Hi Paul,

Actually, very similar is what is already being done. A series of layered queries are run prior to the logic we are discussing that first return all the records. Then once this filtering is done, however that is determined, an OpenReport is then generated. Only difference I can surmise here, is doing all the filtering in the OpenReport with a Where Condition now to be used? Just want to be sure. Thanks.
 
This is getting a bit confusing as you are talking about delete records and then some sort of report. What's the report and how does it related to deleting records.

Whether deleting or reporting if you have 13 incomes types and 13 corresponding boolean fields maybe you should consider a different structure with a table of income types and a table that relates clients to types of income. With that structure creating SQL from the listbox selections would be a lot easier.
 
It sounds like you dump a bunch of records into a table, then delete records you don't want out of that table, then base the report on the table. In general, that's a rookie workaround. Not saying it is in your case, just saying it generally is. Normally you'd create a query or queries that drew out the filtered data you want and base the report on them. No temp tables involved.

Not knowing your situation, the simplest way out in my view is leaving the process in place to dump data into the table, then use the suggested method to filter the report rather than delete from that table. You could still delete from the table, but unless I'm missing something it will be more complicated (still creating the string I described, but using a DAO QueryDef to update the SQL of a delete query).

That all said, the "ideal" solution (if feasible) is creating SELECT queries that pull out the desired data without the overhead of emptying/populating/deleting that comes with a temp table.
 
Paul,

There are many layered queries, as was established with the original design, so preference has always been to keep the main process in place, especially as I am only charged with doing upgrades. However, yes, I can employ filtering in the Open Report and not delete records. However, still not clear on the Where Condition to use in filtering. Again, with 13 income sources, there are a very large number of permutations as to the look of the final selection string. Just not finding a Where condition that will effectively evaluate any of a possible strings requesting income sources and then checking on those fields to insure at least one is true - then that record is accepted for reporting.

Thanks again.
L. Staley
 
Was using the listbox to build

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

based on the user having selected those 3 fields (or whatever fields) not workable? In other words, whatever items the user selects are added to the string, much as you (presumably) built "'TANF/CalWorks','Wages'".
 
I tried a test with this nomenclature, and another version with Fields set to 0 (don't exist) and 1 (exist), but appears the logic was not working with no records being selected. I will get back to you later on the specifics as I have to go somewhere at tis time.

Thanks again.
 
No problem. If you're still stuck, attaching a sample db here may help.
 
Hi Paul,

We're you recommending something like the following:
strIncomeSource = "AAP=Yes OR ChildSupport=Yes OR FosterCare=Yes"

The main SQL follows:

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

For some reason, I am getting a syntax error so can't test it.
Maybe I have something off here compared to what you were recommending. Please let me know.

Thanks again.
L. Staley
 
Hi Paul,

Oh, even if those are each set to True, and not Yes, makes no difference, still have a syntax error with the final version of the SQL statement. Syntax error is attached.

Thanks again.

L. Staley
 

Attachments

  • syntax_error.jpg
    syntax_error.jpg
    94.9 KB · Views: 157
Hi Paul,

Okay, added back Cstr and eliminated the syntax. This time got prompted each for AAP, Child Support, and Foster Care. Tried all versions with 0,0,-1, and No,No,Yes, and False, False, True. In every case a null report was produced and not with 6 records as I was anticipating.

Also will have to remove this prompting. We don't want our report users prompted at all.

Thanks again.

L. Staley
 
Can you attach the db here?
 

Users who are viewing this thread

Back
Top Bottom