Access 2010

I'm new to Access, and have a bunch of data that I want to use muliple field to match up and find the right match.

See attached the field I want to search using top two rows

I think I need a seach button but unsure how to write the vba code for this?
optionsbearAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
So I assume that in the form in your "form view" attachment, you want to enter values in one or more of those boxes, and you would then see what data in the table matches your entries?

You can do that fairly easily, if you're looking for an exact match.

If you're looking for a "closest match", then that's an entirely different matter. You can develop a "grading" system, where you assign various weighted scores to matches on certain columns, and then present the user with the results in order of "weight", but that is a difficult and complex matter (and not well suited for someone who is new to this).

To get an exact match, you'd build a Search feature something like this:

Function Search
  Dim sWhere As String

  If Me.ValveType <> "" Then
    sWhere = "ValveType='" & Me.ValveType & "'"
  End If

  If Me.Size <> "" Then
    Is Len(sWhere) >0 Then sWhere = sWhere & " AND "
    sWhere = sWhere & " Size='" & Me.Size & "'"
  End If

  etc etc - you do the same for EVERY column where you need to match

  Dim rst As DAO.Recordset
  Set rst = Currentdb.Openrecordset("SELECT * FROM MyTAble WHERE & " sWhere)

End Function

You could then show the results in a Subform, or Listbox. That would be an EXACT match system - in other words, if the user enters a value in any of your boxes, the code would attempt to exactly match the values entered.

If you need something along the lines of a "closest match", you could try using OR instead of AND:

  If Me.Size <> "" Then
    Is Len(sWhere) >0 Then sWhere = sWhere & " OR "
    sWhere = sWhere & " Size='" & Me.Size & "'"
  End If

This would show you any record where ANY value typed in by the user matches the associated column.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You didn't attach anything.

Also, what is "the right match"? That can mean many different things, so please be sure to define that thoroughly.

Also give details as to exactly what you want to match in your "bunch of data". The more details you give us, the more easily we can help.
0
 
optionsbearAuthor Commented:
Sorry I'm new to this, I have a table of data (see data data attachment) I want to use the fields that are vertical in the [Order] column.

What I am trying to do is choose (quickly) the order column questions and have the database shake out the best matched items

basically I have a complex price book and I want to search the options and come up with the correct "favor" of product..

If I can get the information ie adders into a table I want to create a search function to configure my product, my hopes it to have Access do the work instead of flipping though a 6" thick price book choosing adders and typing them in excel each time.

thanks for the help in advance...
5-31-2013-6-26-19-AM.jpg
Data-table.jpg
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
optionsbearAuthor Commented:
Do you do this stuff? What would it cost?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd be glad to help you work through this onsite, but if you'd prefer to hire this out please contact me at the email address in my profile.
0
 
optionsbearAuthor Commented:
I am trying to learn this but finding the vba code is beyond my knowledge, it seems like it should be that difficult to do I would like to know more from an expert, why i signed up on this site? and open to your suggestions? please advise the best course of action
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
What you're asking to do - a "ranked" match of records, based on varying criteria - is generally well beyond the capabilities of a novice. We can help you to work through this, but all in all the onus is on you to do the work, and we'll help you through the tough spots.

A good place to start would be to begin building the Search function I showed you above. Start with just a few of your criteria you want to search, and get those working like you want, and then add more criteria.

I've added some comments to that function below, to help you perhaps understand what's going on:

  '/ We check to see if the user has entered a value in the "ValveType" CONTROL on the form. If they have, then we add the first portion of our WHERE criteria to our sWhere variable
 If Me.ValveType <> "" Then
    sWhere = "ValveType='" & Me.ValveType & "'"
  End If
  '/ Next, we check to see if the user has entered a value in the "Size" CONTROL on the form. If so, we then further check to see if we already have criteria by checking the LENgth of sWhere. If we do, then we add an AND (or OR, depending on how you want your search to proceed), and then add the criteria.
  If Me.Size <> "" Then
    Is Len(sWhere) >0 Then sWhere = sWhere & " AND "
    sWhere = sWhere & " Size='" & Me.Size & "'"
  End If

What we want to end up with is a valid WHERE clause.
0
 
optionsbearAuthor Commented:
can you send me example?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I don't really have any complete examples of this sort of thing (at least none that I could send you without violating NDAs). The examples of the Search function are the basis for those solutions, but (again) they are far more complex. As you stated you're a novice in regard to Access, this will be a difficult project for you to undertake.

I'd suggest you take some time and review some working Access database examples:

http://office.microsoft.com/en-us/access/microsoft-access-for-your-business-database-software-and-applications-FX102473447.aspx
http://access.microsofttemplates.org/

Also, do you have a database that you've started work on? You might consider attaching that file here. If you do, be sure to remove or obfuscate any sensitive data, and also provide instructions on using your database (if you have forms and such).
0
 
optionsbearAuthor Commented:
I have been reading up on this and tried to take a stab at doing it, I am confused a bit the code you sent but im sure that there is a small simple piece that I am missing..

can you look at this?

the top of the form I want to use as my search criteria, the search button would search through my table BAW_Gears_pt2 to find an exact match and display the information on the lower body of the form?
6-1-2013-6-19-48-AM.jpg
6-1-2013-6-21-30-AM.jpg
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
First: The "search" portion of your form (the Header) should not contain Bound fields (i.e. those fields should not have a ControlSource set). In that top section, you should remove the Control Source for each of those controls.

-- When comparing values, you should use "<>", which generally means "anything other than" - so this:

If Me.txtSize <> "" Then

Means "If there is anything other than an empty string in txtSize" ... which means that the user has entered something. Using ONLY the > or < can result in errant data included in your WHERE clause.

-- If you're going to use the LIKE keyword, you generally will want to use the wildcard indicator:

If Me.txtSize <> "" Then
  varWhere = varWhere & " [Size] LIKE '*" & Me.txtSize & "* AND"
End If

In other words, this would find any record where the value you enter in txtSize is included in ANY portion of the Size field. So if I enter "large" in the Size field, this would return values of "Large", "XLarge", "LargeTall" etc.

Note that LIKE operators don't work for Numeric fields, so if Fields like MaxDiffPressure are Numeric fields it's pointless to use LIKE (and can cause issues as well).

-- Also, I don't see the need to declare your varWhere as a Variant. It's a String, and should be declared as such:

Dim varWhere As String

-- Also, your function should return a STRING, not a Variant:

Private Function BuildFilter() As String

-- Finally, in order to actually show the records from your Search, you'd do this:

Me.RecordSource = "SELECT * FROM YourTable WHERE " & varWhere

This "resets" the Recordsource for the form, and should show you the records based on your search.
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.