Date Sort in Union Query

[Dave177]

I've been trying to create a union query from two tables with a date field but when I sort by this field it treats it as a number...
Originally I had:

SELECT ALL [Estab] AS [ACC], [Industry],[PriceNet] AS [Value],Format([InvDate],"dd/mm/yyyy") AS [Date],[HCSC]
FROM [tblSCInternalSales]

UNION ALL SELECT [ACC],[Industry],[Value],Format([Date],"dd/mm/yyyy"), [HCSC]
FROM [qryHCInternalForUnion];

When I tried to sort the above it would put the dates in "number order"

So I tried (some dates are Null values)

SELECT ALL [Estab]AS [ACC], [Industry],[PriceNet] AS [Value],Nz(CDate(Format([InvDate],"dd/mm/yyyy"))) AS [Date],[HCSC]
FROM [tblSCInternalSales]

UNION ALL SELECT [ACC],[Industry],[Value],Nz(CDate(Format([Date],"dd/mm/yyyy"))), [HCSC]
FROM [qryHCInternalForUnion];

This give me a data mismatch error. Any ideas??

Dave

 

[PHV]

What about this ?
SELECT [Estab] AS [ACC],[Industry],[PriceNet] AS [Value],[InvDate] AS [Date],[HCSC]
FROM [tblSCInternalSales]
UNION ALL SELECT [ACC],[Industry],[Value],[Date],[HCSC]
FROM [qryHCInternalForUnion]
ORDER BY 4;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dave177]

PH,

Thanks - I saw you did this on a similar thread...

Unfortunately it doesn't work either. I've copied some of the result of the query below:

ACC Industry Value Date HCSC
536 Engineering £1,107.96 24/04/2008 HC
536 Engineering £5,551.00 24/04/2008 HC
615 Printing £70.56 25/02/2008 SC
537 Laundry £2,176.97 25/03/2008 SC
541 Textiles £5,727.00 25/04/2008 HC

As you can see it goes from April to Feb at line 3.
Interestingly it is only those records from tblSCInternalSales that cause the problem. However, the field is definitely a formatted as a Date field...

Dave

 

[Dave177]

Sorry!! That last post isn't true. It doesn't work from which ever table the dates are from...

 

[Golom]

[tt]
ACC Industry Value Date HCSC
536 Engineering £1,107.96 24/04/2008 HC
536 Engineering £5,551.00 24/04/2008 HC
615 Printing £70.56 25/02/2008 SC
537 Laundry £2,176.97 25/03/2008 SC
541 Textiles £5,727.00 25/04/2008 HC
[/tt]

SELECT [Estab] AS [ACC], 
       [Industry],
       [PriceNet] AS [Value],
       CDate(Format(NZ([InvDate]),"dd/mm/yyyy")) AS [Date],
       [HCSC]
FROM [tblSCInternalSales]

UNION ALL 

SELECT [ACC],
       [Industry],
       [Value],
       CDate(Format(NZ([Date]),"dd/mm/yyyy")), 
       [HCSC]
FROM [qryHCInternalForUnion];

[red]ORDER BY 4[/red]

I don't see an ORDER BY in your code so I don't know how you are attempting to sort the data. If you were doing something like

ORDER BY Format([Date],"dd/mm/yyyy")

then you would get that result because you are sorting by a text string. Note that the results are Day=24 followed by Day=25 and then the months in sequence within each of those days.

 

[EJVTECH]

Have you tried setting the format of the field to "dd/mm/yyyy"? Then you can eliminate the format in your query and add ORDER BY [InvDate]

 

[Dave177]

Thank you for your replies. I apologise for not getting back sooner.

The fields in the two tables I am trying to join are in Date Format. I didn't think this would be a problem...

 

[lespaul]

ok, I guess the next question is why are you formatting the date in the query? are you using this query as the source for a form or a report? then format the date in the control on the form or report. The issue is that you are trying to sort by a formatted date, which is a string and when you sort strings you get:

'1
10
11
12
..
2
20
21
22...'



Leslie

Have you met Hardy Heron?

 

[Dave177]

Thanks Leslie,
I eventually managed to figure it out. It was for a parameter query for a report:

SELECT qryAllInternalSales.Industry, Sum(qryAllInternalSales.Value) AS SumOfValue
FROM qryAllInternalSales
WHERE (((Format([Date],"yyyy/mm/dd")) Between Format([Enter Start Date],"yyyy/mm/dd") And Format([Enter End Date],"yyyy/mm/dd")))
GROUP BY qryAllInternalSales.Industry;

Thanks for your help.

Dave