How do I display Yes/No values based on the format of another (calculated) value

[Barry Heap]

I have a select query with several fields. A calculated field extracts part of the value of one of these fields. I want to retain the extracted value but include a further calculated field that displays Yes or No if the value is in the format LL000000L.

I have tried using the LL000000L in an expression. I have also tried the IiF, AND and RIGHT/MID functions to check all the constituent parts of the value are in the correct format but get stuck on the initial part which could be "AA","AB" etc etc (limited to 255 characters in the expression). The syntax may be incorrect?

I have little knowledge of writing SQL but can understand some, versions are MS Access 2000 v9, Windows XP Prof 2008

[pod]

the SQL statement, calculated field, and a bit more details might help (me) to understand a what you are trying to achieve

[Barry Heap]

Here is the SQL statement from View SQL. I have noted the irrelevant fields as Field 1, 2 etc. The calculated field that extracts the part of the value (from the Customer reference field) is marked EXTRACT. There are then 3 separate attempts at a calculated field to check the EXTRACT value is in the LL000000L format (the second try assumes the initial letters are AA or NB, the whole expression actually works but only confirms when that criteris is met - to include all possible values i need to allow for AA-ZZ).

SELECT Dec.[Field 1], Dec.[Field 2], Dec.[Field 3], Dec.[Field 4], Dec.[Customer Reference],

Right([Customer Reference],9) AS EXTRACT,

IIf([EXTRACT].[Format]=[LL000000L],"Yes","No") AS [EXTRACT FORMAT CHECK 2],

IIf(Left([EXTRACT],2) In ("AA","NB") And Mid([EXTRACT],3,6)<999999 And Right([EXTRACT],1) In ("A","B","C")," Yes","No") AS [EXTRACT FORMAT CHECK],

IIf(Left([EXTRACT],1) In ("A","B","C","D ","E","F","G"," H","I","J","K", "L","M","N","O" ,"P","Q","R","S ","T","U","V"," W","X","Y"," Z") And Mid([EXTRACT],3,6)<999999 And Right([EXTRACT],1) In ("A","B","C")," Yes","No") AS [EXTRACT FORMAT CHECK3]
FROM [Dec];

[pod]

The only thing I can suggest is, starts small, i.e. modify your query to its most simple form until it works then add one small change at a time, this way you will be able to find the trouble spot.

Sorry I cannot be more helpful, wish you luck

[OldBirdman]

I would write a function to return a boolean value. I would put this in my query as:

SELECT Dec.[Field 1], Dec.[Field 2], ...., 
   blnIsFormatOK(Dec.[Customer Reference]) AS FmtOK, 
   FROM Dec;

The function needs to be Public and NOT in a class module. As a simple example:

Public Function blnIsFormat(strIn As String) As Boolean
'There are many ways to write this procedure, using loops and/or Select Case
'This is a 'brute force' method for illustration purposes

    blnIsFormat = True 'Assume Format is correct
    
    If Mid(strIn, 1, 1) < "A" Or Mid(strIn, 1, 1) > "Z" Then
        blnIsFormat = False 'Format is NOT correct
    End If
'By testing ASC(Mid(strIn, 1, 1)) the upper/lower case could be verified
    ' ......
    
    If Mid(strIn, 3, 1) < "0" Or Mid(strIn, 3, 1) > "9" Then
        blnIsFormat = False 'Format is NOT correct
    End If
    ' ......
'Further testing could verify the patterns of alpha or numeric
'   to be correct also
End Function 'blnIsFormat

Currently there are no tests for Null, Zero-length, Too long strings, or other expected abnormalities. If the function returned a string, a description of what were wrong could be used, instead of a simple "Yes"/"No"

[Barry Heap]

I was lost at write a function. Perhaps i should have explained that the query was done using the QBE grid. Whilst i understand roughly what the above code does I would struggle writing it.

Guess I may have to see if there are any solutions that can be applied to the QBE grid query. If not I may have a go at it if time allows. Thank you.

[OldBirdman]

First of all, various sources say the maximum string length is 1,024, 4,096, or 65K+. This should be OK with 1K.
pod's idea that you start slowly and build is a good one. I would start with the first character, which must be between "A" and "Z". I would then have:

[EXTRACT FORMAT CHECK 2]: IIF(Mid(Dec.[Customer Reference],1,1)>="A" And Mid(Dec.[Customer Reference],1,1) <="Z", "Yes","No")

in the design grid for the query. I would run this from the design grid until I were satisfied with it.
Next, add the 2nd check, Mid(Dec.[...],2,1) which will add 2 more 'And' clauses to the above grid element. You would have:

[EXTRACT FORMAT CHECK 2]: IIF(Mid(Dec.[Customer Reference],1,1)>="A" And Mid(Dec.[Customer Reference],1,1) <="Z" And Mid(Dec.[Customer Reference],2,1)>="A" And Mid(Dec.[Customer Reference],2,1) <="Z", "Yes","No")

When this works, add another 'And' for the numeric, which must be >= 0 And <=999999 unless there is an embedded alpha. Proceed in this manner until you have included all your tests within the Iif function.
Another, slightly shorter and probably faster syntax would be:

[EXTRACT FORMAT CHECK 2]: IIF(Mid(Dec.[Customer Reference],1,1) Between "A" And "Z", "Yes","No")

[Barry Heap]

Thanks to the comments posted the following formula was constructed and appears to work fine:

(Format) Check: IIf(Left([ref],1) Between "A" And "Z" And Mid([ref],2,1) Between "A" And "Z" And Mid([ref],3,6) Between "000000" And "999999" And Right([ref],1) Between "A" And "Z","OK","INCOR RECT")

It checks that the data is in the LL000000L format. All characters are required to be in their respective places, if any letters are entered as numbers (or vice versa) or missed out completely it reports "incorrect" .

The letter checks may need to be refined to reflect the true range.

Again many thanks.