Convert Dates SQL UNION

[Shaunk23]

I have a union Query displaying in listbox... All date fields are defined as date/time in table with the format being MM,DD,YY. Seems to work in all the field forms.. however in my below union "Date Created" always returns MM,DD,YYYY.

How can i convert these to MM/DD/YY to show in my listbox. I know its CONVERT(DataType(),Expression,Format )

What is my datatype?

SELECT ExpRecords.ID, ExpRecords.AEC, "AEC Created" AS Expr1, ExpRecords.DateCreated, ExpRecords.[Job Manager], 10 as orderinfo
FROM ExpRecords
WHERE (((ExpRecords.AEC)=[Forms]![AeclMainExportScreen]![AEC]));



UNION ALL SELECT ExpBooking.ID, ExpBooking.AEC, "Booking" AS Expr1, ExpBooking.DateCreated, [QuantityOfEquip] & " X " & [EquipmentType] AS Expr2, 20 as orderinfo
FROM ExpBooking
WHERE (((ExpBooking.AEC)=[Forms]![AeclMainExportScreen]![AEC]));




UNION ALL SELECT ExpMoveOrders.ID, ExpMoveOrders.AEC, "Move Order" AS Expr1, ExpMoveOrders.DateCreated, [InlandCarrier] & " | " & [InlandMoveType] AS Expr2, 30 as orderinfo
FROM ExpMoveOrders
WHERE (((ExpMoveOrders.AEC)=[Forms]![AeclMainExportScreen]![AEC]));




UNION ALL SELECT ExpDocRecAuto.DocID, ExpDockRec.AEC, "Dock Receipt" AS Expr1, ExpDockRec.DateCreated, ExpDocRecAuto.AutoVin, 40 as orderinfo
FROM ExpDockRec LEFT JOIN ExpDocRecAuto ON ExpDockRec.ID = ExpDocRecAuto.DocID
WHERE (((ExpDockRec.ID)=[ExpDockRec]![ID]) AND ((ExpDocRecAuto.DocID)=[ExpDockRec]![ID]) AND ((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="Roll On Roll Off"));




UNION ALL SELECT ExpDockRec.ID, ExpDockRec.AEC, "Dock Receipt" AS Expr1, ExpDockRec.DateCreated, ExpDockRec.[Container Number], 40 as orderinfo
FROM ExpDockRec
WHERE (((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="Container (General Cargo)")) OR (((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="HHG & Automobiles (Container)")) OR (((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="Automobiles Only (Container)")) OR (((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="HHG & Personal Effects (Container)")) OR (((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="Blank - Custom")) OR (((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="REEFER"));




UNION ALL SELECT ExpDockRec.ID, ExpDockRec.AEC, "Dock Receipt" AS Expr1, ExpDockRec.DateCreated, ExpDockRec.TypeOfDockRec, 40 as orderinfo
FROM ExpDockRec
WHERE (((ExpDockRec.AEC)=Forms!AeclMainExportScreen!AEC) And ((ExpDockRec.TypeOfDockRec)="LCL / LTL"));



UNION ALL SELECT ExpDockRec.ID, ExpDockRec.AEC, "Dock Receipt" AS Expr1, ExpDockRec.DateCreated, ExpDockRec.TypeOfDockRec, 40 as orderinfo
FROM ExpDockRec
WHERE (((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.TypeOfDockRec)="BreakBulk Item"));



UNION ALL SELECT ExpDocRecAuto.DocID, ExpDockRec.AEC, "Dock Receipt" AS Expr1, ExpDockRec.DateCreated, [AutoMake] & " " & [AutoModel] AS Expr2, 40 as orderinfo
FROM ExpDockRec LEFT JOIN ExpDocRecAuto ON ExpDockRec.ID = ExpDocRecAuto.DocID
WHERE (((ExpDocRecAuto.DocID)=[ExpDockRec]![ID]) AND ((ExpDockRec.AEC)=[Forms]![AeclMainExportScreen]![AEC]) AND ((ExpDockRec.ID)=[ExpDockRec]![ID]) AND ((ExpDockRec.TypeOfDockRec)="High Heavy Unit"))



UNION ALL SELECT ExpTransmittal.ID, ExpTransmittal.AEC, "Transmittal" AS Expr1, ExpTransmittal.DateRecieved, "Documents Possessed" AS Expr2, 50 as orderinfo
FROM ExpTransmittal
WHERE (((ExpTransmittal.AEC)=[Forms]![AeclMainExportScreen]![AEC]))


UNION ALL SELECT ExpDocsOut.ID, ExpDocsOut.aec, "Doc's Out" AS Expr1, ExpDocsOut.SentDate, ExpDocsOut.SentVia, 90 AS orderinfo
FROM ExpDocsOut
WHERE (((ExpDocsOut.aec)=[Forms]![AeclMainExportScreen]![AEC]));

 

[sparks80]

Hi,

Firstly as far as I know there is no native function called Convert in Access, unless this has been introduced in the latest version.

You can force a date to display in a certain format using the Format function like this:

FormattedDate: Format([DateField], "dd/mm/yyyy")

So for the first part of the union query you would write:

SELECT ExpRecords.ID, ExpRecords.AEC, "AEC Created" AS Expr1, FormattedDateCreated: Format(ExpRecords.DateCreated, "dd/mm/yyyy"), ExpRecords.[Job Manager], 10 as orderinfo
FROM ExpRecords
WHERE (((ExpRecords.AEC)=[Forms]![AeclMainExportScreen]![AEC]));