Full Version: Integer Interpreted As Text In Union Query
UtterAccess Forums > Microsoft® Access > Access Forms
expatriate
Hi,
I have a table with a sort column (integer). When I use it as the source of a cbo within a UNION query below, the CBO sorts as if the integer is text, like 18,19,2,20,21... I've tried using format() to general number but either it won't work or I have not got the syntax right.
SELECT tblMasterGR.MasterGRPK, tblMasterGR.MasterGR, sort FROM tblMasterGR UNION SELECT 0,"<Make Selection>","" FROM tblMasterGR ORDER BY SORT;
Thank you.
MStef
Check in the table,
Data Type (it must be a NUMBER).
expatriate
Like I said, it is an integer. Works fine in a regular query, but not in the union query.
vtd
Your Union SQL combines an Integer Column [sort] with a implicit Text Column represented by the zero-length String "" and thererfore the resultant Column cannot be numeric. The resultant column is of Variant data-type and when sorted, it is sorted as Variant of String sub-type.
You need to change the ZLS explicit value to a suitable numeric value, e.g. 0.
expatriate
Ah, it seems so obvious now!
Thank you.
vtd
You're welcome... Glad we could help...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.