Formatting Fields in a Query

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Is there anway to specify a format for a field in a
query. For example, I have a field that contains car
registrations like:

SB03UKA
K193TBD

I want to filter the data (either through the criteria on
the field or using an IIF statement on a calculated
field) so that the data is in one of the two formats
above, and anything else is removed as an error. I have
been trying to use the wildcards, but Access seems not to
be recognising the format. I have been trying to filter
it by stating in the criteria that the field must be
equal to "??##???" OR "?###???", but this is not
working. Can anyone help?

Thanks in advance!!
 
try in query criteria to find K193TBD:
Like "[A-Z]" & "[0-9]" & "[0-9]" & "[0-9]" & "[A-Z]"
& "[A-Z]" & "[A-Z]" or Like (you see the pattern here).
If the pattern doesn't matter after a certain character
position, then you can end the criteria string with &"*"

Problem is your "digits" are actually text in the
examples, so you have to test for character range or digit
range in each position. See discussion under wildcards in
string comparisons in Help
HTH
 
Tom said:
Is there anway to specify a format for a field in a
query. For example, I have a field that contains car
registrations like:

SB03UKA
K193TBD

I want to filter the data (either through the criteria on
the field or using an IIF statement on a calculated
field) so that the data is in one of the two formats
above, and anything else is removed as an error. I have
been trying to use the wildcards, but Access seems not to
be recognising the format. I have been trying to filter
it by stating in the criteria that the field must be
equal to "??##???" OR "?###???", but this is not
working. Can anyone help?
Click to expand...
Hi Tom,

You did not provide the query text you have
been trying that did not work, but one guess
would be that you were not using the LIKE
operator.

SELECT * FROM yourtable
WHERE
([CarReg] LIKE '??##???')
OR
([CarReg] LIKE '?###??');

In the query design grid, the column
for your "CarReg" field might look like:

Field: CarReg
Table: yourtable
Sort:
Show:
Criteria: LIKE '??##???'
or: LIKE '??###??'

If this does not work, then is there
a chance your field length is not
actually 7?

Criteria: LIKE '*' & '??##???' & '*'
or: LIKE '*' & '??###??' & '*'

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Back
Top