Partial Text Search Facility

Feb 28, 2008

I’m trying to provide the users of my database with a text search facility.

The database deals with documents, and I’d like the user to be able to search by title of document, with partial text matches. E.g. a title might be ‘Health and safety at work act’ - so searching by Health and/or safety gives this document as a result.

The table is called ‘tblDocTitle’ with the field ‘Title’ containing just that.

Ideally the search facility would be some sort of text box in which the text, and then a button to initiate that search.

Hope you can help

View Replies


ADVERTISEMENT

A Form That Can Be Used As A Search Facility

Jun 21, 2006

please excuse the ignorance, im am i novice in the process on learning this.

i have a project that requires to create a customer service log of all the queries that come in.

i would like to create a form that would enable me to search for the following

query number
date
account name
account reference

then i would like it to list all the return so you can select the entry to open it or either go to that record.

any help is greatly greatly appriciated.

thank you for your time

View 5 Replies View Related

Another DMax Question (yes I've Used Your Search Facility)

May 14, 2006

Hello,

I have inherited a database that already has heaps of records in it. One of the forms which is used constantly, has a "BookNumber field" (taken from an underlying table called "Participants". At the moment, this number is entered by hand, but it really could be done automatically increasing by 1 each time a new record is created.

My questions are these:

1. If the BookNumber field in the Participants table is a text field, can I use DMax on it, or does it need to be a number field?

2. If it does need to be a number field, will it cause problems to the existing records if I change the text field to a number field in the table design?

3. Where do I put the DMax statement - in the table or in the form? I am assuming that I put it in the form, but point it to the table is that right?

I know you have answered heaps of questions about DMax before (I think I have read just about every one), but I'm still having trouble figuring this out. I tried putting a text box in the form that had an expression that read: =DMax("BookNumber", "Participants")+1 as it's data, but it just came back with an error message.

Please help...

Thanks,
QuietAchiever

View 3 Replies View Related

Partial Text Compare Across Two Tables

Jul 20, 2007

Hi,

I'm trying to compare partial text records across two tables.

The first table simply has a description, such as:
DescriptionHammer
Bone Saw
Power Saw

The second table is a list of terms and a category, such as:
Term, Category
Hammer, 1
Saw, 2

I'm looking to build a query will compare the two and assign each description a category based on the partial match. It seems like this should be fairly easy to do, but I'm struggling to find anything to point down that path.

Any ideas?

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

Forms :: Search Text Box With Option Box As Criteria For Search

Mar 4, 2014

I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.

View 3 Replies View Related

Queries :: Calculate Ratio And Then Arrange Result Based On Facility Types

Mar 20, 2013

I have a simple query like below.

Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;

It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.

The error I get is a syntax error the SELECT statements.

View 3 Replies View Related

Forms :: Database For Medical Facility - Admit And Discharge Radio Buttons

Apr 18, 2015

I have been writing a database for a medical facility. Currently, I have a box to check if a patient in the database is admitted. What I would like to do is create two radio buttons: one to admit and one to discharge.

When the patient is discharged, I want the data in some of the fields on the form(s) to clear, but not all the fields.

I realize the I will need some scripting here.

As an example: the fields for Unit and Room number will clear upon discharge. As well as fields for diagnosis and informed consent signature located on another form within the same table.

View 3 Replies View Related

Forms :: Text Box Search On ID And Populate Other Text Boxes In Same Form

Nov 12, 2013

I am trying to search on EmployeeID field and populate corresponding data like EmployeeName, EmployeePay in other text boxes in the same box .

In my Unbound Form I have three unbound Text Boxes and one Command button:

txtEmpID
txtEmpName
txtEmpPay
cmdFind

In my table EMPLOYEE i have three fields

EmpID -- Autonumber
EmpName -- Text
EmpPay -- Text

View 2 Replies View Related

General :: Search For Highlighted Text In Rich Text Box

Oct 1, 2012

Is there a way of searching a rich text box for any text which is highlighted and then deleting that text? I cannot find an answer to this anywhere..

View 2 Replies View Related

Forms :: Text Box Search To Populate Other Text Boxes?

May 10, 2015

I have a form with two text boxes and a button. I want to be able to type a value into the first Text box click the button and the second text box to be filled with the value which is stored in the Table.

The first Textbox is called barTxt, The second Textbox is called CustTxt The button is called SearchBtn and the Table is called BookInTable. I have been trying to use the code.

Code:

Private Sub SearchBtn_Click()
DoCmd.FindRecord Me.BarTxt.Value, , True, , True
CustTxt.Value = "SELECT BookInTable.Customer FROM BookInTable " & _
" WHERE Customer = """ & Nz(Me.BarTxt) & """" & _
" ORDER BY Customer"
End Sub

This however instead of displaying the Value for customer which is stored in the BookInTable. Displays The code SELECT BookInTable.Customer FROMBookInTable " & _ " WHERE Customer = """ & Nz(Me.BarTxt) & """" & _ " ORDER BY Customer"

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

SQL Text Search.

Dec 5, 2006

Hi all,

I have done a quick search for this on the forum so I apologise if I have missed the thread that talks about this.

Basically I am interested in doing a text search on the SQL used in my Access queries.

Is this possible and how?

Thanks,

Matt.

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

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

Search, Text VS Numeric.

Jun 21, 2005

I have made my Search Form that will search through my tables, some how I have a syntax error for the following: year, Section and ID, these fields are not text I use numeric values so I think the search is not working for that reason, I am not sure how can I fix it though do I have do add val() or sth.


Private Sub Command8_Click()

On Error GoTo Command8_ClickError

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim strWhereCondition As String
Dim strSql As String

strWhereCondition = ""
strSql = "Select distinct Id From AcademicVideo Where True "

'================================'

If Not IsNull(ID) And Trim(ID) <> "" Then
strSql = strSql & " And [Id] = " & [ID]
End If

'================================'

If Not IsNull(Course) And Trim(Course) <> "" Then
If InStr(Course, "*") = 0 Then
strSql = strSql & " And [Course] = '" & [Course] & "'"
Else
strSql = strSql & " And [Course] like '" & [Course] & "'"
End If
End If

'================================'

If Not IsNull([Format]) And Trim([Format]) <> "" Then
If InStr([Format], "*") = 0 Then
strSql = strSql & " And [Format] = '" & [Format] & "'"
Else
strSql = strSql & " And [Format] like '" & [Format] & "'"
End If
End If

'================================'

If Not IsNull([Title]) And Trim([Title]) <> "" Then
If InStr([Title], "*") = 0 Then
strSql = strSql & " And [Title] = '" & [Title] & "'"
Else
strSql = strSql & " And [Title] like '" & [Title] & "'"
End If
End If

'================================'

If Not IsNull([Lecturer]) And Trim([Lecturer]) <> "" Then
If InStr([Lecturer], "*") = 0 Then
strSql = strSql & " And [Lecturer] = '" & [Lecturer] & "'"
Else
strSql = strSql & " And [Lecturer] like '" & [Lecturer] & "'"
End If
End If

'================================'

' If Not IsNull([Section]) And Trim([Section]) <> "" Then
' If InStr([Section], "*") = 0 Then
' strSql = strSql & " And [Section] = " & [Section] & "'"
' Else
' strSql = strSql & " And [Section] like " & [Section] & "'"
' End If
' End If

'================================'

If Not IsNull([Semester]) And Trim([Semester]) <> "" Then
If InStr([Semester], "*") = 0 Then
strSql = strSql & " And [Semester] = '" & [Semester] & "'"
Else
strSql = strSql & " And [Semester] like '" & [Semester] & "'"
End If
End If

'================================'

If Not IsNull([Year]) And Trim([Year]) <> "" Then
If InStr([Year], "*") = 0 Then
strSql = strSql & " And [Year] = " & [Year] & "'"
Else
strSql = strSql & " And [Year] like " & [Year] & "'"
End If
End If

'================================'


If Not IsNull([Description]) And Trim([Description]) <> "" Then
If InStr([Description], "*") = 0 Then
strSql = strSql & " And [Description] = '" & [Description] & "'"
Else
strSql = strSql & " And [Description] like '" & [Description] & "'"
End If
End If

'================================'


Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
' If (rs2.RecordCount = Null) Then

' MsgBox "Enter A valid Record"

If (rs.RecordCount = 0) Then
MsgBox "Could Not found "

Else
strWhereCondition = "[Id] In (" & rs!ID
Do While Not rs.EOF
strWhereCondition = strWhereCondition & ", " & rs!ID
rs.MoveNext
Loop
strWhereCondition = strWhereCondition & ")"
End If
rs.Close

If strWhereCondition <> "" Then
DoCmd.OpenForm "ACVideo", acNormal, , strWhereCondition
DoCmd.Close acForm, "Search AcVideo"
End If

Exit Sub

Command8_ClickError:
MsgBox Err.Number & " " & Err.Description

Resume Next
End Sub

View 2 Replies View Related

Search For A Number Not A Text, SQL

Aug 12, 2005

I have a form with four combo box that will search for a record, as follow:

Field Name "Printer" , DataType "Text"
Field Name Month" , DataType "Text"
Field Name "Year" , DataType "Number"
Field Name "Day" , DataType "Number"

code for Find Record Button:


If Not IsNull(ID) And Trim(ID) <> "" Then
StrSQL = StrSQL & " And [Id] = " & [ID]
End If

If Not IsNull([txtname]) And Trim([txtname]) <> "" Then
If InStr([txtname], "*") = 0 Then

StrSQL = StrSQL & " And [Name] = '" & [txtname] & "'"
Else
StrSQL = StrSQL & " And [Name] like '" & [txtname] & "'"
End If
End If

'================================'


If Not IsNull([txtyear]) And Trim([txtyear]) <> "" Then

If InStr([txtyear], "*") = 0 Then

StrSQL = StrSQL & " And [Year] = '" & [txtyear] & "'"
Else
StrSQL = StrSQL & " And [Year] like '" & [txtyear] & "'"
End If

End If

'================================'


If Not IsNull([txtmonth]) And Trim([txtmonth]) <> "" Then

If InStr([txtmonth], "*") = 0 Then

StrSQL = StrSQL & " And [Month] = '" & [txtmonth] & "'"
Else
StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'"
End If

End If

'================================'


If Not IsNull([txtday]) And Trim([txtday]) <> "" Then

If InStr([txtday], "*") = 0 Then
StrSQL = StrSQL & " And [Day] = '" & [txtday] & "'"
Else
StrSQL = StrSQL & " And [Day] like '" & [txtday] & "'"
End If

End If

'================================'


This is only a part of the code, I have a problem with year and day since they are numbers not text I was told that i cant use this line:

StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'"

I was told that there is no need to use "like", am not really sure how to do it, any help will be appreciated.

Thanks.

View 2 Replies View Related

How To Search Subform With Text Box

Mar 5, 2013

how to search subform with textbox and button in form

View 2 Replies View Related

Search List Box With Text Box

Jun 2, 2010

I have a list box pulling information from a table. It include fields for last name, first name, primary number, secondary number, and id; of course with different names. I display first and last name, but the rest are hidden. What would be ideal is if I could begin typing someones first or last name in the text box and it would just start either narrowing the list, like in itunes, or would start skipping down the list and would be highlighted. For example. If I was searching the number for walmart, i could go to the text box and type "wal" and it might highlight "wallace, james"; and if I added and "m" to that, it would skip down to "walmart".

I would settle for autofill and click a search command button when I'm done, and it takes me to that entry in the list box. I copied some code off the internet. Don't know if it'll work, but here is what I got.

Private Sub txtSearch_AfterUpdate()
Dim srchstrng As String
srchstrng = Me.txtSearch.Value
SQL = " SELECT Phone.last_name, Phone.first_name FROM Phone_"
WHERE (((Phone.last_name) Like '*" & srchstrng & "*'));"
Form.RecordSource = SQL
DoCmd.RunCommand acCmdRefreshPage
End Sub

It says there is a problem with the where clause.

View 6 Replies View Related







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