?
Solved

Use Dynamically Created String Variable for SelectCommand with AccessDataSource?

Posted on 2009-04-21
2
Medium Priority
?
594 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:JimWarner
2 Comments
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 2000 total points
ID: 24201460
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
 
LVL 3

Author Closing Comment

by:JimWarner
ID: 31572787
xcom,

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

Jim
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

862 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