Finding Inexact Matches With WHERE

Sep 14, 2006

I have a combo box that is filtering a form. The combo box contains employee's last name. However, the field that it is looking up can contain more than one name (e.g., possibilities include just Smith, Smith & Johnson, Smith & Jones & Johnson, etc.).

Thus, when I click on the combo box, I want all records containing "Smith" to show up. However, I have only been able to bring up exact matches. Here is my code. Thanks in advance.


Dim strSQL As String
Dim strSQLSF As String

Status_CB = Null

strSQL = "SELECT DISTINCT Pipeline.Status FROM Pipeline"
strSQL = strSQL & " WHERE Pipeline.[Lead_Person(s)] = '" & S_MD_CB & "'"
strSQL = strSQL & " ORDER BY Pipeline.Status;"

Status_CB.RowSource = strSQL

strSQLSF = "SELECT * FROM Pipeline "
strSQLSF = strSQLSF & "WHERE Pipeline.[Lead_Person(s)] = '" & S_MD_CB & "'"

Me!Data_Entry_SF.LinkChildFields = "[Lead_Person(s)]"
Me!Data_Entry_SF.LinkMasterFields = "[Lead_Person(s)]"
Me.RecordSource = strSQLSF
Me.Requery

View Replies


ADVERTISEMENT

Inexact Match

Jul 26, 2006

Hi,

I have two tables of software names.
A count is needed of those names that are in both tables, as follows :

Table1.software name Count1 Table2.software name Count2

matching on software name.


The problem is that names match only partially, for examle:

Table 1 :

Acrobat Writer 6.0 std

Table 2:

Adobe Acrobat standard edition 6.0

This is the same software, but how do I make Access recognize that?
can't enter every parameter manually using wildcards, because
1) tables are too large to ask for each name separately
2) Sofware names matches on different parts, not only a first or second word.

Does anyone know how to do that ?

thanks

View 2 Replies View Related

Query For Inexact Match

Sep 19, 2005

I have converted an Old FileMaker Pro DB to Access.
There is a record for each of the 22,000 sheets of engineering project documents issued over a period of years.
One of the main fields we query by is the "Description" field.
If I have the following Criteria:
[Enter Document Description]
Then save and exucute the query Access seems to look for an exact match to the value I provide.
I need a query to return ANY record with the criteria value in it.
For example If I entered "Fire" I would like to return anything with "Fire" in the description:
B Building FIRE alarm Install
Building C FIRE sprinkler Demolition
A Building FIRE Exit Signage
Site FIRE Protection Main
etc.
How do I phrase a query to return records that contain the value I provide within the text string in the "Description" field?

I realize this is a dumb, rookie qiestion. I tried Access Help and
I tried a "search" in this Forum, I probably didn't do a good job describing my problem.

View 4 Replies View Related

Word/Sentence Searching With % Matches

Apr 12, 2007

