Conditional Data Type

[Harry Shmedlap]

I want a column in the query to be used for sorting but it depends on the type of data. In preparing to use my tables, I used a simple conditional formula:

sortField: IIf(IsNumeric("123"),CDbl("123"),"123")

Given that the test condition discovered that the data is numeric, the result is the data typed as double. Yet the result is left-justified, showing that the datatype is NOT numeric.

Then when I do it explicitly:

good: CDbl("123")

I do get a right-justified numeric field.

Why doesn't IIF() return the value as a numeric type?

 

[boblarson]

Why doesn't IIF() return the value as a numeric type?

Sorry, you get one datatype per column. If you got text in there, and you convert it to numeric then it will do it for the entire column. If you have text and you leave it as text it will show as text. If you try to mix it like you are, it will still be text.

The way access thinks is that a field is always the same datatype or else it could not be related to the records around it.

 

[gemma-the-husky]

but a number will sort correctly as opposed to letters.

are you just trying to stop the number being displayed right-justified?

 

[Harry Shmedlap]

The sorting is only one task. I also want to do comparisons.
Perhaps you nice folks can suggest an alternate solution. Here is the problem.

Given a inventory of products, each defined by 3 parameters, each of which has a data type.

Begin with the "lowest" table in the object hierarchy, the table of data types, tblDT, where each record has a Name field:

DOUBLE
INTEGER
STRING

The next "higher" table is the table of parameters, tblPR, where each parameter has a Name and a data type pointer:

PACKAGE, STRING
SIZE, DOUBLE

Now for a "higher" table, that of inventory parts, tblINV, where each part is defined by one or more records, where each record contains one parameter pointer and associated value:

"Ruler", SIZE, 12.0
"Glue", PACKAGE, "Tube"
"Paper", SIZE, 11.1
"Paper", PACKAGE, "Box"

(From this table, using a cross-tab query, I can generate an inventory list, with the parameters shown as column headings).

Now I want to do sorts, comparisons, totals, etc. on the inventory, for each parameter, based on the parameter's data type.
The formula I posted last time, used in the query of the above, would be something like this:
sortField: switch(tblDT.Name="DOUBLE",CDbl(tblPR.Value), tblDT.Name="Integer", CInt(tblPR.Value), tblDT.Name="String", tblPR.Value))
Thus, when I filter the output to show only SIZE parameters, the sorting will be done numerically, whereas if I filter a STRING parameter it will be sorted alphabetically.
(I'm simplifying the problem considerably, as I have 5 data types, over 50 parameters and thousands of products).
Any thoughts...?