Format Cells in a query created using SQL

[Elizabeth]

Hi! I used SQL to create a query (I copied and pasted the verbiage that some
WONDERFUL soul gave me, woohoo!). But I don't know how to use SQL othe rthan
to copy and paste the verbiage goven to me by others and just insert my own
field names. So my query turned out BEAUTIFULLY in terms of the data
provided..

But the dates in the date column are different formats. Some have the date
AND time and others just the date. Because I used the SQL creen, though, I
cannot get to the design screen to change the formatting. Any idea how I
change the formatting for that one field? This is what my SQL code looks
like...I just need to change the format of the "Posted Date" column to be the
standard MM-DD-YYYY format.

Select [POS_ID],[Posted
Date],[Salesrep],[EndCustomer],[ShipCustomer],[Value],[Reason],[Prod/Serv],[Op],[Region],[AM/Team],[Claim_Split_%],[Order Type],[Report Name]
From [Table - POS]
Union All
Select [POS_ID],[Posted
Date],[Salesrep],[EndCustomer],[ShipCustomer],[Value],[Reason],[Prod/Serv],[Op],[Region],[AM/Team],[Claim_Split_%],[Order Type],[Report Name]
From [Table - Manual]
Union All
Select [POS_ID],[Posted
Date],[Salesrep],[EndCustomer],[ShipCustomer],[Value],[Reason],[Prod/Serv],[Op],[Region],[AM/Team],[Claim_Split_%],[Order Type],[Report Name]
From [Table - Unallocated]
UNION ALL
Select [POS_ID],[Posted
Date],[Salesrep],[EndCustomer],[ShipCustomer],[Value],[Reason],[Prod/Serv],[Op],[Region],[AM/Team],[Claim_Split_%],[Order Type],[Report Name]
From [Table - Unclaimed Sherrel]
UNION ALL
Select [POS_ID],[Posted
Date],[Salesrep],[EndCustomer],[ShipCustomer],[Value],[Reason],[Prod/Serv],[Op],[Region],[AM/Team],[Claim_Split_%],[Order Type],[Report Name]
From [Table - Unclaimed POS Carl]

Thanks!
Elizabeth

 

[Jerry Whittle]

Create a query based on the query below.

For the date fields, use the Format function to make the dates look like you
want.

ThePostedDate: Format([Posted Date], "mm-dd-yyyy")

The Format function returns a string so it might not sort like a date
afterwards.

 

[John Spencer]

You can use the Format function, but that will turn the date field into a
string field

Select [POS_ID],Format([Posted Date],"mm-dd-yyyy") as PostedDate
,[Salesrep],[EndCustomer],[ShipCustomer],[Value],[Reason],[Prod/Serv]
,[Op],[Region],[AM/Team],[Claim_Split_%],[Order Type],[Report Name]
From [Table - POS]

OR you can strip off the time component using the datevalue function. If
Posted Date ALWAYS has a value, then you can just use
DateValue([Posted Date])
However, if Posted Date can be null (blank), then you need to test for that
IIF(IsDate([Posted Date]),DateValue([Posted Date]),Null)

Select [POS_ID]
,IIF(IsDate([Posted Date]),DateValue([Posted Date]),Null) as PostedDate
,[Salesrep],[EndCustomer],[ShipCustomer],[Value],[Reason],[Prod/Serv]
,[Op],[Region],[AM/Team],[Claim_Split_%],[Order Type],[Report Name]
From [Table - POS]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hi! I used SQL to create a query (I copied and pasted the verbiage that some
WONDERFUL soul gave me, woohoo!). But I don't know how to use SQL othe rthan
to copy and paste the verbiage goven to me by others and just insert my own
field names. So my query turned out BEAUTIFULLY in terms of the data
provided..

But the dates in the date column are different formats. Some have the date
AND time and others just the date. Because I used the SQL creen, though, I
cannot get to the design screen to change the formatting. Any idea how I
change the formatting for that one field? This is what my SQL code looks
like...I just need to change the format of the "Posted Date" column to be the
standard MM-DD-YYYY format.

Don't confuse data PRESENTATION with data MANIPULATION.

Queries let you select and arrange data. They're not designed for
presentation, display, or interaction.

Use a Form (for onscreen) or Report (for printing), based on your UNION query,
for that purpose. You can set the format of the textboxes on the form or
report however you like.