Union query converting date to text

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hi,

I have the following union query. Basically I am trying to merge the two
tables. The Tbl_Summary_PO_Details table contains the expected delivery date
(OTB_Date with a Date/Time Data Type) and the Tbl_Summary_Rcpt_Details
doesn't so I put a 0 (zero) in this field.

The result of this query seems to work except all of the dates (and zeros)
are left justified.

I then tried to use the result of the query to be used in another query that
linked the OTB_Date field to a field in another table (with a Date/Time Data
Type) but I receive a 'Type mismatch in expression' error.

I've tried searching this site for the solution and can't seem to find the
answer.


SELECT Tbl_Summary_PO_Details.[Company], Tbl_Summary_PO_Details.[OTB Date]
AS OTB_Date, [Old Calendar OTB Period] AS Old_Calendar_Report_Period,[New
Calendar OTB Period] AS New_Calendar_Report_Period,
Tbl_Summary_PO_Details.[Po Number], Tbl_Summary_PO_Details.[Po Type],
Tbl_Summary_PO_Details.[Po Status], Tbl_Summary_PO_Details.[Product Number],
Tbl_Summary_PO_Details.[Units Ordered], Tbl_Summary_PO_Details.[Cost Ordered]
AS [Total Cost$], Tbl_Summary_PO_Details.[Retail Ordered] AS [Total Retail$],
0 AS [Units Received], 0 AS [Cost Received], 0 AS [Retail Received]
FROM Tbl_Summary_PO_Details;
UNION SELECT Tbl_Summary_Rcpt_Details.[Company], 0 AS OTB_Date,
Tbl_Summary_Rcpt_Details.`Rcvd Period` AS Old_Calendar_Report_Period,
Tbl_Summary_Rcpt_Details.`Rcvd Period` AS New_Calendar_Report_Period,
Tbl_Summary_Rcpt_Details.[Po Number], Tbl_Summary_Rcpt_Details.[Po Type],
Tbl_Summary_Rcpt_Details.[Po Status], Tbl_Summary_Rcpt_Details.[Product
Number], 0 AS [Order Quantity], 0 AS [Total Cost$], 0 AS [Total Retail$],
Tbl_Summary_Rcpt_Details.[Units Received], Tbl_Summary_Rcpt_Details.[Cost
Received], Tbl_Summary_Rcpt_Details.[Retail Received]
FROM Tbl_Summary_Rcpt_Details;
 
Try

SELECT Tbl_Summary_PO_Details.[Company], Tbl_Summary_PO_Details.[OTB Date]
AS OTB_Date, [Old Calendar OTB Period] AS Old_Calendar_Report_Period,[New
Calendar OTB Period] AS New_Calendar_Report_Period,
Tbl_Summary_PO_Details.[Po Number], Tbl_Summary_PO_Details.[Po Type],
Tbl_Summary_PO_Details.[Po Status], Tbl_Summary_PO_Details.[Product Number],
Tbl_Summary_PO_Details.[Units Ordered], Tbl_Summary_PO_Details.[Cost
Ordered]
AS [Total Cost$], Tbl_Summary_PO_Details.[Retail Ordered] AS [Total
Retail$],
0 AS [Units Received], 0 AS [Cost Received], 0 AS [Retail Received]
FROM Tbl_Summary_PO_Details;
UNION SELECT Tbl_Summary_Rcpt_Details.[Company], CDate(0),
Tbl_Summary_Rcpt_Details.[Rcvd Period],
Tbl_Summary_Rcpt_Details.[Rcvd Period],
Tbl_Summary_Rcpt_Details.[Po Number], Tbl_Summary_Rcpt_Details.[Po Type],
Tbl_Summary_Rcpt_Details.[Po Status], Tbl_Summary_Rcpt_Details.[Product
Number], 0 , 0, Tbl_Summary_Rcpt_Details.[Units Received],
Tbl_Summary_Rcpt_Details.[Cost Received],
Tbl_Summary_Rcpt_Details.[Retail Received]
FROM Tbl_Summary_Rcpt_Details;


(Note that it's not necessary to supply a field name in any subselect after
the first one: the query gets all its field names from the first subselect)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andrew said:
Hi,

I have the following union query. Basically I am trying to merge the two
tables. The Tbl_Summary_PO_Details table contains the expected delivery
date
(OTB_Date with a Date/Time Data Type) and the Tbl_Summary_Rcpt_Details
doesn't so I put a 0 (zero) in this field.

The result of this query seems to work except all of the dates (and zeros)
are left justified.

I then tried to use the result of the query to be used in another query
that
linked the OTB_Date field to a field in another table (with a Date/Time
Data
Type) but I receive a 'Type mismatch in expression' error.

I've tried searching this site for the solution and can't seem to find the
answer.


