Solved

Access 2010

Posted on 2013-05-31
13
219 Views
Last Modified: 2015-01-20
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?
0
Comment
Question by:optionsbear
[X]
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
  • 6
  • 5
13 Comments
 
LVL 85
ID: 39210825
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
 

Author Comment

by:optionsbear
ID: 39210927
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39211035
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:optionsbear
ID: 39211298
Do you do this stuff? What would it cost?
0
 
LVL 85
ID: 39211492
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
 

Author Comment

by:optionsbear
ID: 39211699
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39211764
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
 

Author Comment

by:optionsbear
ID: 39212040
can you send me example?
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39212729
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
 

Author Comment

by:optionsbear
ID: 39212897
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39214267
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

695 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