Writing Code for a better FIND command

[jquest]

Hi Again;

I have had help from this group before and want to thank everyone,
especially PCDatasheet.
My database includes a field called HomePhone, it uses the (xxx)xxx-xxx
format to include area code.
When a customer calls, I currently use Ctrl F with the HomePhone field
highlighted. Then I enter the last 4 digits and use the find next option.
This is cumbersome, so I have tried several methods (including a macro)
using comand buttons, but nothing seems quite right.

The future of this function in my database is more complicated than just
using Ctrl F function and using find next repeatedly. I am hoping to
incorporate a list or combo box.

I would like to click a command button, have it search and find all
occurrences of the least 4 digits of the phone number (based on the 4 digits
I input), and then present a list or combo box that displays the entire
phone number, customer name and address of each record that contains the 4
digits as the LAST 4 digits of the phone number, not the first 4 or any 4.

Is this possible? and can anyone recommend code that would work.

I use Access 2000 and XP Home.

TIA

[PC Datasheet]

Thanks for the compliment!

The first thing we need to know to help you is how your telephone numbers
are stored. Open the table and let us know if the dash is stored. Perhaps
give us an example number. Also check for us to see if all the numbers are
stored in the same format.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"jquest" <ad******@telus.net> wrote in message
news:yW4Sd.5353$9a3.946@edtnps91...

Hi Again;

I have had help from this group before and want to thank everyone,
especially PCDatasheet.
My database includes a field called HomePhone, it uses the (xxx)xxx-xxx
format to include area code.
When a customer calls, I currently use Ctrl F with the HomePhone field
highlighted. Then I enter the last 4 digits and use the find next option.
This is cumbersome, so I have tried several methods (including a macro)
using comand buttons, but nothing seems quite right.

The future of this function in my database is more complicated than just
using Ctrl F function and using find next repeatedly. I am hoping to
incorporate a list or combo box.

I would like to click a command button, have it search and find all
occurrences of the least 4 digits of the phone number (based on the 4 digits I input), and then present a list or combo box that displays the entire
phone number, customer name and address of each record that contains the 4
digits as the LAST 4 digits of the phone number, not the first 4 or any 4.

Is this possible? and can anyone recommend code that would work.

I use Access 2000 and XP Home.

TIA

[Filips Benoit]

listbox.recordsource

SELECT TBL_PERSOON.PERSOON_ID, TBL_PERSOON.PERSOON_TEL_PRIVE,
Right([TBL_PERSOON]![PERSOON_TEL_PRIVE],4) AS Expr1
FROM TBL_PERSOON
WHERE (((TBL_PERSOON.PERSOON_TEL_PRIVE) Is Not Null) AND
((Right([TBL_PERSOON]![PERSOON_TEL_PRIVE],4))="1234"));

Filip

"jquest" <ad******@telus.net> wrote in message
news:yW4Sd.5353$9a3.946@edtnps91...

Hi Again;

I have had help from this group before and want to thank everyone,
especially PCDatasheet.
My database includes a field called HomePhone, it uses the (xxx)xxx-xxx
format to include area code.
When a customer calls, I currently use Ctrl F with the HomePhone field
highlighted. Then I enter the last 4 digits and use the find next option.
This is cumbersome, so I have tried several methods (including a macro)
using comand buttons, but nothing seems quite right.

The future of this function in my database is more complicated than just
using Ctrl F function and using find next repeatedly. I am hoping to
incorporate a list or combo box.

I would like to click a command button, have it search and find all
occurrences of the least 4 digits of the phone number (based on the 4 digits I input), and then present a list or combo box that displays the entire
phone number, customer name and address of each record that contains the 4
digits as the LAST 4 digits of the phone number, not the first 4 or any 4.

Is this possible? and can anyone recommend code that would work.

I use Access 2000 and XP Home.

TIA

[jquest]

Here is an actual Example

