det indtastede udtryk er for komplekst

Der har tidligere været et maksimum på 'nestede' IIF sætninger i samme formel/kriterie

Jeg ved ikke om det er ændret i Office 2007.

Di er nok nødt til at oprette den som en VBA finction og kalde denne i dine forespørgsler

AS jensen363 says you will need to make a function (code) to replace the 22 IIF.

Hej Jensen

jeg er desværre ret meget nybegynder i access, så hvordan laves en VBA function og hvordan kaldes den?

If jensen dosnt have time then you can send your dB to me and I'll do it for you.
ekspertenATsanthell.dk
AT = @

Hej Terry

jeg sender en mail

Have you received my mail?

Sorry ... have just seen the mail/comments now

I'm sure that Terry provided the right answer :-)

Hej

Har ikke hørt fra Terry endnu - så kom frisk hvis I kender en mulig løsning på mit problem!

I sent you a mail on Friday. Anyway I've just sent it again, please post a comment if you dont receive it soon.

Heres the result.

Make a PUBLIC function.

Public Function MyUser(Agent_ID As String) As Integer

    Select Case Agent_ID
   
        Case "Admin"
            MyUser = 0
        Case "DK1"
            MyUser = 2
        Case "AU-2006"
            MyUser = 3
        Case "GR-2007"
            MyUser = 4
        Case "PT-2025"
            MyUser = 5
        Case "BE-1953"
            MyUser = 6
        Case "ES-2000"
            MyUser = 7
        Case "IE-2031"
            MyUser = 8
        Case "FI-1055"
            MyUser = 9
        Case "UK-2065"
            MyUser = 10
        Case "US-1881"
            MyUser = 11
        Case "SE-1963"
            MyUser = 12
        Case "DE-1438"
            MyUser = 13
        Case "NL-1836"
            MyUser = 14
        Case "DK-HQ-norden-8000"
            MyUser = 15
        Case "DK-HQ-retail-8001"
            MyUser = 16
        Case "IR-2035"
            MyUser = 17
        Case "FR-2036"
            MyUser = 18
        Case "PL-2041"
            MyUser = 19
        Case "SY-2215"
            MyUser = 20
        Case "ASIEN-2046"
            MyUser = 21
        Case "TRAVEL-retail-2083"
            MyUser = 22
           
        Case Else
            MyUser = 1
   
    End Select



End Function


In the query use:

User: MyUser([Agent_ID])

hi Terry

I dont have get any mail yet

I send you a PM now

Hi Terry

I have tried you script, but I get this messages:
There is  a not-defined function "MyUser" in the expression ;-(

Have you received the dB I sent you ?

Terry -

No mail/DB received yet..........

My mailbox is OK, I receive mail from ohter people - I dont get it

Terry -

I sent you another PM

I've sent it to your other email, hope you receive it.

Hi Terry

I got yours mail - and I seems to work perfect local. But when I uploaded the DB, I cant connect to it through a *.asp document. When I try that I get this error:
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'MyUser' in expression

I hope you now what to do! Because I dont

Sorry I wasnt aware that the query was going to be used through ASP.
I havent worked so much with ASP so I'm just guessing here.
Before the SQL gets sent to the Access dB it gets check to make sure the syntax is correct and because it doent know that the function exists the it fails.

I think the solution now is to alter the design of your dB. And as I mentined in a mail, if you received it, this will be the correct way to do it.

Here is what I wrote:

"I would suggest that you put all of the Agents details into a separate table and then include the table in the query or even a Dlookup to get the agent number from the table. Even better replace the text name in the main table with the actual number and the related table contains the text. This in my opinion is the correct way to do  it, that's if I have understood your dB correctly :o)"

Hi Terry

have you sent a DB in the mail? because I have not received any mails from you. maby you can sent it to the ohter email adr. I give yesterday

I'm a bit puzzled now.
in your comment tir. d. 08. september 2009 kl. 12:08:41

you say it works local but not whan using ASP. The dB I sent was the one you have working.

I only suggested you alter your database design so that the Agents information is in another table. I didn tmake an example as I'm not 100% sure that its what you want.

whats the status now Johan?

I have google this quistion a lot, and I dont think it possibly.
So now I try to use yours script and push the information to another DB and connect to the DB from *.asp.

Thanks a lot for yours help

best regards
maddog_em

Thanks
I still suggest you put the agents information in another table.

If you need help with this then drop me an email.

Thanks agian for you help :-)