Dynamic If statement

H

here4real

Registered User.
Local time
Today, 07:32
Joined
May 1, 2013
Messages
87
What I am looking to do is to walk through a DB that has a field on each record as to what conditions to check for. If the conditions are true, keep the record. If false, delete the record. The contents of the field that has the conditions do change from record to record. I want to build an If statement that tests for the condition that is contained in the field on the record.

Is there a way to do this?
 
Could you give a couple of examples of how your data is stored and how they will be checked for the condition?
 
I have a field called Criteria. On each record it the field contains something like CurrentAge >= 55. I want to plug the contents of the field into the If statement.
 
Okay that did not make much sense.. As I needed more than one example to identify how different or varied each criteria is.. And how complex the criteria would get.. Without that I am afraid I cannot be of much help.. Good luck..:rolleyes:
 
Sorry... Here are the first few records and the values on that field... You can ignore the ones that say All and If Diabetes Screen... You can see I tried putting in the recordset reference for the VBA...

rs.Fields("Patient_Sex") = "F" and rs.Fields("CurrentAge") Between 21 and 64 If Diabetes Screen If Diabetes Screen rs.Fields("CurrentAge") >= 50 rs.Fields("CurrentAge") >= 55 rs.Fields("Patient_Sex") = "F" and rs.Fields("CurrentAge") Between 21 and 64 All All rs.Fields("CurrentAge") >= 55 If Diabetes Screen If Diabetes Screen rs.Fields("CurrentAge") >= 50 If Diabetes Screen If Diabetes Screen rs.Fields("CurrentAge") >= 55 All rs.Fields("Patient_Sex") = "F" and rs.Fields("CurrentAge") Between 21 and 64 All If Diabetes Screen If Diabetes Screen rs.Fields("CurrentAge") >= 55 All rs.Fields("Patient_Sex") = "F" and rs.Fields("CurrentAge") Between 21 and 64 All rs.Fields("CurrentAge") >= 50 All rs.Fields("Patient_Sex") = "F" and rs.Fields("CurrentAge") Between 21 and 64 rs.Fields("CurrentAge") >= 55 If Diabetes Screen If Diabetes Screen rs.Fields("CurrentAge") >= 50 All rs.Fields("CurrentAge") >= 50 If Diabetes Screen If Diabetes Screen rs.Fields("CurrentAge") >= 55 All rs.Fields("Patient_Sex") = "F" and rs.Fields("CurrentAge") Between 21 and 64 All rs.Fields("CurrentAge") >= 50 All All rs.Fields("Patient_Sex") = "F" and rs.Fields("CurrentAge") Between 21 and 64 rs.Fields("CurrentAgeY") Between 12 And 21
 
Arghhh... I always forget the Code demarking...

Code: 
rs.Fields("Patient_Sex")   = "F" and rs.Fields("CurrentAge") Between 21 and 64     If Diabetes Screen      
If Diabetes Screen       
rs.Fields("CurrentAge")  >=    50       
rs.Fields("CurrentAge")  >=    55       
rs.Fields("Patient_Sex") = "F" and   rs.Fields("CurrentAge") Between 21 and 64       All       
All       
rs.Fields("CurrentAge")  >=    55       
If Diabetes Screen       
If Diabetes Screen       
rs.Fields("CurrentAge")  >=    50       
If Diabetes Screen       
If Diabetes Screen       
rs.Fields("CurrentAge")  >=    55       
All       
rs.Fields("Patient_Sex") = "F" and   rs.Fields("CurrentAge") Between 21 and 64       All       
If Diabetes Screen       
If Diabetes Screen       
rs.Fields("CurrentAge")  >=    55       
All       
rs.Fields("Patient_Sex") = "F" and   rs.Fields("CurrentAge") Between 21 and 64       All       
rs.Fields("CurrentAge")  >=    50       
All       
rs.Fields("Patient_Sex") = "F" and   rs.Fields("CurrentAge") Between 21 and 64       rs.Fields("CurrentAge")  >=    55       
If Diabetes Screen       
If Diabetes Screen       
rs.Fields("CurrentAge")  >=    50       
All       
rs.Fields("CurrentAge")  >=    50       
If Diabetes Screen       
If Diabetes Screen       
rs.Fields("CurrentAge")  >=    55       
All       
rs.Fields("Patient_Sex") = "F" and   rs.Fields("CurrentAge") Between 21 and 64       All       
rs.Fields("CurrentAge")  >=    50       
All       
All       
rs.Fields("Patient_Sex") = "F" and   rs.Fields("CurrentAge") Between 21 and 64       rs.Fields("CurrentAgeY") Between 12 And 21
 
