Text Field for Tracking Number - Ignore the dash

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to ignore the "-" (dash) character in a text field somehow? I
want to be able to search any part of the field, but I never know where the
dash is placed, so it messes up my searches. I have the same problem for
spaces in the text field, although it's less of a problem than the dash.

I have a text field used to enter tracking numbers from UPS, FedEx, DHL,
etc. Each carrier has its own number of characters and formats. We enter
the data in the format it is given to us, sometimes with spaces, sometimes
with dashes, sometimes alpha, sometimes numeric. I would like Access to
store the field contents without dashes, and if possible, without spaces
regardless of how it is typed. That way, I could type any series of
characters in my search box.

Is this even possible?
Thanks
Sammie
 
Dear Sammie:

You could use code to replace the dashes and spaces when you work with the
field, for example.


Private Sub txtData_AfterUpdate()
Me.txtData = Replace(Me.txtData, "-", "") 'Replace dashes
Me.txtData = Replace(Me.txtData, " ", "") 'Replace spaces...
End Sub

Note: IIRC the Replace() function wasn't in Access 97...

HTH
Fred Boer
 
Fred,
Works great - just what I was looking for. The only problem is that I need
the tracking number field printed on a report as typed (with dashes and
spaces), but I want it stored as text without the dashes & spaces for
searching purposes. How can I do that? Do I need to create a second field
to store the new text value?
Thanks
Sammie
 
Hi Sammie:

No, no... you can store the data as is, with dashes and so on. Then, when
you want to search, create a query expression that removes the unwanted
characters, or do it in your code.

HTH
Fred
 
Fred,
I guess I need a little more help with that. Using a command button like
this:

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

How would I add your code?
Thanks
Sammie
 
Hi!

Well, let me try to do a better job of explaining! You are working from a
form, correct? And the form is based on a table? You could change that, and
base the form on a *query* of the table, not the table itself. The query
could have an expression which would create a field in the resulting
recordset that has your "massaged" data. If you don't know how to create an
expression in a query, let me know...

So the form could have one field of the original data (if you want), and
another field (generated by the expression in the query) which would have
the data formatted as you like. Then just open the "Find and Replace"
dialogue (which is what your code does, if I am not mistaken...), and then
do a find on the field with the modified data.

Please post back if that isn't helpful!

Cheers!
Fred
 
Hi Fred
Thank you for your kind response.
I am with you on the query thing, but I need help with the expression. I
know how to write expressions, but I can't figure this one out.
Thanks
Sammie
 
Great! We'll get there! Open the query design grid. Add whatever fields you
like from the table. Then, at the top of an empty column, enter something
like this:

ExpNoDash: Replace([tbldata].[txtData],"-","")

When you run the query, there should be a field called "ExpNoDash" with the
data from txtData minus the dashes. "tblData" and "txtData" should be
replaced with the table and field name you are using for the unaltered data,
of course.

Try it and let me know how it goes!

Fred
 
Hi Fred
Thanks for staying with me on this. I learn things every time.
Now I've got 2 working statements in my query: one for dashes and one for
spaces. How do I combine them in 1 statement?
TrkNoDashes: Replace([Tracking Number],"-","")
TrkNoSpaces: Replace([Tracking Number]," ","")
Thanks!
Sammie

Fred Boer said:
Great! We'll get there! Open the query design grid. Add whatever fields you
like from the table. Then, at the top of an empty column, enter something
like this:

ExpNoDash: Replace([tbldata].[txtData],"-","")

When you run the query, there should be a field called "ExpNoDash" with the
data from txtData minus the dashes. "tblData" and "txtData" should be
replaced with the table and field name you are using for the unaltered data,
of course.

Try it and let me know how it goes!

Fred

Sammie said:
Hi Fred
Thank you for your kind response.
I am with you on the query thing, but I need help with the expression. I
know how to write expressions, but I can't figure this one out.
Thanks
Sammie
Click to expand...
Click to expand...
 
Hi Sammie:

Well, someone probably can suggest a better way, but this will work...

TrkNoDashes: Replace(Replace([Tracking Number]," ",""),"-","")

Basically, you nest one Replace() function inside the other Replace()
function.

HTH
Fred Boer


Sammie said:
Hi Fred
Thanks for staying with me on this. I learn things every time.
Now I've got 2 working statements in my query: one for dashes and one for
spaces. How do I combine them in 1 statement?
TrkNoDashes: Replace([Tracking Number],"-","")
TrkNoSpaces: Replace([Tracking Number]," ","")
Thanks!
Sammie

Fred Boer said:
Great! We'll get there! Open the query design grid. Add whatever fields
you
like from the table. Then, at the top of an empty column, enter something
like this:

ExpNoDash: Replace([tbldata].[txtData],"-","")

When you run the query, there should be a field called "ExpNoDash" with
the
data from txtData minus the dashes. "tblData" and "txtData" should be
replaced with the table and field name you are using for the unaltered
data,
of course.

Try it and let me know how it goes!

Fred

Sammie said:
Hi Fred
Thank you for your kind response.
I am with you on the query thing, but I need help with the expression.
I
know how to write expressions, but I can't figure this one out.
Thanks
Sammie


:

Hi!

Well, let me try to do a better job of explaining! You are working
from a
form, correct? And the form is based on a table? You could change
that, and
base the form on a *query* of the table, not the table itself. The
query
could have an expression which would create a field in the resulting
recordset that has your "massaged" data. If you don't know how to
create an
expression in a query, let me know...

So the form could have one field of the original data (if you want),
and
another field (generated by the expression in the query) which would have
the data formatted as you like. Then just open the "Find and Replace"
dialogue (which is what your code does, if I am not mistaken...), and then
do a find on the field with the modified data.

Please post back if that isn't helpful!

Cheers!
Fred




