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
