If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error message: Number 0x800CCCF0F | [email protected] | Outlook Express | 1 | September 2nd, 2004 09:32 PM |
OE Script Error when printing | jabba | Outlook Express | 2 | September 1st, 2004 03:57 PM |
Troubleshoot the SendObject method | Nikky_Pickles | General Discussion | 1 | August 30th, 2004 07:40 AM |
Error message number 0x800ccc0f | chris | General Discussion | 1 | August 26th, 2004 05:59 AM |
No More Custom Number Format ERROR | James | Worksheet Functions | 0 | September 18th, 2003 03:13 PM |