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
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