Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <img src=/S/shocked.gif border=0 alt=shocked width=15 height=15> , 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 <table1>.[field1] left([field1],2) = IA Then
    "This Text"
    Else If <table1>.[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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

    <table border=1><td align=center>Code</td><td align=center>Text</td><td>IA</td><td>This Text</td><td>SY</td><td>That Text</td><td>...</td><td>...</td></table>
    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.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank You Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •