Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reverse field (2000)

    I have a field in a query called "ReportDate". It is defined as text. The data looks like this - 200407. I would like to create another column that would define this field as a date with a format of 07/2004. Would this be possible? Thanks for your help...

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse field (2000)

    You could create a new column in your query and use the expression Mid([DateReport],5,2) & "/" & Left([DateReport],4) to display the info in the manner you want. Give the column a name by preceeding the expression with the name you want to use followed by a colon.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Reverse field (2000)

    You can do this in a query. If you want to keep the original data and calculate the date in the query, enter an expression like this in an empty column of the query grid:

    ReportDate2: DateSerial(Left([ReportDate],4),Right([ReportDate],2),1)

    and set the Format property for this column to mm/yyyy.

    If you want to store the date in the table, add a Date/Time field to the table in design view. Next, create a query based on the table, and select Query | Update Query. Add the Date/Time field to the query grid and enter the following expression in the Update to line for this field:

    DateSerial(Left([ReportDate],4),Right([ReportDate],2),1)

    Select Query | Run or click the Run button on the toolbar. Note: if you have blank values in the ReportDate field, add Is Not Null to the Criteria line of the Date/Time field before running the query.

Posting Permissions

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