May I ask why you would want to delete records in the first place.
This defeating the one purposes of a database, storing history data.

Dale
 
This is an intermediate table to determine which records require additional processing... A previous procedure creates this table for one purpose... Then, instead of creating another table, it should be easier to just delete the unwanted records leaving the records that require yet further processing... It is not the repository of the historical information...
 
This is what I have tried unsuccessfully (note it is not doing the delete, etc. - just testing the program flow):

Code: 
    rs.MoveFirst
    
    Do While Not rs.EOF
        If rs.Fields("EligCrit") <> "All" And rs.Fields("EligCrit") <> "If Diabetes Screen" And _
                rs.Fields("EligCrit") <> "No eligibility" Then
            If rs.Fields("EligCrit") <> True Then
                MsgBox "Not true  " & rs.Fields("EligCrit") & "  " & rs.Fields("QARR Measure") & "  " & rs.Fields("CurrentAge") & "  " & rs.Fields("Patient_Sex")
            Else
                MsgBox "True  " & rs.Fields("EligCrit") & "  " & rs.Fields("QARR Measure") & "  " & rs.Fields("CurrentAge") & "  " & rs.Fields("Patient_Sex")
            End If
        Else
            MsgBox rs.Fields("EligCrit") & "  " & rs.Fields("QARR Measure") & "  " & rs.Fields("CurrentAge") & "  " & rs.Fields("Patient_Sex")
        End If
        rs.MoveNext
        Loop
 
I have tried this as well (using Eval):

Code: 
    Do While Not rs.EOF
        If rs.Fields("EligCrit") <> "All" And rs.Fields("EligCrit") <> "If Diabetes Screen" And _
                rs.Fields("EligCrit") <> "No eligibility" Then
            If Eval(rs.Fields("EligCrit")) <> True Then
                MsgBox "Not true  " & rs.Fields("EligCrit") & "  " & rs.Fields("QARR Measure") & "  " & rs.Fields("CurrentAge") & "  " & rs.Fields("Patient_Sex")
            Else
                MsgBox "True  " & rs.Fields("EligCrit") & "  " & rs.Fields("QARR Measure") & "  " & rs.Fields("CurrentAge") & "  " & rs.Fields("Patient_Sex")
            End If
        Else
            MsgBox rs.Fields("EligCrit") & "  " & rs.Fields("QARR Measure") & "  " & rs.Fields("CurrentAge") & "  " & rs.Fields("Patient_Sex")
        End If
        rs.MoveNext
        Loop

I get an error message that it doesn't know what rs is...
 
You may have seen this but I thought I would pass it along to you.
It appears to be what you are trying to do.

