Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Red face Unanswered: Some help with a query

    I need some help with a query...

    I have a module that is used by queries in my app as follows;

    It works perfectly well and basically returns a string based on a job
    code:>

    #####################################
    Public Function CheckDivision(Code as string) as string

    If Not IsNull(CODE) Then

    Select Case Left$(code, 1)

    Case "9", "2"
    CheckDivision = "SYS"

    Case "B"
    CheckDivision = "SYS2"

    Case "E"
    CheckDivision = "ELE"

    Case "F"
    CheckDivision = "FURN"

    Case "G"
    CheckDivision = "GRAPH"

    Case "X"
    CheckDivision = "ONE"

    Case "Y"
    CheckDivision = "YNU"

    Case Else
    CheckDivision = CODE & "9 ERROR - DIVISION UNKNOWN!"

    End Select

    Else
    CheckDivision = ""
    End If

    End Function
    ##########################################

    i.e. it evaluates the start of the job code to determine which
    "Division" has created the job.

    ....Heres the but...(Query also returns other job information based on fields stored in various tables)

    I have another 3rd party application which needs to interrogate the query but it fails to recognise ANY field names if I use the above module to create a field in the query i.e. Division: CheckDivision([code])

    Although the query works fine within Access, my third party app cant see the data if my Division: CheckDivision([code]) field is part of it.

    Once I remove this field, the third party app can see everything else in the query Fields / Data etc

    Therefore, I need another method to get the "Division" into the query without using a module (Im sure this is what is causing the problem)

    Can anyone put me in the right direction?

    Can nested Iif statements do something similer - Ive not really used Iif before - as I understand it , it evaluates and gives 2 results based on true / false - can you nest these statements?

    Or can anyone suggest another / better idea???

    Many thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Thumbs up Got the idea...

    Div: IIf([JobNumber] Between "X" And "Y","One Offs",IIf([jobnumber] Between "Y" And "Z","Y Numbers",IIf([jobnumber] Between "F" And "G","Furniture")))


    ....On the right track. The above is working, just got to code the other divisions - What a pain in the butt.


    GD

Posting Permissions

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