Why is 32 < 4?

[Marty444]

Numerical values in my database and exported tables are not registering as wanted. The first digit is recognized, but adding a second digit does not give the data a significantly higher value. For example a column sorted by increasing value may look like this:

2
23
24
26
3
3
3
3
31
39
4
40
42
5
5
6
61
etc.

I have tried changing the Field Size from Integer to Long Integer to Decimal, but this does cause have an apparent difference.

Any advice?

 

[Tiro Cupidus]

Does it sort like this only after it has been exported?

 

[mvos]

The only reason this would happen is if the numbers are stored as text.

 

[Tiro Cupidus]

Yep, which is why it should be doing it only in some spreadsheet viewer (Excel) after an export, but not in Access.

 

[Marty444]

Nope, I get this kind of sorting mishap in both Access and in exported Excel spreadsheets. The data type is "number." This was not enetered as text and the database was designed from the start to read this field as a number. The same issue persists.

 

[mvos]

What happens if you try to a calculation on the field, say [MyField] * 2 ?

 

[Marty444]

Manipulating the field...Very good question! The resulting value is correct. 2*50=100 as expected and desired. But, 50 still should be recognized as greater than 6, right?

 

[mvos]

Running out of ideas here

You could try creating a new field in the table and running an update query to copy the data into the new field.

Then see what the resulting new field looks like.

 

[Marty444]

Thanks for trying.

 

[Pat Hartman]

Is the field defined as numeric in the table?
Are you exporting the table or a query?
If a query, have you formatted the field in the query therefore changing it to text?

If you can't find what is making the field's data type text rather than numeric, you can create a query that will force the field to be numeric. Use the CLng(), CInt(), CSng(), or CDbl() function depending on what is appropriate for the data values.