Code: 
  [FONT=&quot]Sub DeleteDuplicateShippers()[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Dim dbsNorthwind As DAO.Database[/FONT]
  [FONT=&quot]Dim rstShippers As DAO.Recordset[/FONT]
  [FONT=&quot]Dim strSQL As String[/FONT]
  [FONT=&quot]Dim strName As String[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]On Error GoTo ErrorHandler[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]   Set dbsNorthwind = CurrentDb[/FONT]
  [FONT=&quot]   strSQL = "SELECT * FROM Shippers ORDER BY CompanyName, ShipperID"[/FONT]
  [FONT=&quot]   Set rstShippers = dbsNorthwind.OpenRecordset(strSQL, dbOpenDynaset)[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]   'If no records in Shippers table, exit.[/FONT]
  [FONT=&quot]   If rstShippers.EOF Then Exit Sub[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]   strName = rstShippers![CompanyName][/FONT]
  [FONT=&quot]   rstShippers.MoveNext[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]   Do Until rstShippers.EOF[/FONT]
  [FONT=&quot]      If rstShippers![CompanyName] = strName Then[/FONT]
  [FONT=&quot]         rstShippers.Delete[/FONT]
  [FONT=&quot]      Else[/FONT]
  [FONT=&quot]         strName = rstShippers![CompanyName][/FONT]
  [FONT=&quot]      End If[/FONT]
  [FONT=&quot]      rstShippers.MoveNext[/FONT]
  [FONT=&quot]   Loop[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Exit Sub[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]ErrorHandler:[/FONT]
  [FONT=&quot]   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description[/FONT]
  [FONT=&quot]End Function[/FONT][FONT=&quot][/FONT]

Dale
 
Thanks but not quite... In the example you posted is the line:

If rstShippers![CompanyName] = strName Then

It is this line that I want to make dynamic, i.e. I want to make the rstShippers![CompanyName]=strName a variable...
 
field(Number)
Number = the index number of the field within the record.
Should remain the same as long as RST is open.

Whatever EligCrit is in one record it should remain in all records.


Dale
 
[FONT=&quot]Do Until rs.EOF[/FONT] [FONT=&quot] If rs![IndexNumber] = strName Then[/FONT] [FONT=&quot] rs.Delete[/FONT]
 
here4real said:
I get an error message that it doesn't know what rs is...
Click to expand...

This is most likely because you forgot to defrine the recordsert, i.e.
Code: 
set rs = CurrentDB.OpenRecordset(.....

Best,
Jiri
 
Thanks... The recordset is defined... When I cut the snippet of code, I didn't include the definitions...
 
[FONT=&quot]Do Until rs.EOF[/FONT] [FONT=&quot] If rs![IndexNumber] = strName Then[/FONT] [FONT=&quot] rs.Delete


How does this help??? The field contains the criteria for other fields on the record... I think another way of describing my issue is something similar that I have done in Excel using the Indirect function - it calculates the criteria on the fly and then substitutes it into the formula as if it was actually entered...
[/FONT]
 
how many different types of criteria are there, perhaps you could use a Select Case statement on the criteria field and then within each case, incorporate some simple If statement conditions when to delete

David
 
Interesting idea... A case statement won't work because the criteria I need to test for is still stored in the contents of one of the fields on the record...

I'm starting to think that the only way I can get this to work is to parse the criteria stored in the variable and do something that way... I can search for rs. to find the beginning of each condition... Combine that with searching for Or or And and I should be able to identify each field that needs to be tested and what it needs be tested for... But I still have the question of how to build the If...
 
but that is exactly what I'm suggesting, the case statement would be based on the value in the field you're referring to, i.e.

Select case rs.criteriaField
Case ">50"
If rs.otherField > 50 Then: rs.Delete
Case "X" ....
Case "Y" ....
End Select

David
 
You must log in or register to reply here.

Similar threads

S
Solved Multiple If Statements Problem
2
Replies
37
Views
1,066
silentwolf
S
S
Solved Second SQl delete query raises runtime error 424
Replies
8
Views
362
Pat Hartman
P
I
Dynamic/reusable forms
4 5 6
Replies
111
Views
5,251
Imb
I
C
Before Update event on a continuous form doesn't work
Replies
4
Views
274
The_Doc_Man
The_Doc_Man
B
Test if a compound. (two or more joined fields) exists
Replies
7
Views
517
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom