Date order with a union all

Hi Guys and Gals,

I have a problem with my date sorting. I have 3 tables all with a comman field "reviewdate" which is within MS Access as a date/time type. I need to look at all three tables and join the results, and then display them within dat order. So far my sql code selects the records fine, but does not sort the all the records put together into date order!

Code ---------------------------

varSQL = "SELECT tbl_client_gen.reviewdate, tbl_client_gen.reviewreason, tbl_client1.* FROM tbl_client_gen, tbl_client1 WHERE cint(tbl_client_gen.clientid_1)=tbl_client1.fld_clientid ORDER BY tbl_client_gen.reviewdate ASC"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_life.reviewdate, tbl_life.reviewreason, tbl_client1.* FROM tbl_life, tbl_client1 WHERE cint(tbl_life.clientid1)=tbl_client1.fld_clientid ORDER BY reviewdate ASC"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_mortgage.reviewdate, tbl_mortgage.reviewreason, tbl_client1.* FROM tbl_mortgage, tbl_client1 WHERE cint(tbl_mortgage.clientid1)=tbl_client1.fld_clientid ORDER BY reviewdate ASC"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_general_insure.reviewdate, tbl_general_insure.reviewreason, tbl_client1.* FROM tbl_general_insure, tbl_client1 WHERE cint(tbl_general_insure.clientid1)=tbl_client1.fld_clientid ORDER BY reviewdate ASC"

END CODE ------------------------------


Does anyone have and ideas as to where I might be going wrong?

thanks

loz

Try ordering by the column number

e.g.
ORDER BY 1

sorry that didnt work - thanks for trying!

You only need the ORDER BY once. Put it at the end of the query.

new code

-----------------------
varSQL = "SELECT * FROM ("
varSQL = varSQL & "SELECT tbl_client_gen.reviewdate, tbl_client_gen.reviewreason, tbl_client1.* FROM tbl_client_gen, tbl_client1 WHERE cint(tbl_client_gen.clientid_1)=tbl_client1.fld_clientid"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_life.reviewdate, tbl_life.reviewreason, tbl_client1.* FROM tbl_life, tbl_client1 WHERE cint(tbl_life.clientid1)=tbl_client1.fld_clientid"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_mortgage.reviewdate, tbl_mortgage.reviewreason, tbl_client1.* FROM tbl_mortgage, tbl_client1 WHERE cint(tbl_mortgage.clientid1)=tbl_client1.fld_clientid"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_general_insure.reviewdate, tbl_general_insure.reviewreason, tbl_client1.* FROM tbl_general_insure, tbl_client1 WHERE cint(tbl_general_insure.clientid1)=tbl_client1.fld_clientid"
varSQL = varSQL & ") ORDER BY reviewdate"
-------------------------------

still doesnt work??

date format is dd/mm/yyyy
ms access
VB ASP
and field is Date/Time

try this:
varSQL = varSQL & "SELECT tbl_client_gen.reviewdate, tbl_client_gen.reviewreason, tbl_client1.* FROM tbl_client_gen, tbl_client1 WHERE cint(tbl_client_gen.clientid_1)=tbl_client1.fld_clientid"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_life.reviewdate, tbl_life.reviewreason, tbl_client1.* FROM tbl_life, tbl_client1 WHERE cint(tbl_life.clientid1)=tbl_client1.fld_clientid"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_mortgage.reviewdate, tbl_mortgage.reviewreason, tbl_client1.* FROM tbl_mortgage, tbl_client1 WHERE cint(tbl_mortgage.clientid1)=tbl_client1.fld_clientid"
varSQL = varSQL & " UNION ALL"
varSQL = varSQL & " SELECT tbl_general_insure.reviewdate, tbl_general_insure.reviewreason, tbl_client1.* FROM tbl_general_insure, tbl_client1 WHERE cint(tbl_general_insure.clientid1)=tbl_client1.fld_clientid"
varSQL = varSQL & " ORDER BY reviewdate"