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 Replies


ADVERTISEMENT

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

Modules & VBA :: Searching For Partial Primary Key?

Jan 8, 2014

I am trying to search for a partial number in the primary key field. I have based my search off Allen Browne's search form [URL]

I have posted my full code below. I am having trouble with the portion searching the Member_ID field.

My first version:

I would get a Run-time error '3464':

Data type mismatch in criteria expression.

Code:
If Not IsNull(Me.txtMember_ID) Then
strWhere = strWhere & "([Member_ID] = """ & Me.txtMember_ID & """) AND "
End If

My second Version: (I was searching for 54)

I would get a Run-time error '3075'

Syntax error (missing operator) in query expression '([Member_ID] = Like "*54*")'

Code:
If Not IsNull(Me.txtMember_ID) Then
strWhere = strWhere & "([Member_ID] = Like ""*" & Me.txtMember_ID & "*"") AND "
End If

Version 3:

If I use this the search works when the Member_ID matches exactly

Code:
If Not IsNull(Me.txtMember_ID) Then
strWhere = strWhere & "([Member_ID] = " & Me.txtMember_ID & ") AND "
End If

Code:
Private Sub Member_Search_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).

[code]....

View 14 Replies View Related

Delete Partial String

May 15, 2015

I have a column in my databse that has random numbers. There are some numbers in the database that have .0 at the end of them. For example: 44551 .0. I want to create an update query that will delete the .0 from the values.

View 2 Replies View Related

Modules & VBA :: Match Partial Text String And Update Record

Jun 13, 2013

I am provided a spreadsheet that contains multiple rows of similar data; each row/record represents a different stage in the process of financial transactions (requisition, purchase order, & voucher payment). Each financial transaction has these three records, with the amounts in one of three columns (pre-encumbrance, encumbrance, and expense), depending on the process.

What I am really after is the fuller, more detailed description that is apparently only available for the two records I don't want to import into the database (which is tracking only expenses and not the other two stages of the process). There is apparently no way to cross-reference these multiple rows due to the way the original database was designed (and we apparently have no control over this).

After importing the spreadsheet into Access, I would like to match the partial text string (truncated description) to the full description in another record, and update the record with the truncated description to the full description. To make mattes more complicated, I will also have to match values in the "pre-enc" or "enc" field with the "exp" field across these three records to make sure the correct descriptions are being matched because the truncated description will match multiple distinct records with the longer description.

TypeDescrPreEncEncExp
VOUJsmith-Instructor, 12/16/13$0.00$0.00$45.00
POJSmith-Instructor, 12/16/13, Course1, Parking($45.00)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Parking$0.00$45.00$0.00
VOUJsmith-Instructor, 12/16/13$0.00$0.00$221.13
POJSmith-Instructor, 12/16/13, Course1, Lodging($221.13)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Lodging$0.00$221.13$0.00

View 5 Replies View Related

Modules & VBA :: Exclude String From Wildcard

Oct 17, 2013

I have:

Code: Kill "Z:Client_Reports" & [websiteID] & "" & LEFT([Forms]![frm x main]![month name], 3) & "*"

This has been working fine - all the files that meet that criteria are deleted, but now I need the option to exclude a particular file from those to be deleted: the particular file would be identified by having some specific text in its filename. So for example I need to be able to delete all files meeting the criteria EXCEPT any files that have the string "summary" as part of the filename.

View 5 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

Modules & VBA :: Edit A String Or Use Wildcard In Search Function

Jul 18, 2013

Access 2007

I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.

Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.

Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.

Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()

[Code] .....

View 2 Replies View Related

String Searching

Mar 20, 2006

I have a problem with an SQL query that is used to create a report, the query itself works fine, however I need to get it to do something extra. Basically we have a column that contains 1-3 letters. The letters are A, B and C. The query searches the database and returns result depending on which of the letters we entered. We do this using the like statment -:

SELECT * FROM TableUser WHERE UserType LIKE B

So the above SQL statement returns all entries with the letter B in the column UserType. Also we my need to search for AB, which returns all entries with AB and ABC. The problem is that we sometimes need to search for AC, which should return the values AC and ABC. The problem is that it only returns the values AC and not ABC. I know why it does this as it is searching for a string like 'AC', I am just not sure how to get round the problem. Is there a way of searching the string for the Value AC so that it returns the entries AC and ABC.
Thanks in advance for any help.
Cheers
Andy

View 1 Replies View Related

Searching Queries For Some String.

Jun 8, 2005

Is there a way that I can search the queries in my Access Database for certain text string? If someone knows please post some tips.

Thanks...

View 1 Replies View Related

Queries :: Onclick Event - Searching For A String From Table

Aug 26, 2013

I have an access form and there is a textbox in which i enter a string and have a search button so I need the onclick event procedure to search a string from my table. so how do i do it in access 2010.

View 7 Replies View Related

Partial Sum

May 14, 2007

I wan't access to calculate partial sums based on dates(formatted american style here), in particular, the amount ordered of a certain product until a certain date instead of the amount ordered at a certain date. So:

product date amount
a 01-01-2005 1
b 02-01-2005 1
a 07-01-2005 2
a 10-01-2005 1
b 01-02-2005 4
b 01-03-2005 1

Should become:

product date amount
a 01-01-2005 1
b 02-01-2005 1
a 07-01-2005 3(=1+2)
a 10-01-2005 4(=1+2+1)
b 01-02-2005 5(=1+4)
b 01-03-2005 6(=1+4+1)

I have done it with the following query:

SELECT tespartialsum.produkt_nr, tespartialsum.transdate, tespartialsum.amount, Sum(tespartialsum_1.amount) AS SumOfamount
FROM tespartialsum AS tespartialsum_1 INNER JOIN tespartialsum ON tespartialsum_1.produkt_nr = tespartialsum.produkt_nr
WHERE (((tespartialsum_1.transdate)<=[tespartialsum].[transdate]))
GROUP BY tespartialsum.produkt_nr, tespartialsum.transdate, tespartialsum.amount;

it works, but only on a restricted version of the table. I want to do it faster to be able to do it for the entire table(70000 products, 1000.000 lines).

Please help..

View 2 Replies View Related

Partial Data

Apr 21, 2006

I have a form where I enter the students lastname, firstname
I need a field that just pulls the first 2 letters of the lastname as well
Is there an easy way to do this

View 2 Replies View Related

Partial Data

Apr 21, 2006

I have a form where I enter the students lastname, firstname
I need a field that just pulls the first 2 letters of the lastname as well
Is there an easy way to do this

View 3 Replies View Related

Partial Data

Apr 21, 2006

I have a form where I enter the students lastname, firstname
I need a field that just pulls the first 2 letters of the lastname as well
Is there an easy way to do this

View 1 Replies View Related

Partial Replication

Feb 9, 2006

Hi,
I've been reading about this alot today but can't decide for sure if I should replicate my database or not. The users will have a front end installed on their local drive and the back end is on a shared server.

Basically the users will not be able to add or delete records. However, they can update one column- its a checkbox that allows them to select a record or records in a continuous form. I imagine that if 2 users were using it simultanuously and one de-selected all the records, they would be deselected on the other persons form? This is why I think I need a partial replica. That way when the user opens the database they get a fresh copy of the new data and can select and deselect drawings without interfering with others.

All records are automatically selected when the form is opened so updating changes in the master is not an issue.

Please let me know if I'm on the right track here..

View 5 Replies View Related

Partial Dates

Jan 4, 2007

I have a database with fields for dates but formatted in "text" so as to allow for partial input of a date. (ie. 12/00/1990 or 00/00/2001) I now need to extract data prior to an exact date and after an exact date. Due to the text format, I am assuming, I cannot create a query to do this.
I have tried the expression below but of course it does not give me what I need.
< 5/1/1995

I have also tried to create a new field formatted for date/time but I cannot get the date to copy into it since some of my dates are partial.

Can someone please tell me what I need to do so that I can extract the data I need? Any help is appreciated as I have a deadline for this report.

View 10 Replies View Related

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 3 Replies View Related

Problem With Partial Replication

Mar 2, 2007

Hello everyone.
Your help is very much appreciated.
I am trying to create partial replica from the design master.
it all goes well until I try to set the filter using:
repPartial.Filters.Append "Customers",jrFilterTypeTable,"Customers.Region = 'CA' "
It throws an error:
"Invalid expression in the ReplicaFilter property".

The replica db, does contain "Customers" table, which has "Region" field.

Thank you for you help.
The code, looks like:

dim repFull as new JRO.Replica
repFull.ActiveConnection = "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESorthwind.mdb"
Call repFull.CreateReplica("C:MY_REPLICA.mdb", "description", jrRepTypePartial)

Set repFull = Nothing
' open the partial replica in exclusive mode and apply filters
repPartial.ActiveConnection = "Data Source=C:MY_REPLICA.mdb; Mode=Share Exclusive"
repPartial.Filters.Append "Customers", jrFilterTypeTable, "Customers.Region like 'CA' "

Cheers
Roy

View 4 Replies View Related

Partial Field Filtering

Dec 15, 2006

Is there a way to filter based on a partial field match. I couldn't find anything posted before, but it doesn't seem like an unusual problem. I have an inventory database and have a field for part number. I would like to create a filter that would display only part numbers that begin with "FNQ" for example. The entire part number would include other numbers and letters, of course. How can I do this?:confused:

View 2 Replies View Related

Partial Entry Query

Jul 11, 2006

When i go to input my query, firstly it doesnt display results half the time, secondly it is normally very problematic in terms of being case-sensitive, exact word matching etc.

What do i use (criteria) to program the query to let me use partial and non case-sensitive search methods?

PLEASE HELP ME!!! PLEASE!!!!

View 1 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







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