Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    West Chester, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating Column during Query (Access 2000)

    I am still trying to figure out how to use this thing called "Access" and I have been running into a problem which I can't seem to get right. Once I write the formula Mydate:format([MyDate],"yyyy-mm-dd") I want to either update the Mydate column with the new information or create a new column. I have tried everything I can think of but nothing seems to work for me. Any help would be greatly appreciated. Thanks!

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

    Re: Creating Column during Query (Access 2000)

    You can't give the name of the field to the expression.
    Use something like :
    MyFormatedDate:format([MyDate],"yyyy-mm-dd")
    Francois

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Location
    West Chester, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Column during Query (Access 2000)

    Actually this is what I currently have and I can't seem to get the new information in a new column. I hope the attachment works.

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

    Re: Creating Column during Query (Access 2000)

    So what is the problem you have with your query? When you switch to datasheet view, you should see two columns: BIRTH_DATE and dob. If not, what do you see?

  5. #5
    New Lounger
    Join Date
    Dec 2004
    Location
    West Chester, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Column during Query (Access 2000)

    That is what I see in the Query Datasheet view (BIRTH_DATE and DOB) but I am trying to update the table (sheet 1) with the new information. I would rather replace the BIRTH_DATE information with the text version from the DOB column in the query without having to add a new column in the table. I know it's possible I just can't seem to get it there!

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Location
    West Chester, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Column during Query (Access 2000)

    The reason behind me changing the date to a Text is because I have to export this information to Excel then import to a different database. Trust me I didn't develop it that way and I am unable to change the procedure. All the data has to be exported into Excel as a text value so it can be imported into another database and that is the reason behind my issue. Like I said I am new to this (and what I am working with really doesn't help me learn Access) so I am still trying to figure Access out. I am sure there is an easier way to do what I have to do but unfortunately too many people use this specific database so I really can't be changing it but I would love too! Once again I appreciate the help thanks!

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

    Re: Creating Column during Query (Access 2000)

    You still don't have to change the table. You can export the query directly.
    Francois

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

    Re: Creating Column during Query (Access 2000)

    In the first place, I don't understand why you would want to do this, and in the second place, you can't just replace a date/time value (which is stored as a number) with a text value. If you really want to store the text value in the table (you shouldn't, but OK), you must create a new text field in the table, say DOB. Next, create a query based on the table, and add only the DOB field to the query grid. Select Query / Update Query. In the Update to line, enter the expression

    Format([BIRTH_DATE],"yyyy-mm-dd")

    Execute the update query by selecting Query / Run or by clicking the Run button on the toolbar.

Posting Permissions

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