Text Fields in a Parameter Query

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

Guest

I imported a database from Excel. Have a field in a table called Bday. It's a text field (length 5). I recently added an input mask of aa/aa. I've got a parameter query to pull the birthdays during a certain time period. The birthdays that were imported without the mask require the '/' in the parameter request. Those that have been entered since don't. Any way around this?

Thanks in advance.
 
Change the InputMask to *store* the masking character with the Field values.
You will need the "/" consistently in the Parameter value.

You *may* need to do an Update Query to insert the "/" to existing values
that don't have the "/".

--
HTH
Van T. Dinh
MVP (Access)


Dumb Blonde said:
I imported a database from Excel. Have a field in a table called Bday.
Click to expand...
It's a text field (length 5). I recently added an input mask of aa/aa.
I've got a parameter query to pull the birthdays during a certain time
period. The birthdays that were imported without the mask require the '/'
in the parameter request. Those that have been entered since don't. Any
way around this?
 
What's the best way to insert the "/" to the existing values? I can get to the udpate query but am unsure what to put in the update field.
 
One question ---- How?

How do I "store" the masking character and what should I put in the update field (on the update query) so that it will retain the current values but put a slash between MM and DD?
 
The SQL String should be something like:

UPDATE [YourTable]
SET [YourField] = Left([YourField], 2) & "/" & Right([YourField], 2)
WHERE Len([YourField]) = 4

--
HTH
Van T. Dinh
MVP (Access)




Dumb Blonde said:
I've added a ;0 to the end of my input mask and I've added a format of
Click to expand...
"aa/aa" and am still not getting anything to come up. Anything else I need
to do to ensure that I'm storing the slash?
 
Thank you thank you thank you! I'll try this.

Van T. Dinh said:
The SQL String should be something like:

UPDATE [YourTable]
SET [YourField] = Left([YourField], 2) & "/" & Right([YourField], 2)
WHERE Len([YourField]) = 4

--
HTH
Van T. Dinh
MVP (Access)




Dumb Blonde said:
I've added a ;0 to the end of my input mask and I've added a format of
Click to expand...
"aa/aa" and am still not getting anything to come up. Anything else I need
to do to ensure that I'm storing the slash?
Still haven't figured out what to put in the update field for the update query.
Click to expand...
Click to expand...
 
Worked like a charm! Now I know how to use the left/right command.

Van T. Dinh said:
The SQL String should be something like:

UPDATE [YourTable]
SET [YourField] = Left([YourField], 2) & "/" & Right([YourField], 2)
WHERE Len([YourField]) = 4

--
HTH
Van T. Dinh
MVP (Access)




Dumb Blonde said:
I've added a ;0 to the end of my input mask and I've added a format of
Click to expand...
"aa/aa" and am still not getting anything to come up. Anything else I need
to do to ensure that I'm storing the slash?
Still haven't figured out what to put in the update field for the update query.
Click to expand...
Click to expand...
 
Back
Top