Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Simple query question

    Heya,

    Suppose I wanted to run an update query that would remove commas from a field?

    I'm not savvy enough to pull it off!

    Some fields have commas like this:

    ,,DATA,
    ,DATA
    DATA,

    If this was a radio show, I'd say that the third caller with the right answer wins a prize! but there's not really a prize. just my thanks. and my firstborn, should you demand it.

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Simple query question

    Originally posted by jimmyswinger
    Heya,

    Suppose I wanted to run an update query that would remove commas from a field?

    I'm not savvy enough to pull it off!

    Some fields have commas like this:

    ,,DATA,
    ,DATA
    DATA,

    If this was a radio show, I'd say that the third caller with the right answer wins a prize! but there's not really a prize. just my thanks. and my firstborn, should you demand it.
    Hi Jimmy...

    Here's a little function I wrote for you to remove the commas from the strings...

    Function StripCommas(strValue As String) As String
    Dim i As Integer

    For i = 1 To Len(strValue)
    If Mid(strValue, i, 1) <> "," Then
    StripCommas = StripCommas + Mid(strValue, i, 1)
    End If
    Next i

    End Function



    Paste that into a class module.... and then in your query use the function like this...

    fldNew: StripCommas(fldName)

    (of course, you'll have to change the field names to the ones you are using... )

    HTH

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Talking

    You are so great, Trudi! Thank you!

    Thank you. I just can't figure out how to implement it in the query!

    I attempted to put fldNew: StripCommas([FIELDTYPE]) in the "FIELD" field on the query to no avail. I tried changing the "update to" field but still nogo.

    My DB has three fields, FIELDNAME, FIELDTYPE, FIELDTYPE2.

    I am trying to strip commas from both fieldtype fields, which I can do one at a time...

    Can you help me figure out how to config the query?

    Many thanks!

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    With that function in a module and PUBLIC ... In the querybuilder on the field column use the Expressionbuilder to expose the stripcomma function ...

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    I will play with it tomorrow and give the scoop. I'm still not quite getting it, yet!

    This'll be one of those *DOH* things, methinks!

    Thanks for all your help, fellow DB'rs

Posting Permissions

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