Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help With String Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    I have tried both the following without success in a append query:

    Expr7: Left(Replacestr(Replacestr([Last]," ","",1),",","",1),4)

    Expr7: Left(Replacestr(Replacestr(nz([Last],"NONE")," ","",1),",","",1),4)

    Error Message: 1 record due to voilation rule validations

    They both work OK as long as Last has a value

    If Last is empty I want to return

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With String Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Why do you write a function when this function exist in Access : Replace. It deals with Null strings
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With String Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Try this:
    IIF(len(last)>0,Left(Replacestr(Replacestr([Last]," ","",1),",","",1),4),"NONE")
    I test it with a variable but not with fields
    Francois

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With String Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Francois

    After further review there was a one charaacter space in Last field.

    After space removal both the following worked:

    1. PayerNameControl: IIf(Len([last])>0,Left(Replacestr(Replacestr([Last]," ","",1),",","",1),4),"NONE")

    2. PayerNameControl: Left(Replacestr(Replacestr(nz([Last],"NONE")," ","",1),",","",1),4)

    I don't remember how I got started with Replacestr.

    I tried your initial suggestion with Replace:

    Expr7: Left(Replace(Replace([Last]," ","",,,vbTextCompare),",","",,, vbTextCompare),4)

    And getting following message within query grid:

    The expression you entered contains invalid syntax
    You may have entered a comma without a proceding value or indetifier.

    Any suggestions?

    Thanks, John

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

    Re: Need Help With String Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Unfortunately, the Replace function, which was new in Access 2000, is only supported in VBA, not in expressions in queries, forms or reports. See ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions.

    Access 2002 does support these functions in expressions

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With String Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Hans

    Thank you for reminding why I'm using Replacestr.

    John

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With String Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    But using the replace function in your function (I think) would be faster.
    Francois

Posting Permissions

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