Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting Dates into Strings (2000)

    I feel like this should be so easy and I'm just missing something...

    How do I convert dates into strings? I have a table that has a date data type in it that I need to convert into a text data type so that I can create a bar graph report. I cannot figure it out for the life of me!

    Thanks,
    Kindra

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Dates into Strings (2000)

    Kindra,

    You can somthing like this in a query

    StrDate: Year([YourDateField]) & "/" & Month([YourDateField]) & "/" & Day([YourDateField])

    this will give you yyyy/mm/dd format
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Converting Dates into Strings (2000)

    An even simpler method is Format([MyDate], "mm/dd/yyyy"), which will produce a string formatted as a US date like 11/11/2004.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Dates into Strings (2000)

    Thanks for all of the responses! I used this one Charlotte and worked great.

    Unfortunately I'm still getting the same error when I try to create my chart. I was really hoping that creating a text field out of the date field would solve it!

    The error I'm getting is:

    "The Microsoft Jet database engine does not recognize '[forms]![frmReports]![cboFY]' as a valid field name or expression." ([forms]![frmReports]![cboFY] being a parameter I'm passing from a form to my query)

    When I click OK I get this error:
    "An error occurred while sending data to the OLE server (the application used to create the object)."

    Do I need to create a reference?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Converting Dates into Strings (2000)

    Why don't you post the SQL for the query so we can see exactly what might be missing or incorrect? If you are referring to a form control in the query, you may need to add the fully referenced form control name to the query parameters as well, not just to the critiera. Also, what does that form control have to do with the date you were trying to format?
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Dates into Strings (2000)

    Adding the form control as a parameter worked! Now I don't get an error. I had wanted to convert the date field to text to see if that would work, as I want the dates to be on the x-axis.

    One more question: in the wizard, I want the value field to sum, but it defaults to count and I can't change it through the wizard. Is there some way that I can change it manually?

    Here's my SQL (not sure if that helps):
    PARAMETERS [forms]![frmReports]![cboFY] Text ( 255 );
    SELECT DEPT.DEPT_NAME, Format([WEEK_ENDING],"mm/dd/yyyy") AS [DATE], TIMECARD_DATA.HOURS
    FROM DEPT INNER JOIN TIMECARD_DATA ON DEPT.DEPT_ID = TIMECARD_DATA.DEPT
    WHERE (((TIMECARD_DATA.FISCAL_YEAR)=[forms]![frmReports]![cboFY]))
    GROUP BY DEPT.DEPT_NAME, Format([WEEK_ENDING],"mm/dd/yyyy"), TIMECARD_DATA.HOURS;

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Converting Dates into Strings (2000)

    What wizard are you referring to? There is no count in your SQL.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Dates into Strings (2000)

    I was referring to the chart report wizard... but I did add a "sum" to my SQL and the chart works great now.

    Thank you so much for all of your help!

Posting Permissions

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