Fred,
I guess I need a little more help with that. Using a command
button like
this:

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

How would I add your code?
Thanks
Sammie

:

Hi Sammie:

No, no... you can store the data as is, with dashes and so on.
Then,
when
you want to search, create a query expression that removes the unwanted
characters, or do it in your code.

HTH
Fred


Fred,
Works great - just what I was looking for. The only problem is that
I
need
the tracking number field printed on a report as typed (with dashes
and
spaces), but I want it stored as text without the dashes &
spaces for
searching purposes. How can I do that? Do I need to create a second
field
to store the new text value?
Thanks
Sammie

:

Dear Sammie:

You could use code to replace the dashes and spaces when you work
with
the
field, for example.


Private Sub txtData_AfterUpdate()
Me.txtData = Replace(Me.txtData, "-", "") 'Replace dashes
Me.txtData = Replace(Me.txtData, " ", "") 'Replace spaces...
End Sub

Note: IIRC the Replace() function wasn't in Access 97...

HTH
Fred Boer


Is it possible to ignore the "-" (dash) character in a text field
somehow?
I
want to be able to search any part of the field, but I
never know
where
the
dash is placed, so it messes up my searches. I have the
same
problem
for
spaces in the text field, although it's less of a problem
than the
dash.

I have a text field used to enter tracking numbers from
UPS,
FedEx,
DHL,
etc. Each carrier has its own number of characters and formats.
We
enter
the data in the format it is given to us, sometimes with spaces,
sometimes
with dashes, sometimes alpha, sometimes numeric. I would
like
Access
to
store the field contents without dashes, and if possible, without
spaces
regardless of how it is typed. That way, I could type any series
of
characters in my search box.

Is this even possible?
Thanks
Sammie
Click to expand...
Click to expand...
Click to expand...
 
How do I combine them in 1 statement?
TrkNoDashes: Replace([Tracking Number],"-","")
TrkNoSpaces: Replace([Tracking Number]," ","")
Click to expand...

Nest them:

Replace(Replace([Tracking Number],"-","")," ", "")

John W. Vinson [MVP]
 
Problem solved. Thank you!
Sammie

Fred Boer said:
Hi Sammie:

Well, someone probably can suggest a better way, but this will work...

TrkNoDashes: Replace(Replace([Tracking Number]," ",""),"-","")

Basically, you nest one Replace() function inside the other Replace()
function.

HTH
Fred Boer


Sammie said:
Hi Fred
Thanks for staying with me on this. I learn things every time.
Now I've got 2 working statements in my query: one for dashes and one for
spaces. How do I combine them in 1 statement?
TrkNoDashes: Replace([Tracking Number],"-","")
TrkNoSpaces: Replace([Tracking Number]," ","")
Thanks!
Sammie

Fred Boer said:
Great! We'll get there! Open the query design grid. Add whatever fields
you
like from the table. Then, at the top of an empty column, enter something
like this:

ExpNoDash: Replace([tbldata].[txtData],"-","")

When you run the query, there should be a field called "ExpNoDash" with
the
data from txtData minus the dashes. "tblData" and "txtData" should be
replaced with the table and field name you are using for the unaltered
data,
of course.

Try it and let me know how it goes!

Fred

Hi Fred
Thank you for your kind response.
I am with you on the query thing, but I need help with the expression.
I
know how to write expressions, but I can't figure this one out.
Thanks
Sammie


:

Hi!

Well, let me try to do a better job of explaining! You are working
from
a
form, correct? And the form is based on a table? You could change
that,
and
base the form on a *query* of the table, not the table itself. The
query
could have an expression which would create a field in the resulting
recordset that has your "massaged" data. If you don't know how to
create
an
expression in a query, let me know...

So the form could have one field of the original data (if you want),
and
another field (generated by the expression in the query) which would
have
the data formatted as you like. Then just open the "Find and Replace"
dialogue (which is what your code does, if I am not mistaken...), and
then
do a find on the field with the modified data.

Please post back if that isn't helpful!

Cheers!
Fred




Fred,
I guess I need a little more help with that. Using a command
button
like
this:

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

How would I add your code?
Thanks
Sammie

:

Hi Sammie:

No, no... you can store the data as is, with dashes and so on.
Then,
when
you want to search, create a query expression that removes the
unwanted
characters, or do it in your code.

HTH
Fred


Fred,
Works great - just what I was looking for. The only problem is
that
I
need
the tracking number field printed on a report as typed (with
dashes
and
spaces), but I want it stored as text without the dashes &
spaces
for
searching purposes. How can I do that? Do I need to create a
second
field
to store the new text value?
Thanks
Sammie

:

Dear Sammie:

You could use code to replace the dashes and spaces when you
work
with
the
field, for example.


Private Sub txtData_AfterUpdate()
Me.txtData = Replace(Me.txtData, "-", "") 'Replace dashes
Me.txtData = Replace(Me.txtData, " ", "") 'Replace spaces...
End Sub

Note: IIRC the Replace() function wasn't in Access 97...

HTH
Fred Boer


Is it possible to ignore the "-" (dash) character in a text
field
somehow?
I
want to be able to search any part of the field, but I
never
know
where
the
dash is placed, so it messes up my searches. I have the
same
problem
for
spaces in the text field, although it's less of a problem
than
the
dash.

I have a text field used to enter tracking numbers from
UPS,
FedEx,
DHL,
etc. Each carrier has its own number of characters and
formats.
We
enter
the data in the format it is given to us, sometimes with
spaces,
sometimes
with dashes, sometimes alpha, sometimes numeric. I would
like
Access
to
store the field contents without dashes, and if possible,
without
spaces
regardless of how it is typed. That way, I could type any
series
of
characters in my search box.

Is this even possible?
Thanks
Sammie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top