Use Dynamically Created String Variable for SelectCommand with AccessDataSource?

I have a search screen (see attached image) that allows users to input search criteria in one or more fields. I then build the query string (sqlGetRecords) by testing each field for an entry by the user. How can I sue the dynamcially generated query string (sqlGetRecords) to retieve all records matching the user's criteria?
Dim bolFirst, sqlGetRecords As String
 
    sqlGetRecords = "Select * from ECCC_dbf where "
    bolFirst = "t"
 
      If Len(AccountNum.Text) > 0 Then
        bolFirst = "f" ' Indicate that a search field has been specified
        sqlGetRecords = sqlGetRecords & "AccountNum" & " like "
        sqlGetRecords = sqlGetRecords & "'%" & AccountNum.Text & "%'"
      End If 'len(AccountNum.Text) > 0
 
      If Len(Type.Text) > 0 Then
        If bolFirst = "f" Then ' If a Search Field previously specified, add "AND" to Query
          sqlGetRecords = sqlGetRecords & " AND "
        Else ' If Search Field not previously, set bolFirst to "f" and add "LIKE"
          bolFirst = "f"
          sqlGetRecords = sqlGetRecords & "Type" & " LIKE "
        End If
        sqlGetRecords = sqlGetRecords & "'%" & Type.Text & "%'"
      End If 'len(Type.Text) > 0
 
      If Len(OnSite.Text) > 0 Then
        If bolFirst = "f" Then ' If a Search Field previously specified, add "AND" to Query
          sqlGetRecords = sqlGetRecords & " AND "
        Else ' If Search Field not previously, set bolFirst to "f" and add "LIKE"
          bolFirst = "f"
          sqlGetRecords = sqlGetRecords & "OnSite" & " LIKE "
        End If
        sqlGetRecords = sqlGetRecords & "'%" & OnSite.Text & "%'"
      End If 'len(OnSite.Text) > 0
 
      If Len(Referrals.Text) > 0 Then
        If bolFirst = "f" Then ' If a Search Field previously specified, add "AND" to Query
          sqlGetRecords = sqlGetRecords & " AND "
        Else ' If Search Field not previously, set bolFirst to "f" and add "LIKE"
          bolFirst = "f"
          sqlGetRecords = sqlGetRecords & "Referrals" & " LIKE "
        End If
        sqlGetRecords = sqlGetRecords & "'%" & Referrals.Text & "%'"
      End If 'len(Referrals.Text) > 0
Session("sqlGetRecords") = sqlGetRecords

Open in new window

Search-Screen.jpg
LVL 3
JimWarnerInstructorAsked:
Who is Participating?
 
David H.H.LeeCommented:
Hi JimWarner,
>>..How can I sue the dynamcially generated query string (sqlGetRecords) to retieve all records matching the user's criteria?
- You can reload AccessDataSource's selectcommand during run-time event such as Page_Load event. Make sure you have the stored criteria before bind into its selectcommand property.
That should did the trick.
eg:

Sub Page_Load
  Me. AccessDataSource1.SelectCommand = Session("sqlGetRecords")
end Sub
0
 
JimWarnerInstructorAuthor Commented:
xcom,

Bingo.  That was it. Thanks. I can now put away the heartburn meds!

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.