Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query query (2003 SP1)

    I've got data on an AS400 server with some user user e-mail addresses. I was hoping to strip off the @******.com & use the result as a user name.

    All went well to start with, but when I noticed one record with an address for an individual, when the 'user' is a department, I thought an Iif would sort it out.

    NO.

    Attached shows the SQL for each query version & the results.

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

    Re: Query query (2003 SP1)

    I don't see an attachment?

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query (2003 SP1)

    Oops, I didn't re-attach after preview.
    Doh!

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

    Re: Query query (2003 SP1)

    The result as shown in the second screenshot doesn't make sense. I'd guess it's a temporary glitch. Try quitting and restarting Access, and see if it works better then.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query (2003 SP1)

    I actually started this on Friday. Since then PC has been restarted, but the result is still the same as on the attachment.

    Baffled.

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

    Re: Query query (2003 SP1)

    I hate to ask this, but are you absolutely sure that the 2nd screenshot is the result of the SQL immediately above it? Please double-check.

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query (2003 SP1)

    Dead cert m8. The thing had me (& my buddy) going for about an hour on Friday.

    I don't know if it has any bearing, but the source table is an AS400 one.

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

    Re: Query query (2003 SP1)

    I'm stumped then. The result shown in the table is impossible as outcome of the query.

    What happens if you import (part of) the AS400 table into Access and then run the query against the imported table?

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query (2003 SP1)

    Same result.

    Here's a really basic version.

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

    Re: Query query (2003 SP1)

    There are oodles of spaces after the e-mail addresses. If they were there before, the original query cannot have worked correctly either - it should also have kept the @... and lots of spaces. The following expression for Name should do what you want:

    IIf([HRUSR]="BANSHIP","Transport",Left(Trim([HREML]),Len(Trim([HREML]))-10))

  11. #11
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query (2003 SP1)

    Thanks Hans.

    BTW, have a go at the 1st query. It worked for me, that's one reason I was so stuck with the 2nd.

    Cheers

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

    Re: Query query (2003 SP1)

    The screenshot below shows the result of the first query with the table name changed to tMPnAME.
    As you can see, it also fails to remove the @... part, because of the trailing spaces (the vertical bar in the first record is the blinking insertion point, also showing that there are spaces after the e-mail address).

    So if the first query originally returned correct results, the trailing spaces must have been added sometime between running the first query and running the second query.

  13. #13
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query (2003 SP1)

    Right, got it. When the AS400 table is queried directly, the 1st query works. Not sure why, but it's kinda accademic now; 'Hans the man' has done his magic [img]/forums/images/smilies/smile.gif[/img]

    Thank you.

Posting Permissions

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