Big number gives error!

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

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

"Sara Mellen" wrote in message
...
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

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


"Ken Snell [MVP]" wrote in message
...
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

"Sara Mellen" wrote in message
...
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

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

--

Ken Snell
MS ACCESS MVP

"Sara Mellen" wrote in message
...
No, that was my first thought. It's long integer. Weird, huh?


"Ken Snell [MVP]" wrote in message
...
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

"Sara Mellen" wrote in message
...
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

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

--

Ken Snell
MS ACCESS MVP

"Sara Mellen" wrote in message
...
No, that was my first thought. It's long integer. Weird, huh?


"Ken Snell [MVP]" wrote in message
...
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

"Sara Mellen" wrote in message
...
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

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 [MVP]" wrote in message
...
Also..post the SQL statement that you're using as the query.

--

Ken Snell
MS ACCESS MVP

"Sara Mellen" wrote in message
...
No, that was my first thought. It's long integer. Weird, huh?


"Ken Snell [MVP]" wrote in message
...
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

"Sara Mellen" wrote in message
...
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

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" wrote in message
...
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 [MVP]" wrote in message
...
Also..post the SQL statement that you're using as the query.

--

Ken Snell
MS ACCESS MVP

"Sara Mellen" wrote in message
...
No, that was my first thought. It's long integer. Weird, huh?


"Ken Snell [MVP]" wrote in message
...
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

"Sara Mellen" wrote in message
...
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

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 [MVP]" wrote in message
...
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" wrote in message
...
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 [MVP]" wrote in message
...
Also..post the SQL statement that you're using as the query.

--

Ken Snell
MS ACCESS MVP

"Sara Mellen" wrote in message
...
No, that was my first thought. It's long integer. Weird, huh?


"Ken Snell [MVP]" wrote in message
...
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

"Sara Mellen" wrote in message
...
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

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

Glad you found the answer.
--

Ken Snell
MS ACCESS MVP

"Sara Mellen" wrote in message
...
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 [MVP]" wrote in message
...
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" wrote in message
...
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 [MVP]" wrote in message
...
Also..post the SQL statement that you're using as the query.

--

Ken Snell
MS ACCESS MVP

"Sara Mellen" wrote in message
...
No, that was my first thought. It's long integer. Weird, huh?


"Ken Snell [MVP]" wrote in
message
...
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

"Sara Mellen" wrote in message
...
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