Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: Replace Function

    It's been quite awhile since I've played with Access and I'm having trouble using the Replace function. The first code snippet below is what I'm using as a control source for a textbox. For fields that have FDBK_Closed = NULL, I'm getting #Error. When I return to look at my control source it's being changed to the second code snippet below. It's almost like Access doesn't recognize the Replace function. Is this function still available in A2003?

    txtTag is the name of the textbox that is bound to the Tag Field.

    Code:
     
    =IIf(IsNull([FDBK_Closed]),([SimSysID] & ":bkr_" & replace([TAG],"-","_"),[FDBK_Closed])
    Code:
     
    =IIf(IsNull([FDBK_Closed]),([SimSysID] & ":bkr_" & [replace(txtTAG],"-","_"),[FDBK_Closed])

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes I'm pretty sure the replace function is still available. I think your syntax is a little off though as Access usually only puts [] around items it interprets as a field. (Once you hit enter, if the syntax is off you'll notice the []).

    You can also possibly utilize the instr function but replace is probably better in what you're trying to do.
    Last edited by pkstormy; 10-31-07 at 14:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Without actually trying it...

    =IIf(IsNull([FDBK_Closed]),([SimSysID] & ":bkr_" & replace([TAG],"-","_"),[FDBK_Closed])

    could maybe be...

    =IIf(IsNull([FDBK_Closed]),([SimSysID] & ":bkr_" & replace([TAG],"-","_")),[FDBK_Closed])

    but again, I didn't test this. Look for it to capitolize the r in replace after you hit enter if it recognizes replace as a function. (I think it should do this.)
    Last edited by pkstormy; 10-31-07 at 14:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2005
    Posts
    333
    UGH..All of that because I didn't have matching parentheses.

    Thanks

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The joy of syntax. Can't count the times I've spent hours trying to figure out a simple ) or ( missing or in the wrong place.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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