Converting Date Formats in a Query

These steps show how the DATELOOKUP table can be utilized to convert date field formats for enhanced searching and sorting capabilities in the Report Builder (Query) tool.

 

    1. Launch Tool #99 Manage My Custom Reports.

    2. Select the name of an existing query in the list.

    3. Click the Work With option.

    4. Click the Choose Files button.

    5. On the Specify File Selections screen you’ll see the file(s) that have already been included in this Query.

    6. Click the Add File button (F9).

    7. Enter DATELOOKUP into the File name box.

    8. Click the forward arrow twice.

    9. Click the Matched Records button.

    10. Under the leftmost “Field Name” column, enter the name of the field with the date you want converted.

    11. Under the “Comparison” column, choose EQ = Equal to.

    12. Under the rightmost “Field Name” column, enter a field from the last file in the list of Available Fields that matches the formatting of the field you selected for the first file to match against.

    For example, if your original field is in the format of MMDDYY, select the field DTMMDDYY from the DATELOOKUP file as that matches MMDDYY formatting.

    13. Click the forward arrow.

     

    Using the fields from DATELOOKUP, you now have multiple alternative date formats available to choose and utilize. While on the Report Builder Main Menu screen:

    To see the alternative date formats on your report, use the Choose Fields button.

    To apply filters with the alternative date formats, use the Choose Records menu option