query returns odd result

  • Thread starter Thread starter Support
  • Start date Start date
S

Support

Hi, I'm v new to Access and have been asked to write a query on a database
we have.
There is one table which has a field called o_OrderSubTotalIncVAT which
gives total order value.
I've written this query:
SELECT tblFinal2.c_SourceCustomerId, tblFinal2.c_Title,
tblFinal2.c_FirstName, tblFinal2.c_LastName,
tblAllorderlines2.l_OrderNumber, tblOrders2.o_OrderDate,
tblAllorderlines2.l_ProductReference,
tblAllorderlines2.l_OrderLineProductDescription,
tblAllorderlines2.l_UnitPriceIncVAT, tblAllorderlines2.l_ProductQuantity,
tblAllorderlines2.l_PriceIncVAT, tblOrders2.o_OrderSubTotalIncVAT,
tblOrders2.o_OrderShippingCost, tblOrders2.o_OrderTotalIncVAT
FROM (tblFinal2 INNER JOIN tblOrders2 ON tblFinal2.c_SourceCustomerId =
tblOrders2.o_SourceCustomerID) INNER JOIN tblAllorderlines2 ON
tblOrders2.o_OrderNumber = tblAllorderlines2.l_OrderNumber;
This works and gives me the orders however I would like to find orders over
300. When I add the criteria >"300" or SQL:
WHERE (((tblOrders2.o_OrderTotalIncVAT)>"300"))
I still get values under 300. I have tried sorting (descending) the data
both from the query and also just in the field. All times it lists it in
order from 9-0 including mulitples thereof eg 99, 94, 9.45, 87, 8.4,700, 75,
7.6 etc etc - hope that is clear!
Where I am going wrong here?
thanks
 
You've got quotes around the 300: does that mean that
tblOrders2.o_OrderTotalIncVAT is actually a Text field?

If it is, text fields sort "alphabetically", rather than numerically. "2" is
greater than "1", but it's also greater than "11", "12", "111", etc.

To treat it as a number, try:

WHERE (((CLng(tblOrders2.o_OrderTotalIncVAT))>300))
 
Well, assuming that o_OrderTotalIncVAT is a numeric field, your WHERE clause
should look like this:
WHERE (((tblOrders2.o_OrderTotalIncVAT)>300))

You only use the (") for text fields. For DateTime fields, you use the (#)
to delimit the value.
 
Support said:
Hi, I'm v new to Access and have been asked to write a query on a
database we have.
There is one table which has a field called o_OrderSubTotalIncVAT
which gives total order value.
I've written this query:
SELECT tblFinal2.c_SourceCustomerId, tblFinal2.c_Title,
tblFinal2.c_FirstName, tblFinal2.c_LastName,
tblAllorderlines2.l_OrderNumber, tblOrders2.o_OrderDate,
tblAllorderlines2.l_ProductReference,
tblAllorderlines2.l_OrderLineProductDescription,
tblAllorderlines2.l_UnitPriceIncVAT,
tblAllorderlines2.l_ProductQuantity, tblAllorderlines2.l_PriceIncVAT,
tblOrders2.o_OrderSubTotalIncVAT, tblOrders2.o_OrderShippingCost,
tblOrders2.o_OrderTotalIncVAT
FROM (tblFinal2 INNER JOIN tblOrders2 ON tblFinal2.c_SourceCustomerId
= tblOrders2.o_SourceCustomerID) INNER JOIN tblAllorderlines2 ON
tblOrders2.o_OrderNumber = tblAllorderlines2.l_OrderNumber;
This works and gives me the orders however I would like to find
orders over 300. When I add the criteria >"300" or SQL:
WHERE (((tblOrders2.o_OrderTotalIncVAT)>"300"))
I still get values under 300. I have tried sorting (descending) the
data both from the query and also just in the field. All times it
lists it in order from 9-0 including mulitples thereof eg 99, 94,
9.45, 87, 8.4,700, 75,
7.6 etc etc - hope that is clear!
Where I am going wrong here?
thanks
Click to expand...

From the sound of it, your field o_OrderTotalIncVAT is defined as a text
field. That would be why it sorts alphabetically, rather than
numerically, and why the criterion you enter in the query grid is being
treated as a string. I can't say for sure whether the definition of
this field as text rather than, say, currency, is an error in the table
design, or whether there's some obscure reason why it is defined that
way. It seems to me that it is most likely an error, though. If you
change the definition of the field, also change the query so that
instead of
WHERE (((tblOrders2.o_OrderTotalIncVAT)>"300"))
Click to expand...

it says

WHERE (((tblOrders2.o_OrderTotalIncVAT)>300))
 
Hi, thanks both for your help - you are indeed correct. I imported the data
into the tables from a text file and didn't think to check the data types.
All fields have been set as text so I am going in to change these. Is it
advisable to use currency or number data types? Also, I've been changing
some of tha data types for other fields (eg DOB to date/time) and if I save
this, it trys to delete some records. Why would that happen?
thanks
 
Support said:
Hi, thanks both for your help - you are indeed correct. I imported
the data into the tables from a text file and didn't think to check
the data types. All fields have been set as text so I am going in to
change these. Is it advisable to use currency or number data types?
Also, I've been changing some of tha data types for other fields (eg
DOB to date/time) and if I save this, it trys to delete some records.
Why would that happen?
thanks
Click to expand...

If what's being stored is monetary amounts, I would use Currency rather
than any other numeric type. Currency fields have a fixed precision,
where Single or Double fields are floating-point and hence imprecise,
and Integer and Long Integer fields can't hold values that have decimal
places -- no "cents".

As for your DOB fields, it's likely that you have some values in there
that aren't valid dates, or that Access can't intepret as dates. If the
table is small, just look through it for such values. If the table is
large, you may need to run a query to find these records. Maybe a query
similar to this would show them to you:

SELECT * FROM MyTableName WHERE IsDate(DOB) = 0;
 
Back
Top