SELECT Tbl_Summary_PO_Details.[Company], Tbl_Summary_PO_Details.[OTB Date]
AS OTB_Date, [Old Calendar OTB Period] AS Old_Calendar_Report_Period,[New
Calendar OTB Period] AS New_Calendar_Report_Period,
Tbl_Summary_PO_Details.[Po Number], Tbl_Summary_PO_Details.[Po Type],
Tbl_Summary_PO_Details.[Po Status], Tbl_Summary_PO_Details.[Product
Number],
Tbl_Summary_PO_Details.[Units Ordered], Tbl_Summary_PO_Details.[Cost
Ordered]
AS [Total Cost$], Tbl_Summary_PO_Details.[Retail Ordered] AS [Total
Retail$],
0 AS [Units Received], 0 AS [Cost Received], 0 AS [Retail Received]
FROM Tbl_Summary_PO_Details;
UNION SELECT Tbl_Summary_Rcpt_Details.[Company], 0 AS OTB_Date,
Tbl_Summary_Rcpt_Details.`Rcvd Period` AS Old_Calendar_Report_Period,
Tbl_Summary_Rcpt_Details.`Rcvd Period` AS New_Calendar_Report_Period,
Tbl_Summary_Rcpt_Details.[Po Number], Tbl_Summary_Rcpt_Details.[Po Type],
Tbl_Summary_Rcpt_Details.[Po Status], Tbl_Summary_Rcpt_Details.[Product
Number], 0 AS [Order Quantity], 0 AS [Total Cost$], 0 AS [Total Retail$],
Tbl_Summary_Rcpt_Details.[Units Received], Tbl_Summary_Rcpt_Details.[Cost
Received], Tbl_Summary_Rcpt_Details.[Retail Received]
FROM Tbl_Summary_Rcpt_Details;
Click to expand...
 
Thanks Douglas for your quick reply. Basically if I understand it correctly
I needed to have the function CDate in front of the 0.

Also, I have taken note of your suggestion of not having to repeat the
headings (although you did forget the last '0,' in your retyping causing me a
little greif.

Thanks again.



--
Andrew


Douglas J. Steele said:
Try

SELECT Tbl_Summary_PO_Details.[Company], Tbl_Summary_PO_Details.[OTB Date]
AS OTB_Date, [Old Calendar OTB Period] AS Old_Calendar_Report_Period,[New
Calendar OTB Period] AS New_Calendar_Report_Period,
Tbl_Summary_PO_Details.[Po Number], Tbl_Summary_PO_Details.[Po Type],
Tbl_Summary_PO_Details.[Po Status], Tbl_Summary_PO_Details.[Product Number],
Tbl_Summary_PO_Details.[Units Ordered], Tbl_Summary_PO_Details.[Cost
Ordered]
AS [Total Cost$], Tbl_Summary_PO_Details.[Retail Ordered] AS [Total
Retail$],
0 AS [Units Received], 0 AS [Cost Received], 0 AS [Retail Received]
FROM Tbl_Summary_PO_Details;
UNION SELECT Tbl_Summary_Rcpt_Details.[Company], CDate(0),
Tbl_Summary_Rcpt_Details.[Rcvd Period],
Tbl_Summary_Rcpt_Details.[Rcvd Period],
Tbl_Summary_Rcpt_Details.[Po Number], Tbl_Summary_Rcpt_Details.[Po Type],
Tbl_Summary_Rcpt_Details.[Po Status], Tbl_Summary_Rcpt_Details.[Product
Number], 0 , 0, Tbl_Summary_Rcpt_Details.[Units Received],
Tbl_Summary_Rcpt_Details.[Cost Received],
Tbl_Summary_Rcpt_Details.[Retail Received]
FROM Tbl_Summary_Rcpt_Details;


(Note that it's not necessary to supply a field name in any subselect after
the first one: the query gets all its field names from the first subselect)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andrew said:
Hi,

I have the following union query. Basically I am trying to merge the two
tables. The Tbl_Summary_PO_Details table contains the expected delivery
date
(OTB_Date with a Date/Time Data Type) and the Tbl_Summary_Rcpt_Details
doesn't so I put a 0 (zero) in this field.

The result of this query seems to work except all of the dates (and zeros)
are left justified.

I then tried to use the result of the query to be used in another query
that
linked the OTB_Date field to a field in another table (with a Date/Time
Data
Type) but I receive a 'Type mismatch in expression' error.

I've tried searching this site for the solution and can't seem to find the
answer.


SELECT Tbl_Summary_PO_Details.[Company], Tbl_Summary_PO_Details.[OTB Date]
AS OTB_Date, [Old Calendar OTB Period] AS Old_Calendar_Report_Period,[New
Calendar OTB Period] AS New_Calendar_Report_Period,
Tbl_Summary_PO_Details.[Po Number], Tbl_Summary_PO_Details.[Po Type],
Tbl_Summary_PO_Details.[Po Status], Tbl_Summary_PO_Details.[Product
Number],
Tbl_Summary_PO_Details.[Units Ordered], Tbl_Summary_PO_Details.[Cost
Ordered]
AS [Total Cost$], Tbl_Summary_PO_Details.[Retail Ordered] AS [Total
Retail$],
0 AS [Units Received], 0 AS [Cost Received], 0 AS [Retail Received]
FROM Tbl_Summary_PO_Details;
UNION SELECT Tbl_Summary_Rcpt_Details.[Company], 0 AS OTB_Date,
Tbl_Summary_Rcpt_Details.`Rcvd Period` AS Old_Calendar_Report_Period,
Tbl_Summary_Rcpt_Details.`Rcvd Period` AS New_Calendar_Report_Period,
Tbl_Summary_Rcpt_Details.[Po Number], Tbl_Summary_Rcpt_Details.[Po Type],
Tbl_Summary_Rcpt_Details.[Po Status], Tbl_Summary_Rcpt_Details.[Product
Number], 0 AS [Order Quantity], 0 AS [Total Cost$], 0 AS [Total Retail$],
Tbl_Summary_Rcpt_Details.[Units Received], Tbl_Summary_Rcpt_Details.[Cost
Received], Tbl_Summary_Rcpt_Details.[Retail Received]
FROM Tbl_Summary_Rcpt_Details;
Click to expand...
Click to expand...
 
Back
Top