Big number gives error!

S

Sara Mellen

I have a query (which leads to a report) that has been working great for
about 3 years. One of the fields in the ORDERS table is the
BrokerOrderNumber, a long integer field which has been increasing through
the years. The problem is that as soon as we reached BrokerOrderNumber
33369, the query stopped working!

The query is made up of fields from two tables, ORDERS and ORDER DETAILS,
linked through the ORDERID field.

When I do a query based on just one of the tables, everything works fine,
but when I try to do a query based on fields from both tables, I get the
error, but ONLY if the query criteria includes the BrokerOrderNumber 33369
and above! It works fine on all lower numbers.

I get two kinds of errors--if I run the query as a parameter query, I get
the error message "The expression is typed incorrectly, or it is too compex
to be evaluated. etc." If I type in the criteria (between 33000 and 34000,
for example), I get the "Data type mismatch in criteria expression." i have
checked EVERYTHING--data types, missing number, and I'm completely stuck.

I hate to completely revamp my numbering system--anybody have an idea why
this is acting this way?

Sara Mellen
 
K

Ken Snell [MVP]

Any chance that the field in which you're using BrokerOrderNumber field is
set to Integer data type? Try changing that field to Long Integer.
 
K

Ken Snell [MVP]

Sorry...I left out a few words. What is the data type of the field for which
BrokerOrderNumber is a criterion?
 
S

Sara Mellen

I didn't write the sql...just did the query like any other partly competent
user who doesn't know sql! But here's the sql that Access wrote for me.
Keep in mind that the BrokerOrderNumber criteria is here listed as the
contents of some text boxes on a dialog box--it doesn't make any difference.
I get the same error when I do a regular parameter query and also when I
just punch in the actual numbers. I should say one more thing--the source
for this query is two other queries. I went back and built the query from
scratch (just using the tables, not other queries) but the results were the
same.