!\(999") "000\-0000
Text Field Width is 14 characters

One of the numbers in the database is:
[phone]

I store 3 different phone numbers for each record, HomePhone, WorkPhone and
CellPhone. That way, no matter where they call from I can identify their
file right away. All phone numbers are stored the same way, and yes the
brackets and dashes are stored.

TIA

"PC Datasheet" <no****@nospam.spam> wrote in message
news:i9*****************@newsread3.news.atl.earthl ink.net...

Thanks for the compliment!

The first thing we need to know to help you is how your telephone numbers
are stored. Open the table and let us know if the dash is stored. Perhaps
give us an example number. Also check for us to see if all the numbers are
stored in the same format.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"jquest" <ad******@telus.net> wrote in message
news:yW4Sd.5353$9a3.946@edtnps91...

Hi Again;

I have had help from this group before and want to thank everyone,
especially PCDatasheet.
My database includes a field called HomePhone, it uses the (xxx)xxx-xxx
format to include area code.
When a customer calls, I currently use Ctrl F with the HomePhone field
highlighted. Then I enter the last 4 digits and use the find next option. This is cumbersome, so I have tried several methods (including a macro)
using comand buttons, but nothing seems quite right.

The future of this function in my database is more complicated than just
using Ctrl F function and using find next repeatedly. I am hoping to
incorporate a list or combo box.

I would like to click a command button, have it search and find all
occurrences of the least 4 digits of the phone number (based on the 4

digits

I input), and then present a list or combo box that displays the entire
phone number, customer name and address of each record that contains the 4 digits as the LAST 4 digits of the phone number, not the first 4 or any 4.
Is this possible? and can anyone recommend code that would work.

I use Access 2000 and XP Home.

TIA

[PC Datasheet]

You need tables that look like:
TblCustomer
CustomerID
FirstName
Lastname
Address
City
State
Zipcode

TblPhoneNum
PhoneNumID
CustomerID
HomePhone
WorkPhone
CellPhone

The tables need a relationship on CustomerID.

Create a query named QryFindCustomer that includes both tables. Put the
following fields in the query in the order shown below:
CustomerToFind:TblCustomer.CustomerID
Customer:[LastName] & ", " & [[Firstname] <<Sort Accending>>
Address
CityStateZip:[City] & ", " & [State] & " " & [Zipcode]
HomePhone
LastFourDigits:Right([HomePhone],4)
Set the criteria for LastFourDigits as:
Forms!FrmSearchForCustomer!FourDigitNum

Create a search form named FrmSearchForCustomer. Add an unbound textbox
named FourDigitNum. Add a listbox named CustomersWithFourDigits with the
following properties:
RowSource <<Leave Blank>>
Bound Column 1
Column Count 5
Column Width 0;1.5;1.5;1.5;1
Note: You will need to adjust the widths to suit.

Put the following code in the OnChange event of the textbox, FourDigitNum:
If Len(Me!FourDigitNum) = 4 Then
Me!CustomersWithFourDigits.RowSource = "QryFindCustomer"
End If

The listbox will have the value CustomerID. You will need to put code in the
listbox's AfterUpdate event so when you select a customer, code will give
you what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"jquest" <ad******@telus.net> wrote in message
news:Q96Sd.5484$9a3.1023@edtnps91...

Here is an actual Example

!\(999") "000\-0000
Text Field Width is 14 characters

One of the numbers in the database is:
[phone]

I store 3 different phone numbers for each record, HomePhone, WorkPhone and CellPhone. That way, no matter where they call from I can identify their
file right away. All phone numbers are stored the same way, and yes the
brackets and dashes are stored.

TIA

"PC Datasheet" <no****@nospam.spam> wrote in message
news:i9*****************@newsread3.news.atl.earthl ink.net...

Thanks for the compliment!

The first thing we need to know to help you is how your telephone numbers

are stored. Open the table and let us know if the dash is stored. Perhaps give us an example number. Also check for us to see if all the numbers are stored in the same format.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"jquest" <ad******@telus.net> wrote in message
news:yW4Sd.5353$9a3.946@edtnps91...

Hi Again;

I have had help from this group before and want to thank everyone,
especially PCDatasheet.
My database includes a field called HomePhone, it uses the (xxx)xxx-xxx format to include area code.
When a customer calls, I currently use Ctrl F with the HomePhone field highlighted. Then I enter the last 4 digits and use the find next

option. This is cumbersome, so I have tried several methods (including a macro) using comand buttons, but nothing seems quite right.

The future of this function in my database is more complicated than just using Ctrl F function and using find next repeatedly. I am hoping to
incorporate a list or combo box.

I would like to click a command button, have it search and find all
occurrences of the least 4 digits of the phone number (based on the 4

digits

I input), and then present a list or combo box that displays the entire phone number, customer name and address of each record that contains

the 4 digits as the LAST 4 digits of the phone number, not the first 4 or

any

4.
Is this possible? and can anyone recommend code that would work.

I use Access 2000 and XP Home.

TIA

[jquest]

Thank You;

I have entered the code and have the query working well. I still canot
populate the list box with the query results, but right now I am running the
query and displaying it once the 4 digits are entered. It would be better if
I could populate the list box, because then I would double click the
important entry aqnd pull up their records automatically
TIA
"PC Datasheet" <no****@nospam.spam> wrote in message
news:mL*****************@newsread2.news.atl.earthl ink.net...

You need tables that look like:
TblCustomer
CustomerID
FirstName
Lastname
Address
City
State
Zipcode

TblPhoneNum
PhoneNumID
CustomerID
HomePhone
WorkPhone
CellPhone

The tables need a relationship on CustomerID.

Create a query named QryFindCustomer that includes both tables. Put the
following fields in the query in the order shown below:
CustomerToFind:TblCustomer.CustomerID
Customer:[LastName] & ", " & [[Firstname] <<Sort Accending>>
Address
CityStateZip:[City] & ", " & [State] & " " & [Zipcode]
HomePhone
LastFourDigits:Right([HomePhone],4)
Set the criteria for LastFourDigits as:
Forms!FrmSearchForCustomer!FourDigitNum

Create a search form named FrmSearchForCustomer. Add an unbound textbox
named FourDigitNum. Add a listbox named CustomersWithFourDigits with the
following properties:
RowSource <<Leave Blank>>
Bound Column 1
Column Count 5
Column Width 0;1.5;1.5;1.5;1
Note: You will need to adjust the widths to suit.

Put the following code in the OnChange event of the textbox, FourDigitNum:
If Len(Me!FourDigitNum) = 4 Then
Me!CustomersWithFourDigits.RowSource = "QryFindCustomer"
End If

The listbox will have the value CustomerID. You will need to put code in the listbox's AfterUpdate event so when you select a customer, code will give
you what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"jquest" <ad******@telus.net> wrote in message
news:Q96Sd.5484$9a3.1023@edtnps91...

Here is an actual Example

!\(999") "000\-0000
Text Field Width is 14 characters

One of the numbers in the database is:
[phone]

I store 3 different phone numbers for each record, HomePhone, WorkPhone

and

CellPhone. That way, no matter where they call from I can identify their
file right away. All phone numbers are stored the same way, and yes the
brackets and dashes are stored.

TIA

"PC Datasheet" <no****@nospam.spam> wrote in message
news:i9*****************@newsread3.news.atl.earthl ink.net...

Thanks for the compliment!

The first thing we need to know to help you is how your telephone numbers are stored. Open the table and let us know if the dash is stored. Perhaps give us an example number. Also check for us to see if all the numbers are stored in the same format.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"jquest" <ad******@telus.net> wrote in message
news:yW4Sd.5353$9a3.946@edtnps91...
> Hi Again;
>
> I have had help from this group before and want to thank everyone,
> especially PCDatasheet.
> My database includes a field called HomePhone, it uses the (xxx)xxx-xxx > format to include area code.
> When a customer calls, I currently use Ctrl F with the HomePhone field > highlighted. Then I enter the last 4 digits and use the find next

option.

> This is cumbersome, so I have tried several methods (including a macro) > using comand buttons, but nothing seems quite right.
>
> The future of this function in my database is more complicated than just > using Ctrl F function and using find next repeatedly. I am hoping to
> incorporate a list or combo box.
>
> I would like to click a command button, have it search and find all
> occurrences of the least 4 digits of the phone number (based on the 4 digits
> I input), and then present a list or combo box that displays the entire > phone number, customer name and address of each record that contains

the

4

> digits as the LAST 4 digits of the phone number, not the first 4 or

any

4.

>
> Is this possible? and can anyone recommend code that would work.
>
> I use Access 2000 and XP Home.
>
> TIA
>
>

[Br]

jquest wrote:

Thank You;

I have entered the code and have the query working well. I still canot
populate the list box with the query results, but right now I am
running the query and displaying it once the 4 digits are entered. It
would be better if I could populate the list box, because then I
would double click the important entry aqnd pull up their records
automatically

I use a simply method. I have a text box where the user enters the
search text. I then use some code to find a match. I then search for
another match. If there was only one match then it moves to that record.
If there is more than one it passes an SQL string to a popup form to
poulate a listbox on it with the matches. The user then double-clicks
the one they want and they are returned to the main form with that
record no current.

eg. (just quickly typed this so it might not quite work :)

SearchString = Me![Search]
Criteria = "[HomePhone] LIKE '%" & SearchString & "%'"
Set rs = Me.bookmark

rs.findfirst Criteria
if rs.nomatch then
'no matches, display message
else
rs.findnext Criteria
if rs.nomatch then
'more than one match found, open form to dispay results
'code on popup form will move to required record once selected
'or you can pass the value back or something

Docmd.openform "frmSearchMatches",,,Criteria,,acDailog

else
'one match found, move to that record
Me.bookmark = rs.bookmark
end if
set rs = nothing

[Steve]

The listbox will be blank when the search form opens and will not fill until
you have entered four digits in the textbox and pressed Enter.

Did you do this?
Put the following code in the OnChange event of the textbox, FourDigitNum:
If Len(Me!FourDigitNum) = 4 Then
Me!CustomersWithFourDigits.RowSource = "QryFindCustomer"
End If

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"PC Datasheet" <no****@nospam.spam> wrote in message
news:mL*****************@newsread2.news.atl.earthl ink.net...

You need tables that look like:
TblCustomer
CustomerID
FirstName
Lastname
Address
City
State
Zipcode

TblPhoneNum
PhoneNumID
CustomerID
HomePhone
WorkPhone
CellPhone

The tables need a relationship on CustomerID.

Create a query named QryFindCustomer that includes both tables. Put the
following fields in the query in the order shown below:
CustomerToFind:TblCustomer.CustomerID
Customer:[LastName] & ", " & [[Firstname] <<Sort Accending>>
Address
CityStateZip:[City] & ", " & [State] & " " & [Zipcode]
HomePhone
LastFourDigits:Right([HomePhone],4)
Set the criteria for LastFourDigits as:
Forms!FrmSearchForCustomer!FourDigitNum

Create a search form named FrmSearchForCustomer. Add an unbound textbox
named FourDigitNum. Add a listbox named CustomersWithFourDigits with the
following properties:
RowSource <<Leave Blank>>
Bound Column 1
Column Count 5
Column Width 0;1.5;1.5;1.5;1
Note: You will need to adjust the widths to suit.

Put the following code in the OnChange event of the textbox, FourDigitNum:
If Len(Me!FourDigitNum) = 4 Then
Me!CustomersWithFourDigits.RowSource = "QryFindCustomer"
End If

The listbox will have the value CustomerID. You will need to put code in the listbox's AfterUpdate event so when you select a customer, code will give
you what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"jquest" <ad******@telus.net> wrote in message
news:Q96Sd.5484$9a3.1023@edtnps91...

Here is an actual Example

!\(999") "000\-0000
Text Field Width is 14 characters

One of the numbers in the database is:
[phone]

I store 3 different phone numbers for each record, HomePhone, WorkPhone

and

CellPhone. That way, no matter where they call from I can identify their
file right away. All phone numbers are stored the same way, and yes the
brackets and dashes are stored.

TIA

"PC Datasheet" <no****@nospam.spam> wrote in message
news:i9*****************@newsread3.news.atl.earthl ink.net...

Thanks for the compliment!

The first thing we need to know to help you is how your telephone numbers are stored. Open the table and let us know if the dash is stored. Perhaps give us an example number. Also check for us to see if all the numbers are stored in the same format.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"jquest" <ad******@telus.net> wrote in message
news:yW4Sd.5353$9a3.946@edtnps91...
> Hi Again;
>
> I have had help from this group before and want to thank everyone,
> especially PCDatasheet.
> My database includes a field called HomePhone, it uses the (xxx)xxx-xxx > format to include area code.
> When a customer calls, I currently use Ctrl F with the HomePhone field > highlighted. Then I enter the last 4 digits and use the find next

option.

> This is cumbersome, so I have tried several methods (including a macro) > using comand buttons, but nothing seems quite right.
>
> The future of this function in my database is more complicated than just > using Ctrl F function and using find next repeatedly. I am hoping to
> incorporate a list or combo box.
>
> I would like to click a command button, have it search and find all
> occurrences of the least 4 digits of the phone number (based on the 4 digits
> I input), and then present a list or combo box that displays the entire > phone number, customer name and address of each record that contains

the

4

> digits as the LAST 4 digits of the phone number, not the first 4 or

any

4.

>
> Is this possible? and can anyone recommend code that would work.
>
> I use Access 2000 and XP Home.
>
> TIA
>
>

[gm]

Hi Steve;

Yes, I entered the code verbatim. I have even cut and pasted it so I know I
haven't made a mistake and overlooked a character.

Thank You for your help.

As an experiment, I tried setting the Row Source Type to Table Query. It
alarms that it can't find the field called Form, but it populates the list.
The problem is that it only populates it once, not on further tries where I
enter a different 4 digit number. But it does work that one time immediately
after I change the RowSource Type.
"Steve" <no****@nospam.spam> wrote in message
news:jc****************@newsread2.news.atl.earthli nk.net...

The listbox will be blank when the search form opens and will not fill until you have entered four digits in the textbox and pressed Enter.

Did you do this?
Put the following code in the OnChange event of the textbox, FourDigitNum:
If Len(Me!FourDigitNum) = 4 Then
Me!CustomersWithFourDigits.RowSource = "QryFindCustomer"
End If

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"PC Datasheet" <no****@nospam.spam> wrote in message
news:mL*****************@newsread2.news.atl.earthl ink.net...

You need tables that look like:
TblCustomer
CustomerID
FirstName
Lastname
Address
City
State
Zipcode

TblPhoneNum
PhoneNumID
CustomerID
HomePhone
WorkPhone
CellPhone

The tables need a relationship on CustomerID.

Create a query named QryFindCustomer that includes both tables. Put the
following fields in the query in the order shown below:
CustomerToFind:TblCustomer.CustomerID
Customer:[LastName] & ", " & [[Firstname] <<Sort Accending>>
Address
CityStateZip:[City] & ", " & [State] & " " & [Zipcode]
HomePhone
LastFourDigits:Right([HomePhone],4)
Set the criteria for LastFourDigits as:
Forms!FrmSearchForCustomer!FourDigitNum

Create a search form named FrmSearchForCustomer. Add an unbound textbox
named FourDigitNum. Add a listbox named CustomersWithFourDigits with the
following properties:
RowSource <<Leave Blank>>
Bound Column 1
Column Count 5
Column Width 0;1.5;1.5;1.5;1
Note: You will need to adjust the widths to suit.

Put the following code in the OnChange event of the textbox, FourDigitNum:

If Len(Me!FourDigitNum) = 4 Then
Me!CustomersWithFourDigits.RowSource = "QryFindCustomer"
End If

The listbox will have the value CustomerID. You will need to put code in the

listbox's AfterUpdate event so when you select a customer, code will give you what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"jquest" <ad******@telus.net> wrote in message
news:Q96Sd.5484$9a3.1023@edtnps91...

Here is an actual Example

!\(999") "000\-0000
Text Field Width is 14 characters

One of the numbers in the database is:
[phone]

I store 3 different phone numbers for each record, HomePhone, WorkPhone

and

CellPhone. That way, no matter where they call from I can identify

their file right away. All phone numbers are stored the same way, and yes the brackets and dashes are stored.

TIA

"PC Datasheet" <no****@nospam.spam> wrote in message
news:i9*****************@newsread3.news.atl.earthl ink.net...
> Thanks for the compliment!
>
> The first thing we need to know to help you is how your telephone

numbers

> are stored. Open the table and let us know if the dash is stored.

Perhaps

> give us an example number. Also check for us to see if all the numbers are

> stored in the same format.
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> re******@pcdatasheet.com
> www.pcdatasheet.com
>
>
>
>
> "jquest" <ad******@telus.net> wrote in message
> news:yW4Sd.5353$9a3.946@edtnps91...
> > Hi Again;
> >
> > I have had help from this group before and want to thank everyone,
> > especially PCDatasheet.
> > My database includes a field called HomePhone, it uses the

(xxx)xxx-xxx

> > format to include area code.
> > When a customer calls, I currently use Ctrl F with the HomePhone

field

> > highlighted. Then I enter the last 4 digits and use the find next
option.
> > This is cumbersome, so I have tried several methods (including a

macro)

> > using comand buttons, but nothing seems quite right.
> >
> > The future of this function in my database is more complicated

than just

> > using Ctrl F function and using find next repeatedly. I am hoping

to > > incorporate a list or combo box.
> >
> > I would like to click a command button, have it search and find all > > occurrences of the least 4 digits of the phone number (based on

the 4 > digits
> > I input), and then present a list or combo box that displays the

entire

> > phone number, customer name and address of each record that

contains the

4
> > digits as the LAST 4 digits of the phone number, not the first 4

or any

4.
> >
> > Is this possible? and can anyone recommend code that would work.
> >
> > I use Access 2000 and XP Home.
> >
> > TIA
> >
> >
>
>