Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Help streamlining some logic

Posted on 2011-02-11
10
360 Views
Last Modified: 2012-08-14
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. :)
0
Comment
Question by:epuglise
  • 5
  • 4
10 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 34875767
Why don't you do something like

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
0
 
LVL 11

Accepted Solution

by:
Runrigger earned 500 total points
ID: 34875805

Private Sub Test()
Dim sConditions() As String, sCriteria As String

    'Set the intial size of the array to 1
    ReDim Preserve sConditions(0)
   
    If Me.chckbox1 Then sConditions(UBound(sConditions)) = "Field like 'mammal'"
   
    If Me.chkcbox2 Then
        ReDim Preserve sConditions(UBound(sConditions) + 1)
        sConditions(UBound(sConditions)) = "Field like 'reptile'"
    End If
   
    If Me.chkcbox3 Then
        ReDim Preserve sConditions(UBound(sConditions) + 1)
        sConditions(UBound(sConditions)) = "Field like 'crustacean'"
    End If
   
    If Me.chkcbox4 Then
        ReDim Preserve sConditions(UBound(sConditions) + 1)
        sConditions(UBound(sConditions)) = "Field like 'fish'"
    End If

    sCriteria = Join(sConditions, " OR ")

End Sub
0
 

Author Comment

by:epuglise
ID: 34875857
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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:epuglise
ID: 34875897
That is a great piece of code!  Thanks!
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34875995
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
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34875997
let me know.
Dave
0
 

Author Comment

by:epuglise
ID: 34920931
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.
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34921345
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
0
 

Author Comment

by:epuglise
ID: 34930049
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!  
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34930155
Glad that I was able to help.

Good luck
Dave
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question