epuglise
asked on
Help streamlining some logic
I have four checkboxes on a form that, when selected correspond to a search term for a single field.
My code surely can be condensed but I'm not as fluent as some of you all!
Please help me make this prettier code:
dim sFieldName as string 'this will become a piece of sql that searches on FieldName
If me.chkBox1 then sFieldName = 'Field like 'mammal'"
end if
if sfieldName = "" and me.chckbox2 then
sFieldName = "Field like 'reptile'"
elseif sFieldName <> "" and chkbox2 then
sFieldName = sFieldName & " OR Field like 'reptile'"
end if
if sfieldName = "" and me.chckbox3 then sFieldName = "Field like 'crustacean'"
elseif sFieldName <> "" and chkbox3 then sFieldName = sFieldName & " OR Field like 'crustacean'"
end if
if sfieldName = "" and me.chckbox4 then sFieldName = "Field like 'fish'"
elseif sFieldName <> "" and chkbox4 then sFieldName = sFieldName & " OR Field like 'fish'"
end if
Thanks for the help. :)
My code surely can be condensed but I'm not as fluent as some of you all!
Please help me make this prettier code:
dim sFieldName as string 'this will become a piece of sql that searches on FieldName
If me.chkBox1 then sFieldName = 'Field like 'mammal'"
end if
if sfieldName = "" and me.chckbox2 then
sFieldName = "Field like 'reptile'"
elseif sFieldName <> "" and chkbox2 then
sFieldName = sFieldName & " OR Field like 'reptile'"
end if
if sfieldName = "" and me.chckbox3 then sFieldName = "Field like 'crustacean'"
elseif sFieldName <> "" and chkbox3 then sFieldName = sFieldName & " OR Field like 'crustacean'"
end if
if sfieldName = "" and me.chckbox4 then sFieldName = "Field like 'fish'"
elseif sFieldName <> "" and chkbox4 then sFieldName = sFieldName & " OR Field like 'fish'"
end if
Thanks for the help. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I may start thinking in arrays... a concept I totally understand but whose language I can not yet speak. I like this abstraction a lot. Thanks. I have another similar thorny problem that I'll also be able to use this checking method with!! Thanks.
ASKER
That is a great piece of code! Thanks!
you are very welcome, I am certain that some other experts will be able to streamline further!
try the below, should work regardless of a part of the array being empty
Happy playing.
Regards
Dave
Private Sub Test()
Dim sConditions() As String, sCriteria As String
'Create a blank array
ReDim Preserve sConditions(3)
If Me.chckbox1 Then sConditions(0) = "Field like 'mammal'"
If Me.chkcbox2 Then sConditions(1) = "Field like 'reptile'"
If Me.chkcbox3 Then sConditions(2) = "Field like 'crustacean'"
If Me.chkcbox4 Then sConditions(3) = "Field like 'fish'"
sCriteria = Join(sConditions, " OR ")
End Sub
try the below, should work regardless of a part of the array being empty
Happy playing.
Regards
Dave
Private Sub Test()
Dim sConditions() As String, sCriteria As String
'Create a blank array
ReDim Preserve sConditions(3)
If Me.chckbox1 Then sConditions(0) = "Field like 'mammal'"
If Me.chkcbox2 Then sConditions(1) = "Field like 'reptile'"
If Me.chkcbox3 Then sConditions(2) = "Field like 'crustacean'"
If Me.chkcbox4 Then sConditions(3) = "Field like 'fish'"
sCriteria = Join(sConditions, " OR ")
End Sub
let me know.
Dave
Dave
ASKER
Ok Dave, I hit a snag when I have a single condition that is true. I need to know how many items are in the array right? I'd be happy to open another question if necessary. Title suggestions welcomed.
I didn't think that it would join a single entry array, I am surprised!
Check this condition;
If Ubound(sConditions)=0 then
sCriteria=sConditions(0)
else
sCriteria = Join(sConditions, " OR ")
end if
Check this condition;
If Ubound(sConditions)=0 then
sCriteria=sConditions(0)
else
sCriteria = Join(sConditions, " OR ")
end if
ASKER
So the problem was in a new version of the code you gave me i was dealing with text boxes that might or might not have entries. I think I figured it out, but the problem was that it would occasionally populate the first (0) entry of the array with a null so the first element of the join statement was blank. Weird. I solved it by adding a check to the code (similar to yours) that determined if the (0) entry was empty, and if so, fill it. Thanks for the follow up help. BTW they've asked me to build another tool (actually upgrade someone else's) so I'm sure I'll be back with more questions!
Glad that I was able to help.
Good luck
Dave
Good luck
Dave
field in ('mammal', 'reptile', ....).
This should be easier to implement.
Unless you need to use the "like" option (this is done if the search will have a wildcard in it).
Ss