Filter datasheet for specific column (1 Viewer)

vent · Aug 17, 2017

Hello everyone,

I have a datasheet in split-form view and a search bar that filters based on whatever the user types in and I'm happy to say works very well. I have attached the SQL of the query this datasheet uses. So as you can see from the screenshot whatever the user types in is based on those columns in the WHERE clause. But to take it a little further, program codes (e.g. 2010) and dates 1/2/2010 can conflict, because for example if i need to find all the records with the program code "2010", i might not get all of them since the query look for records with "2010" in it's date field as well. Is there anyway I can maybe add a second search bar that filters by programs only? The VBA for the search bar is as follows:

Code:

Private Sub cmdReset_Click()
Me.txtSearch = ""
    Me.SrchText = ""
    Me.txtSearch.SetFocus
    DoCmd.Requery
End Sub


Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub


Private Sub SrchText_AfterUpdate()
Me.SrchText.Requery
End Sub

Private Sub txtSearch_Change()
'Create a string (text) variable

    Dim vSearchString As String
    vSearchString = txtSearch.Text
    SrchText.Value = vSearchString
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
        End If
    'Me.SearchResults = Me.SearchResults.ItemData(1)
    
    'Me.SearchResults.SetFocus
    DoCmd.Requery
    Me.txtSearch.SetFocus
    If Not IsNull(Len(Me.txtSearch)) Then
        Me.txtSearch.SelStart = Len(Me.txtSearch)
        End If
End Sub

Any help is much appreciated.

jdraw · Aug 17, 2017

Tell us a little more about the application and what users might be searching for.

Your current sql does/will not take advantage of any indexing, so speed could be slow depending on volumes.

My guess is that you could limit your search to fewer fields, but we need to know more about your database and the users.

vent · Aug 17, 2017

jdraw said:
Tell us a little more about the application and what users might be searching for.

Your current sql does/will not take advantage of any indexing, so speed could be slow depending on volumes.

My guess is that you could limit your search to fewer fields, but we need to know more about your database and the users.

Well basically this database just keeps track of agency and subsidiary (branches of agency) information working with this one educational institute. The information includes: agency name, subsidiary name, educational programs (listed as codes), placement types (community, long term care), address, city, state, contact info (first name, last name, phone number and email), an affiliation agreement expiry date, insurance expiry date, and worker safety insurance claims. Basically at this point, the users need a way to quickly look up agency info. I know that users would prefer to look up by programs too since it's important to know what programs are currently in use by which agencies and make edits, if needed.

They have also requested reports based on the two expiry date fields and the worker safety insurance claims (yes/no field)

jdraw · Aug 17, 2017

Could you show us a jpg of your relationships window? Knowing your database design (tables and relationships) could help in getting a focused response.
Did you develop the database?

vent · Aug 17, 2017

No, I didn't develop. Just building upon it. As a side note, although there aren't any relationships for the two tables. Originally data in both tables were an absolute mess, i imported them into a couple excel files, did some data cleanup, imported them back into the database. So even though the program codes in the agency column are the same as in the programcodes table, the codes in that table don't come from the programcodes table. Instead, on the main form, there is a list box used as a directory to search up what the program name is for a code which comes from the programcodes table.

jdraw · Aug 17, 2017

If you and users are happy, carry on.
Based on your overview description in post 3, I'm sure your database is not designed to support your "business". There are additional entities in your description that have not been identified in a normalized manner in your database.
Here is a link that may help you with database planning and design.

Many of the items are good for reference and review.

Good luck.

vent · Aug 17, 2017

For sure. I presented what I have so far to some of the users and they liked it. But was just wondering if there is any way to in addition look up multiple columns at once, to look search one specific column as well. If not, I appreciate it and will also look at refining the design of this poorly done database. Thank you.

jdraw · Aug 17, 2017

Rob,

I'm just saying that relational databases are based on some underlying concepts and principles. Primary of those is the set up of tables and relationships based on "business facts/rules".

Review a few of the links and tutorials to get some appreciation of the concepts involved. It will make your database-life much more relaxing.

Good luck.

vent · Aug 17, 2017

jdraw said:
Rob,

I'm just saying that relational databases are based on some underlying concepts and principles. Primary of those is the set up of tables and relationships based ob "business facts/rules".

Review a few of the links and tutorials to get some appreciation of the concepts involved. It will make your database-life much more relaxing.

Good luck.

You're right. I'm glad you mentioned this before I invest more work into it, it's good timing to review good design principles, since 2 out of 3 major tables are in there, one more still needs to be included with clean data. I was thinking of continuing without the third but after reviewing this, I think its better to include this third table with clean data and then create the table relationships before moving on. Once I accomplish this, then I'll get back to you. Thank you very much

Filter datasheet for specific column (1 Viewer)

vent

Registered User.

Attachments

jdraw

Super Moderator

vent

Registered User.

jdraw

Super Moderator

vent

Registered User.

Attachments

jdraw

Super Moderator

vent

Registered User.

jdraw

Super Moderator

vent

Registered User.

Similar threads

Users who are viewing this thread