Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Left function in queries (Access 2K)

    I am trying to run a query to find the first two letters in a field. In the query grid I'm typing in:

    WrkStation: Left$([Master].[Workstation],2)

    I swear I've done this before and it would just return a column with the first two letters of the Workstation field, for which I can do a criteria against. It is giving me an error:

    Function is not available in expressions in query expression [then it gives the Left$(. . . expression here]

    Can anyone see what I'm doing wrong?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    I did a quick test of your formula and it worked fine.

    Questions:

    Is the data type in [Workstation] text?

    Is [Master].[Workstation] a table & field in your query builder?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    Hi Rupert, thanks for your reply.

    Yes, to all your questions. It is text and it is table and field.

    If it worked on yours, I might have a missing reference or something.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    Before I lead you astray, I based my test on an existing contacts table, made a query in design view, and pulled Field

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    No its not that!

    Both:

    WrkStation: Left$([tbl_Acc_Cust].[TEL],2)

    And:

    WrkStation: Left$([tbl_Acc_Cust]![TEL],2)

    Work fine!!!!!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    You're right, neither worked on mine. In code I tried using a LEFT$( . . . statement and it worked fine, which tells me that the references are okay. Why it isn't working, I have no idea.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    Have you done a test using another table in your db? That might throw some light on it.

    If it don

  8. #8
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    I've seen situations wherein the actual .mdb file became corrupt and copying everything lock, stock, and barrel into a new .mdb fixed the problem. Something to consider anyway.

    jeff

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Left function in queries (Access 2K)

    Thanks, your last suggestion made me try opening up the database it was linked to, which was a lower version of Access. That was probably the issue. It worked fine on the back end.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: User Left function in queries (Access 2K)

    That's something you probably want to fix as soon as you can. Front-ends attached to Access 97 or 95 (OR 2?) get pretty sluggish, and you may encounter data corruption if you use memo fields extensively.
    Wendell

Posting Permissions

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