SELECT [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
Sum([Order Details Query].LineCostNoFrt) AS SumOfLineCostNoFrt, [Orders
Query for Orders Form].NegotiatedCommission,
CCur(nz([negotiatedcommission])*[sumoflinecostnofrt]) AS NegCommAmt, [Orders
Query for Orders Form].FlatCommission,
CCur(nz([flatcommission])+nz([negcommamt])) AS CommissionDue, [Orders Query
for Orders Form].CommissionPaid
FROM [Order Details Query] INNER JOIN [Orders Query for Orders Form] ON
[Order Details Query].OrderID = [Orders Query for Orders Form].OrderID
GROUP BY [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate, [Orders
Query for Orders Form].NegotiatedCommission, [Orders Query for Orders
Form].FlatCommission, [Orders Query for Orders Form].CommissionPaid
HAVING ((([Orders Query for Orders Form].BrokerOrderNumber) Between
[forms]![vendorbondialog]![txtbeginningbon] And
[forms]![vendorbondialog]![txtendingbon]))
ORDER BY [Orders Query for Orders Form].BillingDate;
 
K

Ken Snell [MVP]

OK - perhaps your query needs to have the parameters explicitly cast as long
integer values, so try this (I have used the CLng function to cast the
entered / read values as long integer type):

SELECT [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
Sum([Order Details Query].LineCostNoFrt) AS SumOfLineCostNoFrt, [Orders
Query for Orders Form].NegotiatedCommission,
CCur(nz([negotiatedcommission])*[sumoflinecostnofrt]) AS NegCommAmt, [Orders
Query for Orders Form].FlatCommission,
CCur(nz([flatcommission])+nz([negcommamt])) AS CommissionDue, [Orders Query
for Orders Form].CommissionPaid
FROM [Order Details Query] INNER JOIN [Orders Query for Orders Form] ON
[Order Details Query].OrderID = [Orders Query for Orders Form].OrderID
GROUP BY [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate, [Orders
Query for Orders Form].NegotiatedCommission, [Orders Query for Orders
Form].FlatCommission, [Orders Query for Orders Form].CommissionPaid
HAVING ((([Orders Query for Orders Form].BrokerOrderNumber) Between
CLng([forms]![vendorbondialog]![txtbeginningbon]) And
CLng([forms]![vendorbondialog]![txtendingbon])))
ORDER BY [Orders Query for Orders Form].BillingDate;

--

Ken Snell
<MS ACCESS MVP>

Sara Mellen said:
I didn't write the sql...just did the query like any other partly competent
user who doesn't know sql! But here's the sql that Access wrote for me.
Keep in mind that the BrokerOrderNumber criteria is here listed as the
contents of some text boxes on a dialog box--it doesn't make any difference.
I get the same error when I do a regular parameter query and also when I
just punch in the actual numbers. I should say one more thing--the source
for this query is two other queries. I went back and built the query from
scratch (just using the tables, not other queries) but the results were the
same.

SELECT [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
Sum([Order Details Query].LineCostNoFrt) AS SumOfLineCostNoFrt, [Orders
Query for Orders Form].NegotiatedCommission,
CCur(nz([negotiatedcommission])*[sumoflinecostnofrt]) AS NegCommAmt, [Orders
Query for Orders Form].FlatCommission,
CCur(nz([flatcommission])+nz([negcommamt])) AS CommissionDue, [Orders Query
for Orders Form].CommissionPaid
FROM [Order Details Query] INNER JOIN [Orders Query for Orders Form] ON
[Order Details Query].OrderID = [Orders Query for Orders Form].OrderID
GROUP BY [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate, [Orders
Query for Orders Form].NegotiatedCommission, [Orders Query for Orders
Form].FlatCommission, [Orders Query for Orders Form].CommissionPaid
HAVING ((([Orders Query for Orders Form].BrokerOrderNumber) Between
[forms]![vendorbondialog]![txtbeginningbon] And
[forms]![vendorbondialog]![txtendingbon]))
ORDER BY [Orders Query for Orders Form].BillingDate;







Ken Snell said:
Also..post the SQL statement that you're using as the query.

--

Ken Snell
<MS ACCESS MVP>

field
is I
get idea
why
Click to expand...
Click to expand...
 
S

Sara Mellen

I've solved this, but not in the way you suggested because, as I was totally
stressing and beating my head against a wall, I discovered that the guy who
was entering the orders had re-used broker order numbers. When he made a
mistake, instead of correcting it he just created another order. This is
not generally a problem--I had not made the field "no duplicates"--but I
hadn't foreseen that this would cause the link between the orders table and
the orders detail table to mess up. i see how it happened--and have now
made this field "no duplicates".

Thanks so much for taking the time to answer this--and I apologize for being
another semi-trained access person trying to do more than she is trained
for!

Sara



Ken Snell said:
OK - perhaps your query needs to have the parameters explicitly cast as
long
integer values, so try this (I have used the CLng function to cast the
entered / read values as long integer type):

SELECT [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
Sum([Order Details Query].LineCostNoFrt) AS SumOfLineCostNoFrt, [Orders
Query for Orders Form].NegotiatedCommission,
CCur(nz([negotiatedcommission])*[sumoflinecostnofrt]) AS NegCommAmt,
[Orders
Query for Orders Form].FlatCommission,
CCur(nz([flatcommission])+nz([negcommamt])) AS CommissionDue, [Orders
Query
for Orders Form].CommissionPaid
FROM [Order Details Query] INNER JOIN [Orders Query for Orders Form] ON
[Order Details Query].OrderID = [Orders Query for Orders Form].OrderID
GROUP BY [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
[Orders
Query for Orders Form].NegotiatedCommission, [Orders Query for Orders
Form].FlatCommission, [Orders Query for Orders Form].CommissionPaid
HAVING ((([Orders Query for Orders Form].BrokerOrderNumber) Between
CLng([forms]![vendorbondialog]![txtbeginningbon]) And
CLng([forms]![vendorbondialog]![txtendingbon])))
ORDER BY [Orders Query for Orders Form].BillingDate;

--

Ken Snell
<MS ACCESS MVP>

Sara Mellen said:
I didn't write the sql...just did the query like any other partly competent
user who doesn't know sql! But here's the sql that Access wrote for me.
Keep in mind that the BrokerOrderNumber criteria is here listed as the
contents of some text boxes on a dialog box--it doesn't make any difference.
I get the same error when I do a regular parameter query and also when I
just punch in the actual numbers. I should say one more thing--the
source
for this query is two other queries. I went back and built the query
from
scratch (just using the tables, not other queries) but the results were the
same.

SELECT [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
Sum([Order Details Query].LineCostNoFrt) AS SumOfLineCostNoFrt, [Orders
Query for Orders Form].NegotiatedCommission,
CCur(nz([negotiatedcommission])*[sumoflinecostnofrt]) AS NegCommAmt, [Orders
Query for Orders Form].FlatCommission,
CCur(nz([flatcommission])+nz([negcommamt])) AS CommissionDue, [Orders Query
for Orders Form].CommissionPaid
FROM [Order Details Query] INNER JOIN [Orders Query for Orders Form] ON
[Order Details Query].OrderID = [Orders Query for Orders Form].OrderID
GROUP BY [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate, [Orders
Query for Orders Form].NegotiatedCommission, [Orders Query for Orders
Form].FlatCommission, [Orders Query for Orders Form].CommissionPaid
HAVING ((([Orders Query for Orders Form].BrokerOrderNumber) Between
[forms]![vendorbondialog]![txtbeginningbon] And
[forms]![vendorbondialog]![txtendingbon]))
ORDER BY [Orders Query for Orders Form].BillingDate;







Ken Snell said:
Also..post the SQL statement that you're using as the query.

--

Ken Snell
<MS ACCESS MVP>

No, that was my first thought. It's long integer. Weird, huh?


Any chance that the field in which you're using BrokerOrderNumber field
is
set to Integer data type? Try changing that field to Long Integer.

--

Ken Snell
<MS ACCESS MVP>

I have a query (which leads to a report) that has been working
great
for
about 3 years. One of the fields in the ORDERS table is the
BrokerOrderNumber, a long integer field which has been increasing
through
the years. The problem is that as soon as we reached
BrokerOrderNumber
33369, the query stopped working!

The query is made up of fields from two tables, ORDERS and ORDER
DETAILS,
linked through the ORDERID field.

When I do a query based on just one of the tables, everything works
fine,
but when I try to do a query based on fields from both tables, I
get
the
error, but ONLY if the query criteria includes the
BrokerOrderNumber
33369
and above! It works fine on all lower numbers.

I get two kinds of errors--if I run the query as a parameter query, I
get
the error message "The expression is typed incorrectly, or it is
too
compex
to be evaluated. etc." If I type in the criteria (between 33000
and
34000,
for example), I get the "Data type mismatch in criteria
expression."
i
have
checked EVERYTHING--data types, missing number, and I'm completely
stuck.

I hate to completely revamp my numbering system--anybody have an idea
why
this is acting this way?

Sara Mellen
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

ahhhhh.... user error.... that is also a common source of many problems....

Glad you found the answer.
--

Ken Snell
<MS ACCESS MVP>

Sara Mellen said:
I've solved this, but not in the way you suggested because, as I was totally
stressing and beating my head against a wall, I discovered that the guy who
was entering the orders had re-used broker order numbers. When he made a
mistake, instead of correcting it he just created another order. This is
not generally a problem--I had not made the field "no duplicates"--but I
hadn't foreseen that this would cause the link between the orders table and
the orders detail table to mess up. i see how it happened--and have now
made this field "no duplicates".

Thanks so much for taking the time to answer this--and I apologize for being
another semi-trained access person trying to do more than she is trained
for!

Sara



Ken Snell said:
OK - perhaps your query needs to have the parameters explicitly cast as
long
integer values, so try this (I have used the CLng function to cast the
entered / read values as long integer type):

SELECT [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
Sum([Order Details Query].LineCostNoFrt) AS SumOfLineCostNoFrt, [Orders
Query for Orders Form].NegotiatedCommission,
CCur(nz([negotiatedcommission])*[sumoflinecostnofrt]) AS NegCommAmt,
[Orders
Query for Orders Form].FlatCommission,
CCur(nz([flatcommission])+nz([negcommamt])) AS CommissionDue, [Orders
Query
for Orders Form].CommissionPaid
FROM [Order Details Query] INNER JOIN [Orders Query for Orders Form] ON
[Order Details Query].OrderID = [Orders Query for Orders Form].OrderID
GROUP BY [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
[Orders
Query for Orders Form].NegotiatedCommission, [Orders Query for Orders
Form].FlatCommission, [Orders Query for Orders Form].CommissionPaid
HAVING ((([Orders Query for Orders Form].BrokerOrderNumber) Between
CLng([forms]![vendorbondialog]![txtbeginningbon]) And
CLng([forms]![vendorbondialog]![txtendingbon])))
ORDER BY [Orders Query for Orders Form].BillingDate;

--

Ken Snell
<MS ACCESS MVP>

Sara Mellen said:
I didn't write the sql...just did the query like any other partly competent
user who doesn't know sql! But here's the sql that Access wrote for me.
Keep in mind that the BrokerOrderNumber criteria is here listed as the
contents of some text boxes on a dialog box--it doesn't make any difference.
I get the same error when I do a regular parameter query and also when I
just punch in the actual numbers. I should say one more thing--the
source
for this query is two other queries. I went back and built the query
from
scratch (just using the tables, not other queries) but the results were the
same.

SELECT [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate,
Sum([Order Details Query].LineCostNoFrt) AS SumOfLineCostNoFrt, [Orders
Query for Orders Form].NegotiatedCommission,
CCur(nz([negotiatedcommission])*[sumoflinecostnofrt]) AS NegCommAmt, [Orders
Query for Orders Form].FlatCommission,
CCur(nz([flatcommission])+nz([negcommamt])) AS CommissionDue, [Orders Query
for Orders Form].CommissionPaid
FROM [Order Details Query] INNER JOIN [Orders Query for Orders Form] ON
[Order Details Query].OrderID = [Orders Query for Orders Form].OrderID
GROUP BY [Orders Query for Orders Form].OrderID, [Orders Query for Orders
Form].BrokerOrderNumber, [Orders Query for Orders Form].VendorID, [Orders
Query for Orders Form].PurchaseOrderNumber, [Orders Query for Orders
Form].CustomerID, [Orders Query for Orders Form].Vendors.CompanyName,
[Orders Query for Orders Form].Customers.CompanyName, [Orders Query for
Orders Form].BillingDate, [Orders Query for Orders Form].OrderDate, [Orders
Query for Orders Form].NegotiatedCommission, [Orders Query for Orders
Form].FlatCommission, [Orders Query for Orders Form].CommissionPaid
HAVING ((([Orders Query for Orders Form].BrokerOrderNumber) Between
[forms]![vendorbondialog]![txtbeginningbon] And
[forms]![vendorbondialog]![txtendingbon]))
ORDER BY [Orders Query for Orders Form].BillingDate;







Also..post the SQL statement that you're using as the query.

--

Ken Snell
<MS ACCESS MVP>

No, that was my first thought. It's long integer. Weird, huh?


Any chance that the field in which you're using BrokerOrderNumber field
is
set to Integer data type? Try changing that field to Long Integer.

--

Ken Snell
<MS ACCESS MVP>

I have a query (which leads to a report) that has been working
great
for
about 3 years. One of the fields in the ORDERS table is the
BrokerOrderNumber, a long integer field which has been increasing
through
the years. The problem is that as soon as we reached
BrokerOrderNumber
33369, the query stopped working!

The query is made up of fields from two tables, ORDERS and ORDER
DETAILS,
linked through the ORDERID field.

When I do a query based on just one of the tables, everything works
fine,
but when I try to do a query based on fields from both tables, I
get
the
error, but ONLY if the query criteria includes the
BrokerOrderNumber
33369
and above! It works fine on all lower numbers.

I get two kinds of errors--if I run the query as a parameter
Click to expand...
query,
I
get
the error message "The expression is typed incorrectly, or it is
too
compex
to be evaluated. etc." If I type in the criteria (between 33000
and
34000,
for example), I get the "Data type mismatch in criteria
expression."
i
have
checked EVERYTHING--data types, missing number, and I'm completely
stuck.

I hate to completely revamp my numbering system--anybody have an idea
why
this is acting this way?

Sara Mellen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use In() Expression as Query criteria with guts from Function 1
Delete Query: Could not delete from specified tables 0
Access 2007 syntax error 1
calculating average days 1
Parameterised query to return all records or those Between two val 2
Error when using criteria to prompt for input 4
Aggregate calculations on calculated query fields? 6
formula in query criteria 2

Top