Find test within a field

G

Guest

Hi all, hope someone can help me.
I have a dB where the user can click a button to open a search form. Here
he/she types the search data into say the "Customer" field, then hits return
and the first form is returned with that record shown. Great so far, the
problem is that the "Customer" may have two parts to the name I.E., Joe
Smith. now this is not found when Joe or Smith is typed in only Joe Smith.

Is there a relatively simple way to have access look into the field for just
part of the data, i.e. Smith.

Here is the code for the unbound text box: txtFindCust

Private Sub txtFindCust_BeforeUpdate(Cancel As Integer)
'Check the Customer Name Exists before running the After Update
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
If IsNull(DLookup("Customer", "Sort Records Query - By Customer",
strCustomer)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo.", , "Error"
End If
End If
End Sub

Private Sub txtFindCust_AfterUpdate()
'Find a Customer Name
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
'Close this form
DoCmd.Close
'Make the RELEASE Button, Label Visible visible
Forms!ViewEditData.cmdReleaseFilter.Visible = True
Forms!ViewEditData.lblRecordLocked.Visible = True
'Open the Edit form at the appropriate record
DoCmd.OpenForm "ViewEditData", , , strCustomer
End If
End Sub

I thought of replacing the = with LIKE in the [Customer] = '" &
Me.txtFindCust part but this does nothing although it still works for the
whole search. I have tried entering *smith* into the box and this is not
found.

Any ideas?
--
Cheers
Chas

***************
* Spectrum is Green *
***************
 
B

Brendan Reynolds

strCustomer = "[CompanyName] Like '*" & CustName & "*'"

See the help topic 'Like Operator' for details.
 
G

Guest

Dear Brendan,
Many thanks for that, it works just great.
I did a search as you suggested in the Help Index and it found TWO topics
but neither would open when clicked. Any ideas?
Topics listed are:
Like Operator Example (Jet SQL)
Like Operator

I am using ACC2000 and other topics Open OK so I think the thelp file is
faulty???
--
Cheers
Chas

***************
* Spectrum is Green *
***************


Brendan Reynolds said:
strCustomer = "[CompanyName] Like '*" & CustName & "*'"

See the help topic 'Like Operator' for details.

--
Brendan Reynolds
Access MVP

Chas Large said:
Hi all, hope someone can help me.
I have a dB where the user can click a button to open a search form. Here
he/she types the search data into say the "Customer" field, then hits
return
and the first form is returned with that record shown. Great so far, the
problem is that the "Customer" may have two parts to the name I.E., Joe
Smith. now this is not found when Joe or Smith is typed in only Joe Smith.

Is there a relatively simple way to have access look into the field for
just
part of the data, i.e. Smith.

Here is the code for the unbound text box: txtFindCust

Private Sub txtFindCust_BeforeUpdate(Cancel As Integer)
'Check the Customer Name Exists before running the After Update
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
If IsNull(DLookup("Customer", "Sort Records Query - By Customer",
strCustomer)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo.", , "Error"
End If
End If
End Sub

Private Sub txtFindCust_AfterUpdate()
'Find a Customer Name
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
'Close this form
DoCmd.Close
'Make the RELEASE Button, Label Visible visible
Forms!ViewEditData.cmdReleaseFilter.Visible = True
Forms!ViewEditData.lblRecordLocked.Visible = True
'Open the Edit form at the appropriate record
DoCmd.OpenForm "ViewEditData", , , strCustomer
End If
End Sub

I thought of replacing the = with LIKE in the [Customer] = '" &
Me.txtFindCust part but this does nothing although it still works for the
whole search. I have tried entering *smith* into the box and this is not
found.

Any ideas?
--
Cheers
Chas

***************
* Spectrum is Green *
***************
Click to expand...
Click to expand...
 
B

Brendan Reynolds

Here's a link to the on-line help topic. This is from the Access 2003
documentation, but I'm reasonably sure that it is all relevant to Access
2000 - I don't think there has been any change in the behaviour of the Like
operator.

http://office.microsoft.com/en-gb/assistance/HP010322531033.aspx

--
Brendan Reynolds
Access MVP

Chas Large said:
Dear Brendan,
Many thanks for that, it works just great.
I did a search as you suggested in the Help Index and it found TWO topics
but neither would open when clicked. Any ideas?
Topics listed are:
Like Operator Example (Jet SQL)
Like Operator

I am using ACC2000 and other topics Open OK so I think the thelp file is
faulty???
--
Cheers
Chas

***************
* Spectrum is Green *
***************


Brendan Reynolds said:
strCustomer = "[CompanyName] Like '*" & CustName & "*'"

See the help topic 'Like Operator' for details.

--
Brendan Reynolds
Access MVP

Chas Large said:
Hi all, hope someone can help me.
I have a dB where the user can click a button to open a search form.
Here
he/she types the search data into say the "Customer" field, then hits
return
and the first form is returned with that record shown. Great so far,
the
problem is that the "Customer" may have two parts to the name I.E., Joe
Smith. now this is not found when Joe or Smith is typed in only Joe
Smith.

Is there a relatively simple way to have access look into the field for
just
part of the data, i.e. Smith.

Here is the code for the unbound text box: txtFindCust

Private Sub txtFindCust_BeforeUpdate(Cancel As Integer)
'Check the Customer Name Exists before running the After Update
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
If IsNull(DLookup("Customer", "Sort Records Query - By
Customer",
strCustomer)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo.", , "Error"
End If
End If
End Sub

Private Sub txtFindCust_AfterUpdate()
'Find a Customer Name
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
'Close this form
DoCmd.Close
'Make the RELEASE Button, Label Visible visible
Forms!ViewEditData.cmdReleaseFilter.Visible = True
Forms!ViewEditData.lblRecordLocked.Visible = True
'Open the Edit form at the appropriate record
DoCmd.OpenForm "ViewEditData", , , strCustomer
End If
End Sub

I thought of replacing the = with LIKE in the [Customer] = '" &
Me.txtFindCust part but this does nothing although it still works for
the
whole search. I have tried entering *smith* into the box and this is
not
found.

Any ideas?
--
Cheers
Chas

***************
* Spectrum is Green *
***************
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks again Brendan
--
Cheers
Chas

***************
* Spectrum is Green *
***************


Brendan Reynolds said:
Here's a link to the on-line help topic. This is from the Access 2003
documentation, but I'm reasonably sure that it is all relevant to Access
2000 - I don't think there has been any change in the behaviour of the Like
operator.

http://office.microsoft.com/en-gb/assistance/HP010322531033.aspx

--
Brendan Reynolds
Access MVP

Chas Large said:
Dear Brendan,
Many thanks for that, it works just great.
I did a search as you suggested in the Help Index and it found TWO topics
but neither would open when clicked. Any ideas?
Topics listed are:
Like Operator Example (Jet SQL)
Like Operator

I am using ACC2000 and other topics Open OK so I think the thelp file is
faulty???
--
Cheers
Chas

***************
* Spectrum is Green *
***************


Brendan Reynolds said:
strCustomer = "[CompanyName] Like '*" & CustName & "*'"

See the help topic 'Like Operator' for details.

--
Brendan Reynolds
Access MVP

Hi all, hope someone can help me.
I have a dB where the user can click a button to open a search form.
Here
he/she types the search data into say the "Customer" field, then hits
return
and the first form is returned with that record shown. Great so far,
the
problem is that the "Customer" may have two parts to the name I.E., Joe
Smith. now this is not found when Joe or Smith is typed in only Joe
Smith.

Is there a relatively simple way to have access look into the field for
just
part of the data, i.e. Smith.

Here is the code for the unbound text box: txtFindCust

Private Sub txtFindCust_BeforeUpdate(Cancel As Integer)
'Check the Customer Name Exists before running the After Update
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
If IsNull(DLookup("Customer", "Sort Records Query - By
Customer",
strCustomer)) Then
Cancel = True
MsgBox "No such entry." & vbCrLf & _
"Correct the entry, or press <Esc> to undo.", , "Error"
End If
End If
End Sub

Private Sub txtFindCust_AfterUpdate()
'Find a Customer Name
Dim strCustomer As String
If Not IsNull(Me.txtFindCust) Then
strCustomer = "[Customer] = '" & Me.txtFindCust & "'"
'Close this form
DoCmd.Close
'Make the RELEASE Button, Label Visible visible
Forms!ViewEditData.cmdReleaseFilter.Visible = True
Forms!ViewEditData.lblRecordLocked.Visible = True
'Open the Edit form at the appropriate record
DoCmd.OpenForm "ViewEditData", , , strCustomer
End If
End Sub

I thought of replacing the = with LIKE in the [Customer] = '" &
Me.txtFindCust part but this does nothing although it still works for
the
whole search. I have tried entering *smith* into the box and this is
not
found.

Any ideas?
--
Cheers
Chas

***************
* Spectrum is Green *
***************
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter a Report with a Form 2
Code to Dynamically Filter a Report; Not Working 2
Improving treeview load time 13
Require Field Entry based On Other Entry 2
Runtime error 13 Type mismatch - something to do with date?? 4
Component doesn't support automatrion? 8
Change a report to a query 1
YES/NO FIELD IN COMBOBOX 6

Top