What I am trying to do is split a sentence into words LikeJocelyne Labylle And Jacob DesvarieuxThis would produce 4 words as the and,&,/ Ect are removedSo I end up with:-JocelyneLabylleJacobDesvarieuxIf all 4 words are found in a string it returns all matching records plus gets 100%3 words again returns all matching records plus say 75% and so onI haven't done a lot with it at the min as the first bit is quite simple IE getting the names Etc. but I can't think for the life of me how I can do the search.I Have Tried a query With [Names] Like ""*[String Names]*""" but it seems to only work with each letter???but my main reason for asking here is I don't like reinventing the wheel or at least trying to which will mean hours of Fun but maybe somebody knows of something I could use on the net.Thanks for any help/Advice.mick

View 5 Replies View Related

Criteria Matches 1st Part Of Another Field

Jun 22, 2005

undefinedHi, apologies if this has been answered before but can't find anything relevant!

Trying to match one field to the first 5 digits in another i.e.

Field 1 = 55667

Field 2 = 5566785431 so would want to match the 1st five digits in field 2...

Any ideas?

Thanks

View 3 Replies View Related

Problems With Duplicate Matches In My Queries

Aug 18, 2005

I'm comparing two tables in a query, the tables are information that was imported from other sources. When I compare the tables, I'm trying to get a one to one result (a reconciliation of sorts). The problem is that there are duplicate line items in one of the tables that may or may not be valid, but all are considered matched. How can I create my query to match to only one line item and leave the others unmatched for analysis? I've tried everything but VBA (of which I have little experience in). Any suggestions?

View 2 Replies View Related

SUBFORM Show Approximate Matches

Mar 13, 2006

I have a form that shows a customer number and a dollar amount. In a subform, I want to show all the records with that customer number that are "close" to the dollar amount. For this purpose close can be INT(amt1) = INT(amt2). But I can't figure out how to put that in the parent/child relationship on the sub-form.

Any help?

Thanks,
David

View 2 Replies View Related

Queries :: Find Matches Between 2 Tables

Feb 17, 2015

I am trying to find company matches between 2 tables. The issue I have is that the spellings on table A differs from table B.Some of the differences are minimal like "St. Annes" and "St Annes". And some really big "St. Annes" and "Annes, ST London".

View 1 Replies View Related

Queries :: Not Return Uneven Matches?

Nov 6, 2013

I have created a table which holds transactions which can be both positive and negative. To this table i have added an extra column that shows the Absolute values which end results is as follows:

YYYY MM Currency Amount ABS
2013 10 EUR 50.000 50.000
2013 10 EUR -50.000 50.000
2013 10 EUR 49.995 49.995
2013 10 EUR -50.000 50.000

I need to create a table that shows those positive and negative transactions that can be netted of. I have been able to create a query with the following SQL code:

Code:

SELECT tbl_All_Absolute.[Posting Account], tbl_All_Absolute.Jnl, tbl_All_Absolute.CCYY,
tbl_All_Absolute.PP, tbl_All_Absolute.[Batch Num], tbl_All_Absolute.[Trans Num], tbl_All_Absolute.[Detail Reference],
tbl_All_Absolute.Amount, tbl_All_Absolute.RFPNum, tbl_All_Absolute.[Transaction Ref 1], tbl_All_Absolute.[Transaction Ref 2],
tbl_All_Absolute.[Transaction Ref 3], tbl_All_Absolute.[Transaction Ref 4], tbl_All_Absolute.Expr1
FROM tbl_All_Absolute
WHERE (((tbl_All_Absolute.Expr1) In (SELECT [Expr1] FROM [tbl_All_Absolute] As Tmp GROUP BY [Expr1] HAVING Count(*)>1 )));

Unfortunately (but obviously) the code will list 3x the 50,000 transactions and leaves out the 49.995.

My knowledge in Access is not that of an expert and i am wondering if there is a way for Access to only include the first 2 transactions (the +50.000 and the -50.000)?

View 12 Replies View Related

Search Combo Box - Cannot Browse Through All Matches

Nov 20, 2011

I have a database that keeps track of customers by last name, first name and various other information. I have the combo box setup so that I can type in a last name and it brings up my results. The problem is that it only brings up the first match and doesn't let me browse through all the matches. The last name and first name are seperate fields so if I look for a last name that more than one customer has like smith it only takes me to the first match. Is there any way to make it filter all the names that aren't matches out of the combo box as the values are typed in?

View 3 Replies View Related

If No Matches Reopen Form After Msgbox

Jul 18, 2012

I currently have an unbound form I am using as a switchboard. When a button is pressed it loads a form asking for the user to enter their password (the swtichboard is then closed). If the correct password is entered the form opens to their details, when that form is closed, the swtichboard re-opens. I want an action that if the correct password is not used, then a msgbox lets them know and then the Switchboard they were at re-opens. So far I have the following code:

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
MsgBox "Incorrect Password!"
Cancel = True
End If
End Sub

This works well, but I am unsure how to write that after the msgbox I want the Switchboard to open. I tried to add "DoCmd.OpenForm frmSwitchMain" in various places, but I am unsure on the proper procedure to do this.

View 2 Replies View Related

Magic Query Needed To Isolate Non-matches

Nov 5, 2007

I have a table from an excel sheet that comes in the format above with up to 5000 rows in record groups from 1 to 10 rows in each. The repeating data is how it comes from our all singing and dancing case recording software and I can’t change that fact.

Take a group of records with the same TradingName such as “The Herriots” group, There should be one row in the group where FdInspAction = LastInspDate (in this case 06/01/2007) . In this group there is no match so we need to identify the group. The “Bloggs” group is fine (see Row 8) so can be ignored.

My feeling is it can’t be done other than by visual checking. Can anyone think of a way around this?


IDTradingNameFdInspAction LastInspDate
1The Heriotts 03/02/2001 06/01/2007
2The Heriotts 10/02/2002 06/01/2007
3The Heriotts 16/02/2003 06/01/2007
4The Heriotts 25/01/2005 06/01/2007
5The Heriotts 04/01/2006 06/01/2007
7Bloggs Ltd 02/01/2005 25/01/2005
8Bloggs Ltd 25/01/2005 25/01/2005

View 8 Replies View Related

Wildcard Searching / Partial String Matches

Apr 8, 2008

Hi there,

I've successfully created, for the first time, a database with many attributes that is searchable by most of those attributes as well. I've finally gotten the swing of how tables, forms, queries, and macros link up and am understanding how MSA works.

I can't seem to find any information on here that tells me how to do wildcard searches. For my particular application, people enter items into the database in a nonstandard fashion, i.e. "oring," "o-ring," and "o ring." I need to enable the capability to search "ring" so my search functions are fully maximized.

Can anyone help?


Thanks!

View 2 Replies View Related

Query: Display All 3 Matches For A Field In One Line

Feb 19, 2006

Hi all, I'm new to this forum. I'm having a problem with a query I'm trying to create.

I have two tables in the database: tblCustomers and tblInstallations. These two tables are linked by a field 'CustomerID'.
CustomerID is the Primary Key in tblCustomers.
In tblInstallations, the primary key is InstID and for each CustomerID in tblInstallations there are either 1 or 3 InstID records.

Now what I want to do is create a query with fields: Customer; CustomerID; InstID1; InstID2; InstID3.
InstID1 will contain the first InstID for the CustomerID, and InstID2 & InstID3 will contain the second and third, if applicable.

I see in Query Design View in the 'Totals' line there is a option to display the 'First' and 'Last' match for a field. So is there any way i can use this method and also display the second record? If not, is there any other way to make this query work?

I hope somebody can help!!

Kerry

View 1 Replies View Related

Forms :: Selecting Fields In A Combobox Where ID Matches

Nov 24, 2013

In my Patients table i have a field called PatientID (AUTONUMBER) as my primary key I hve another problem. Is this possible as i hve been trying for two days now. I hve tried a lot of ways but i cant get it to work right. I have a table called dependents. ID , PatientID,,Dependents,DOB,Age id being the PK AND PatientID as a number I have a table called vitals. with ID,PatientID,PatientName( which is a dropdown of Dependents), hpp,sats etc. (This is a subform in datasheet style in another form)

In my table vitals i have the follwing for PatientName . comboxbox.

SELECT Dependents.Dependents, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age FROM Dependents;

So it selects fine in a dropdown like John 48, smith 36.

The problem i have is that it selcts all the records irrespective of PatientID.

View 14 Replies View Related

Update Data From One Table To Another Only If Field Matches

Aug 28, 2014

I have to two tables and I was add data to second table only if the field matches. Lets I have two fields site and recid on both and I want to update site on the second table only if recid from the first table matches the recid from the second table.

View 1 Replies View Related

Find Records Where 1 Field Matches But Other Fields Vary

Feb 14, 2007

I have a table with records for products from each sales region (4 regions, N, NE, S, W). The table has the following fields: Region, Product Name, Product Description, Category, Business Line, and Supplier.

While the Product name is consistent across the regions the description, category, bus.line and supplier may have slightly different entries in each region. I need to build a query that when any of the Product Description, Category, Business Line, and Supplier fields do not match across divisions for each Product Name will display all the records for that Product Name. Any ideas?

View 2 Replies View Related

Queries :: Match Postcodes Ad Return Part That Matches?

Sep 7, 2014

I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.

I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.

So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.

View 5 Replies View Related

Forms :: Do Not Open Form If Query Finds No Matches

Mar 30, 2013

A charity has a client file (over 700 clients) that will have records that need editing. The user is presented with a form(1) holding a text box to enter the parameter (Client Surname) for a query. They can use starting letters and wildcards to narrow a search e.g. Jo*The click a command button that opens another form(2) based on a query that uses the parameters from the first form.

This Works just as planed if matches are found. If not, it displays a blank/new record. I want to either 1) stop the blank form being displayed 2) tell the user that their query was unsuccessful. I Do not want them to use this facility to CREATE a NEW Client record.

The parameter query is working fine and form(2) shows the matching records, it's reacting to a situation where no records are found, that is my problem.

View 2 Replies View Related

General :: Compare 2 Tables Where One Record Matches And Another Doesn't

Jun 26, 2013

I have table A with a list of Work Instruction references (WIREF) and a Issue number (ISSNO).I have Table B wth employees and they too have a Work Instruction (empWIREF) and Issue number (empISSNO).I want to check if the Work instruction Issue has changed since the employee was trained

check:
WIREF = empWIREF and ISSNO <> empISSNO for all records in table B

View 2 Replies View Related

Forms :: Apply Filter To Return Records That Matches The Text Exactly

Aug 2, 2015

I am using an apply filter in a form to find records in a split form.

The macro which I am using is:

[SLIDE] Like "*" & [Forms]![SALES]![ENTERREFDATA] & "*"

However I the filter to return only records which match the text exactly.

View 2 Replies View Related

Forms :: Find Record In Table That Matches Textbox Input

Jan 5, 2014

I'm trying to build a search form.- Access 2007

I have a table (Table1), contains a 'part' field.

I wan to build a form with a blank text box (txtbx1) where users can enter a part description, this will search records in Table1>part and return a list of parts that match the textbox input.

Here's what I've done -

txtbx1 - property sheet - control source - part
event - on enter - ...

I've built an expression -

[Table1]![part] = [txtbx1]

hoping that this would match records 1n Table1 to txtbx1

View 10 Replies View Related

Reports :: Inventory Database - Printing A Report That Matches Real Layout

Jul 20, 2015

I want to track inventory. All our items are tubes that are stored in boxes in a 10x10 grid. So I have a single access table with columns for tube ID, box ID, and position in the box (numbered 1-100). That works fine in terms of 'where can I find tube x'. However it's also useful to be able to look at the physical box & check that there are tubes where should be tubes, and empty spaces where there should be empty spaces. So for each box in the database, I want to print out a 10x10 grid, with the appropriate tube ID's in the appropriate place. Then I can easily check the layout in the database against the layout in the actual box.

View 14 Replies View Related

Modules & VBA :: Open Specific Powerpoint File That Matches A Selection On Access Form

May 27, 2015

We have an access database which has a form that has a list box that details particular records. See attach "list box".

What I would like to do is double click on the record and this would look in a powerpoint folder (see attach "path") and open the presentation if there is a match to the value in Listbox Column 5 i.e. Tool Number.

I can get it to open the presentation if I include the tool number in the path but I cannot seem to be able to get it to compare the values and only open if there is a match.

View 4 Replies View Related

Modules & VBA :: Expanding Treeview Automatically When Node Matches A String Variable From A Table

Jul 14, 2014

I'd like to expand me tree view automatically when a node matches a string variable from a table. In other words, when a node in a tree matches the given name (variable), the tree will expand all the way down to that name. I guess I need to use "For each node" and when the node and variable match, the tree structure will show up.

I have come up with something like this:

Code:
For Each nd In Me.tree.Nodes
If nd = level1 Then
nd.Child.EnsureVisible
Exit For
End If
Next nd
Me.tree.SetFocus

But this only shows the first level of my tree ( btw. I have a 3 level tree). I get lost inside the FOR when I want to make use of another two variables - level2 and level 3

View 1 Replies View Related

DLookup - Check If ID Matches Textbox It Works / However If Form Is Null It Doesn't Work

Mar 26, 2015

basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"

Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.

room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.

here is the dlookup;

If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then
MsgBox "This number already exists."
Else

p.s it unbound form

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved