Using code in a query (Win 2K, Access 2K)

Topic

New Reply

Dear All

I am trying to use the iif in a query to convert the first two characters from a field into a predefined text value, I can actually make this work , but need more then the allowed number of iifs, it seems to me that it would be easier to use an If …….. Then statement (or Case Else, but I’m more comfortable with if then), but I’m unsure about how to get this to run from the query, or even if it’s possible at all.

What I want to achieve is something along the lines of: –

If .[field1] left([field1],2) = IA Then
“This Text”
Else If .[field1] left([field1] = SY Then
“That Text”
etc

All told I have over 20 different prefixes to deal with.

So, is it possible? If so, where do I put the code, how do I make the code run from the query?

Thanks in advance

Ian

Reply | Quote

Replies

One option is to use a VBA function in a standard module (the type of module you create by clicking New in the modules section of the database window, or selecting Insert | Module in the Visual Basic Editor).

Public Function MyFunc(varText)
If IsNull(varText) Then
MyFunc = Null
Exit Sub
End If
Select Case Left(varText, 2)
Case “IA”
MyFunc = “This Text”
Case “SY”
MyFunc = “That Text”
…
…
Case Else
MyFunc = “unknown”
End Select
End Sub

You can then create a calculated column in your query:

NewText: MyFunc([Field1])

Note: give your function a meaningful name; MyFunc is just for illustration purposes.

Another, much more flexible option is to create a translation table tblLookup

Code	Text
IA	This Text
SY	That Text
…	…

First create a query that has a calculated column in addition to the fields you need.

Code: Left([Field1],2)

Save this query, then create a new query based on tblLookup and this query, joined on Code. You can add the Text field to the query grid in addition to the fields you need.

Reply | Quote

Thank You Hans

Reply | Quote