Queries that Check field formats

[mojers]

Hello there AccessForums people,

I am not quite a newbie, have used Access a lot but mostly self tought so possible not the best!
Anyway, I am stuck on a query and need some help!

I need to be able to query (or perhaps macro) a code set used in my database, so I can capute human error in the typing of the codes.

The code format (per record) is = RRCCCABC.C.001


so 2 character combo, followed by a 3 character combo, followed by another 3 character combo, followed by a ".C.001".

I want to make sure that we have 8 characters, followed by a ".C.001"
I am not fussed at the moment what the characters are, just that they are 8 followed by the other bits.

can anyone advice how I can create a query that will check for the field length of characters?

thanks very much in advance!
Mojers

[maximus]

I have prepared a calculative Field in the query with the following conditions:
Text is the Field with the Code Format

CheckEntry: IIf(IsNull([Text]),"Blank",IIf(Len([Text])<>14,"Wrong_Entry",IIf(Right([Text],6)<>".C.001","Wrong Entry","Right_Entry")))

1) if the Text field is Blank. Blank will be displayed
2) if the Total length is <> 14 Wrong_Entry will be displayed.
3) if the last 6 Characters are <> .C.001 then Wrong_Entry will be displayed.

[maximus]

is you problem solved if yes mark the thread solved.

[mojers]

hey there maximus,

This is perfect! thanks so much for your help!

Mojers