I have a column with numbers in the

[dodgyone]

I have a column with numbers in the following format:

B000000000
C000000000
D000000000

etc etc

What I would like to do is to query this column to see whether the record starts with a particular letter and if so fill in the blank column available with a value.

So if the field starts with: A, H, J or K then the value in the spare column is "EAS"

Or if the field starts with: B, C, D, E, F, or G then the value in the spare column is "WES"

I've tried adding the module to the query and used the following code but I'm getting nowhere...

If [ReferenceField] Like "A*" Or "H*" Or "J*" Or "K*" Then
[SpareField] = EAS
ElseIf firstletter Like "B*" Or "C*" Or "D*" Or "E*" Or "F*" Or "G*" Then
[SpareField] = WES
End If


Does anyone have any suggestions please... thanks...

 

[MichaelRed]

SpareField: basLikeLtr([ReferenceField])

Public Function basLikeLtr(RefField As String) As String

    Select Case RefField
        Case Is = "A*" Or "H*" Or "J*" Or "K*"
            basLikeLtr = "EAS"
        Case Is = "B*" Or "C*" Or "D*" Or "E*" Or "F*" Or "G*"
            basLikeLtr = "WES"
    End Select

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dodgyone]

I've copied what you've done but I get a type mismatch when I try to run it...

Thanks for your help...

 

[Guest_imported]

Try this:

Select Case Left([ReferenceField],1)
Case "A", "H", "J", "K"
[SpareField] = "EAS"
Case "B", "C", "D", "E", "F", "G"
[SpareField] = "WES"
Case Else
Exit Sub
End Select

 

[dodgyone]

Thanks for your help but I now get... external name not defined.

Do I have to add a dim?

thanks...

 

[Guest_imported]

Hmmm,

Try preceding the field name with the name of the table:

i.e. [tablename]![ReferenceField] and

[tablename]![SpareField]

 

[dodgyone]

I've tried that before and still the same result. I'm using Access 97 if it's any help...

 

[MichaelRed]

Where do you get the type mismatch error? Is it in the QUERY or the FUNCTION?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JaneC]

dodgyone,

You could do this with a simple update query, put hte following in the "update to" row for SPAREFIELD

IIf(InStr("AHJK",Left([REFERENCEFIELD],1))>0,"EAS","WES&quot