Problems With Microsoft Access

[mark.b]

Hi Guys,

I've got a table in Access 2008 and the data was imported from an XML
file.

The problem I have is when I try to run a query on the table (>=
20.00) the results show all records no matter the amount within the
field I'm querying.

I've had a look at the table and have noted that the field type is set
to TEXT rather than numerical - does this make a difference?

I've created a relationship with two other tables within the same data
base because I needed details from those tables that isn't availble
from the first table (if that make's sense!)

I have also run the query just on the firts table but is still
generating the same resluts described above.

I'm wondering if any one else has had this problem and what they did
to overcome it?

Thanks in advance.
Mark

 

[Bob Barrows]

mark.b wrote:

"Problems With Microsoft Access"? Err, why else would you be posting
here? How about using a more meaningful subject line next time?

Hi Guys,

I've got a table in Access 2008 and the data was imported from an XML
file.

The problem I have is when I try to run a query on the table (>=
20.00) the results show all records no matter the amount within the
field I'm querying.

I've had a look at the table and have noted that the field type is set
to TEXT rather than numerical - does this make a difference?

Yes.
"23" is greater than "205" when sorted as text.

I'm wondering if any one else has had this problem and what they did
to overcome it?

I've not had this problem for as long as I can remember because I am
always careful to use appropriate datatypes for the values being stored.
Numeric data should be stored in numeric columns.

If you are doing a periodic import from that xml file, you should create
a new table, assigning the appropriate datatypes to the fields, and then
use an Append query to insert the newly imported records from the
"staging" table into the new table.

Alternatively, if performance is not an issue, you can convert the text
to a number in your query. Switch your query to SQL view and change:

.... WHERE [fieldname]>=20.00

to

.... WHERE CDbl([fieldname]) >=20.00