Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    7

    Unanswered: changing characters in a table using a query (Resolved)

    I have about 125 records with poorly inputed data, and I want to fix them.

    The field is a date field and sometimes the data entry person used a / or a - or even a .

    So i want to make a query that will find all instances of - in the bday field and change them to a /

    Any help would be appreciated.

    Thanks
    Last edited by DKasler; 01-26-05 at 12:20. Reason: Issue Resolved

  2. #2
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    What version of access are you using?
    Are you certain that the Data Type is set to Date?
    In my Access XP I can not use different formats in a date field even if I try.
    My guess (Guess) is that the Data Type is set to text. if the only information in the field in question is a date, regardless of formatting, in the Design view if the table make the Data Type Date and choose a format and Access <b>SHOULD</b> change all the records to the same format.

    No promises but I did this on my pc and it worked.
    Darasen

  3. #3
    Join Date
    Dec 2004
    Posts
    7
    Access 2003

    You are correct. The field is a text field, but I had to do that to prevent other issues in Visual basic.

    Ill try converting it to a date field and see what happens.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    How about this

    Code:
    UPDATE tblDate SET tblDate.fldDate = Replace([fldDate],"-","/");
    with table being tbldate and the field = flddate

    HTH
    Dave

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Darasen but this may not work as . is not a proper date seperator (at least in access 2002) and these rows will either be blanked or deleted better to force the users to input the dates properly in the first place,
    at least thats my understanding of it

  6. #6
    Join Date
    Dec 2004
    Posts
    7
    Quote Originally Posted by DavidCoutts
    How about this

    Code:
    UPDATE tblDate SET tblDate.fldDate = Replace([fldDate],"-","/");
    with table being tbldate and the field = flddate

    HTH
    Dave

    Worked like a charm! Thanks much.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Changing - to /

    The easiest way to do this is to simply open the table. Highlight the column. Then do the Edit - Replace. Enter - for the Find. Enter / for the replace. And select "Any Part of Field" for Match.

    You can also run an update query as suggested which does the same thing.

Posting Permissions

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