SQL query help

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I have this SQL
SELECT QryHisacctable.CORP, QryHisacctable.DIVISION,
QryHisacctable.FACILITY, QryHisacctable.DST_CNTR,
QryHisacctable.PCC_SMIC, QryHisacctable.CORP_ITEM_CD,
QryHisacctable.DESC_ITEM, QryHisacctable.VEND_NUM,
QryHisacctable.NAME, QryHisacctable.PROD_CLASS,
QryHisacctable.STATUS_CORP, QryHisacctable.SIZE,
QryHisacctable.PACK_WHSE, QryHisacctable.WHSE_WGHT,
QryHisacctable.VEN_UPC_NUM_SYS, QryHisacctable.VEND_CONV_FCTR,
QryHisacctable.VEN_UPC_MANUF, QryHisacctable.VEN_UPC_ITEM,
QryHisacctable.VEN_UPC_CHECK, QryHisacctable.SIZE_NUM,
QryHisacctable.SIZE_UOM, QryHisacctable.WEIGHT_UOM,
QryHisacctable.INNER_PACK, QryHisacctable.MANUF_TYPE_IND,
QryHisacctable.GROUP_CD, QryHisacctable.CTGRY_CD,
QryHisacctable.CLASS_CD, QryHisacctable.SUB_CLASS_CD,
QryHisacctable.SUBSB_CLASS_CD, QryHisacctable.PALLET_LAYER,
QryHisacctable.PALLET_HGHT, QryHisacctable.UPC_MANUF,
QryHisacctable.UPC_SALES, QryHisacctable.UPC_COUNTRY,
QryHisacctable.UPC_SYSTEM, QryHisacctable.PACK_DESC,
QryHisacctable.SIZE_DESC, QryHisacctable.SumOfSHIP,
QryHisacctable.COST_VEND, QryHisacctable.COST_IB, [sumofSHIP]/
[VEND_CONV_FCTR] AS Master_Case, ([master_case]*[cost_vend]) AS Spend,
QryHisacctable.YEAR_WEEK, Right([year_week],2) AS Week, Left
([year_week],4) AS [Year], QryHisacctable.MIN_ORD,
QryHisacctable.MAX_ORD, QryHisacctable.CARRIER_NAME,
QryHisacctable.SHIPPING_POINT, QryHisacctable.STATUS_DST INTO
tblssimsvolumes
FROM QryHisacctable
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND ((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));

I have unbound comboboxes on a form where the criteria selected in the
comboboxes will go into the appropriate field in this query above and
filter the desired results. this works when I have one WHERE statement
in the query i.e. corp_item_cd, if this is the only where in the query
then the query runs fine, but when I add multiple where statements
like in the SQL above then I get this error.

ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)

what does this mean? and why does it happen when I put more than one
where statement in the query?

why can't I have numerous Where statements?
 
I have this SQL
Click to expand...
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND ((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));

I have unbound comboboxes on a form where the criteria selected in the
comboboxes will go into the appropriate field in this query above and
filter the desired results. this works when I have one WHERE statement
in the query i.e. corp_item_cd, if this is the only where in the query
then the query runs fine, but when I add multiple where statements
like in the SQL above then I get this error.

ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)

what does this mean? and why does it happen when I put more than one
where statement in the query?

why can't I have numerous Where statements?
Click to expand...

Could you show us what you mean by "multiple where statements"? A query
can only have a single WHERE clause. I'm pretty sure you would get an
invalid syntax or invalid token error if you attempted to use the word
WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the WHERE
statement shown above has multiple criteria ... is this an example of a
query that fails?
The "invalid precision value" means that there is a datatype problem in
at least one of your comparisons. In order to get to the bottom of it,
you would need to show us the datatypes of all the fields in the WHERE
clause whose use causes the error.

FYI, numeric datatypes in Access do not have precision and scale the way
they do in IBM DB2. Precision is defined as the maximum number of digits
allowed in the number, and scale is defined as the number of digits
allowed to be used in the decimal portion, i.e., the maximum number of
decimal places. Precision and scale together determine the maximum value
that can be stored in the location. For example, the largest value
allowed with precision 5 and scale 2, typically written as numeric(5,2)
or decimal(5,2) is 999.99. Larger numbers are not allowed and the
attempt to do so will typically cause an overflow error.

Values passed via ODBC to DB2 for comparison with values stored in DB2
fields need to be converted by the ODBC driver to the appropriate
datatypes required by DB2. Your error suggests an inability to do so.
Perhaps it would help if you declared those form references as
parameters and assigned them explicit datatypes. You can do this via the
Query>Parameters menu option in Design View or by using a PARAMETERS
clause in SQL View:

PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
 
I really appreciate your input. What I meant by where statements where
the multiple where criterion. I went to parameters and put what you
told me to put in there. I downloaded a query onto a table for a test
and I went to the design view of that table. I went to each field and
noted what data type they were, i.e text, decimal, etc then made each
parameter listed match the data type. Is that correct to do?

[email protected] said:
I have this SQL
Click to expand...
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND ((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));
Click to expand...
I have unbound comboboxes on a form where the criteria selected in the
comboboxes will go into the appropriate field in this query above and
filter the desired results. this works when I have one WHERE statement
in the query i.e. corp_item_cd, if this is the only where in the query
then the query runs fine, but when I add multiple where statements
like in the SQL above then I get this error.
Click to expand...
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
Click to expand...
what does this mean? and why does it happen when I put more than one
where statement in the query?
Click to expand...
why can't I have numerous Where statements?
Click to expand...

Could you show us what you mean by "multiple where statements"? A query
can only have a single WHERE clause. I'm pretty sure you would get an
invalid syntax or invalid token error if you attempted to use the word
WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the WHERE
statement shown above has multiple criteria ... is this an example of a
query that fails?
The "invalid precision value" means that there is a datatype problem in
at least one of your comparisons. In order to get to the bottom of it,
you would need to show us the datatypes of all the fields in the WHERE
clause whose use causes the error.

FYI, numeric datatypes in Access do not have precision and scale the way
they do in IBM DB2. Precision is defined as the maximum number of digits
allowed in the number, and scale is defined as the number of digits
allowed to be used in the decimal portion, i.e., the maximum number of
decimal places. Precision and scale together determine the maximum value
that can be stored in the location. For example, the largest value
allowed with precision 5 and scale 2, typically written as numeric(5,2)
or decimal(5,2) is 999.99. Larger numbers are not allowed and the
attempt to do so will typically cause an overflow error.

Values passed via ODBC to DB2 for comparison with values stored in DB2
fields need to be converted by the ODBC driver to the appropriate
datatypes required by DB2. Your error suggests an inability to do so.
Perhaps it would help if you declared those form references as
parameters and assigned them explicit datatypes. You can do this via the
Query>Parameters menu option in Design View or by using a PARAMETERS
clause in SQL View:

PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
Click to expand...
 
I put in the parameters
[Forms]![frmSSIMSPulls]![cboxDivision] TEXT
[Forms]![frmSSIMSPulls]![cboxDC] TEXT
[Forms]![frmSSIMSPulls]![cboxSMIC] LONG
[Forms]![frmSSIMSPulls]![cboxItem] LONG

It doesn't give that error anymore but no data gets pulled when I run
the query. Any ideas?

[email protected] said:
I have this SQL
Click to expand...
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND ((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));
Click to expand...
I have unbound comboboxes on a form where the criteria selected in the
comboboxes will go into the appropriate field in this query above and
filter the desired results. this works when I have one WHERE statement
in the query i.e. corp_item_cd, if this is the only where in the query
then the query runs fine, but when I add multiple where statements
like in the SQL above then I get this error.
Click to expand...
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
Click to expand...
what does this mean? and why does it happen when I put more than one
where statement in the query?
Click to expand...
why can't I have numerous Where statements?
Click to expand...

Could you show us what you mean by "multiple where statements"? A query
can only have a single WHERE clause. I'm pretty sure you would get an
invalid syntax or invalid token error if you attempted to use the word
WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the WHERE
statement shown above has multiple criteria ... is this an example of a
query that fails?
The "invalid precision value" means that there is a datatype problem in
at least one of your comparisons. In order to get to the bottom of it,
you would need to show us the datatypes of all the fields in the WHERE
clause whose use causes the error.

FYI, numeric datatypes in Access do not have precision and scale the way
they do in IBM DB2. Precision is defined as the maximum number of digits
allowed in the number, and scale is defined as the number of digits
allowed to be used in the decimal portion, i.e., the maximum number of
decimal places. Precision and scale together determine the maximum value
that can be stored in the location. For example, the largest value
allowed with precision 5 and scale 2, typically written as numeric(5,2)
or decimal(5,2) is 999.99. Larger numbers are not allowed and the
attempt to do so will typically cause an overflow error.

Values passed via ODBC to DB2 for comparison with values stored in DB2
fields need to be converted by the ODBC driver to the appropriate
datatypes required by DB2. Your error suggests an inability to do so.
Perhaps it would help if you declared those form references as
parameters and assigned them explicit datatypes. You can do this via the
Query>Parameters menu option in Design View or by using a PARAMETERS
clause in SQL View:

PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
Click to expand...
 
I put in the parameters
[Forms]![frmSSIMSPulls]![cboxDivision] TEXT
[Forms]![frmSSIMSPulls]![cboxDC] TEXT
[Forms]![frmSSIMSPulls]![cboxSMIC] LONG
[Forms]![frmSSIMSPulls]![cboxItem] LONG

It doesn't give that error anymore but no data gets pulled when I run
the query. Any ideas?


Here's SQL with parameters.


PARAMETERS [Forms]![frmSSIMSPulls]![cboxDivision] Text ( 255 ),
[Forms]![frmSSIMSPulls]![cboxDC] Text ( 255 ), [Forms]![frmSSIMSPulls]!
[cboxSMIC] Long, [Forms]![frmSSIMSPulls]![cboxItem] Long;
SELECT QryHisacctable.CORP, QryHisacctable.DIVISION,
QryHisacctable.FACILITY, QryHisacctable.DST_CNTR,
QryHisacctable.PCC_SMIC, QryHisacctable.CORP_ITEM_CD,
QryHisacctable.DESC_ITEM, QryHisacctable.VEND_NUM,
QryHisacctable.NAME, QryHisacctable.PROD_CLASS,
QryHisacctable.STATUS_CORP, QryHisacctable.SIZE,
QryHisacctable.PACK_WHSE, QryHisacctable.WHSE_WGHT,
QryHisacctable.VEN_UPC_NUM_SYS, QryHisacctable.VEND_CONV_FCTR,
QryHisacctable.VEN_UPC_MANUF, QryHisacctable.VEN_UPC_ITEM,
QryHisacctable.VEN_UPC_CHECK, QryHisacctable.SIZE_NUM,
QryHisacctable.SIZE_UOM, QryHisacctable.WEIGHT_UOM,
QryHisacctable.INNER_PACK, QryHisacctable.MANUF_TYPE_IND,
QryHisacctable.GROUP_CD, QryHisacctable.CTGRY_CD,
QryHisacctable.CLASS_CD, QryHisacctable.SUB_CLASS_CD,
QryHisacctable.SUBSB_CLASS_CD, QryHisacctable.PALLET_LAYER,
QryHisacctable.PALLET_HGHT, QryHisacctable.UPC_MANUF,
QryHisacctable.UPC_SALES, QryHisacctable.UPC_COUNTRY,
QryHisacctable.UPC_SYSTEM, QryHisacctable.PACK_DESC,
QryHisacctable.SIZE_DESC, QryHisacctable.SumOfSHIP,
QryHisacctable.COST_VEND, QryHisacctable.COST_IB, [sumofSHIP]/
[VEND_CONV_FCTR] AS Master_Case, ([master_case]*[cost_vend]) AS Spend,
QryHisacctable.YEAR_WEEK, Right([year_week],2) AS Week, Left
([year_week],4) AS [Year], QryHisacctable.MIN_ORD,
QryHisacctable.MAX_ORD, QryHisacctable.CARRIER_NAME,
QryHisacctable.SHIPPING_POINT, QryHisacctable.STATUS_DST INTO
tblssimsvolumes
FROM QryHisacctable
WHERE (((QryHisacctable.DIVISION)=Forms!frmSSIMSPulls!cboxDivision)
And ((QryHisacctable.DST_CNTR)=Forms!frmSSIMSPulls!cboxDC) And
((QryHisacctable.PCC_SMIC)=Forms!frmSSIMSPulls!cboxSMIC) And
((QryHisacctable.CORP_ITEM_CD)=Forms!frmSSIMSPulls!cboxItem));


I really appreciate your input. What I meant by where statements where
the multiple where criterion. I went to parameters and put what you
told me to put in there. I downloaded a query onto a table for a test
and I went to the design view of that table. I went to each field and
noted what data type they were, i.e text, decimal, etc then made each
parameter listed match the data type. Is that correct to do?

[email protected] said:
I have this SQL
Click to expand...
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND ((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));
I have unbound comboboxes on a form where the criteria selected in the
comboboxes will go into the appropriate field in this query above and
filter the desired results. this works when I have one WHERE statement
in the query i.e. corp_item_cd, if this is the only where in the query
then the query runs fine, but when I add multiple where statements
like in the SQL above then I get this error.
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
what does this mean? and why does it happen when I put more than one
where statement in the query?
why can't I have numerous Where statements?
Click to expand...
Click to expand...
Could you show us what you mean by "multiple where statements"? A query
can only have a single WHERE clause. I'm pretty sure you would get an
invalid syntax or invalid token error if you attempted to use the word
WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the WHERE
statement shown above has multiple criteria ... is this an example of a
query that fails?
The "invalid precision value" means that there is a datatype problem in
at least one of your comparisons. In order to get to the bottom of it,
you would need to show us the datatypes of all the fields in the WHERE
clause whose use causes the error.
Click to expand...
FYI, numeric datatypes in Access do not have precision and scale the way
they do in IBM DB2. Precision is defined as the maximum number of digits
allowed in the number, and scale is defined as the number of digits
allowed to be used in the decimal portion, i.e., the maximum number of
decimal places. Precision and scale together determine the maximum value
that can be stored in the location. For example, the largest value
allowed with precision 5 and scale 2, typically written as numeric(5,2)
or decimal(5,2) is 999.99. Larger numbers are not allowed and the
attempt to do so will typically cause an overflow error.
Click to expand...
Values passed via ODBC to DB2 for comparison with values stored in DB2
fields need to be converted by the ODBC driver to the appropriate
datatypes required by DB2. Your error suggests an inability to do so.
Perhaps it would help if you declared those form references as
parameters and assigned them explicit datatypes. You can do this via the
Query>Parameters menu option in Design View or by using a PARAMETERS
clause in SQL View:
Click to expand...
PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
Click to expand...
Click to expand...
 
Not without knowing the source data and the values being used to filter
it. Can you show us a few rows of sample data, eliminating all but the
relevant fields, then show us the values being entered in your form
controls and the rows you expect your query to retrieve from those
sample rows?

I put in the parameters
[Forms]![frmSSIMSPulls]![cboxDivision] TEXT
[Forms]![frmSSIMSPulls]![cboxDC] TEXT
[Forms]![frmSSIMSPulls]![cboxSMIC] LONG
[Forms]![frmSSIMSPulls]![cboxItem] LONG

It doesn't give that error anymore but no data gets pulled when I run
the query. Any ideas?

[email protected] said:
I have this SQL
Click to expand...
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND ((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));
Click to expand...
I have unbound comboboxes on a form where the criteria selected in
the comboboxes will go into the appropriate field in this query
above and filter the desired results. this works when I have one
WHERE statement in the query i.e. corp_item_cd, if this is the only
where in the query then the query runs fine, but when I add
multiple where statements like in the SQL above then I get this
error.
Click to expand...
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
Click to expand...
what does this mean? and why does it happen when I put more than one
where statement in the query?
Click to expand...
why can't I have numerous Where statements?
Click to expand...

Could you show us what you mean by "multiple where statements"? A
query can only have a single WHERE clause. I'm pretty sure you would
get an invalid syntax or invalid token error if you attempted to use
the word WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the
WHERE statement shown above has multiple criteria ... is this an
example of a query that fails?
The "invalid precision value" means that there is a datatype problem
in at least one of your comparisons. In order to get to the bottom
of it, you would need to show us the datatypes of all the fields in
the WHERE clause whose use causes the error.

FYI, numeric datatypes in Access do not have precision and scale the
way they do in IBM DB2. Precision is defined as the maximum number
of digits allowed in the number, and scale is defined as the number
of digits allowed to be used in the decimal portion, i.e., the
maximum number of decimal places. Precision and scale together
determine the maximum value that can be stored in the location. For
example, the largest value allowed with precision 5 and scale 2,
typically written as numeric(5,2) or decimal(5,2) is 999.99. Larger
numbers are not allowed and the attempt to do so will typically
cause an overflow error.

Values passed via ODBC to DB2 for comparison with values stored in
DB2 fields need to be converted by the ODBC driver to the appropriate
datatypes required by DB2. Your error suggests an inability to do so.
Perhaps it would help if you declared those form references as
parameters and assigned them explicit datatypes. You can do this via
the Query>Parameters menu option in Design View or by using a
PARAMETERS clause in SQL View:

PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
Click to expand...
Click to expand...
 
it would be nice if I could copy and paste pictures but I'll try to
explain the best I know how.


query results pulled from database (lets say its all 1,000,000
records) here's an example

Division Item Desc Vend
Name Volume Date
32 123456 Widgets 1241 Widget
Town 250,000 1/2/2007
32 123456 Widgets 1241 Widget
Town 50,000 2/5/2007
33 123456 Widgets 1241 Widget
Town 20,000 1/26/2007
33 123456 Widgets 1241 Widget
Town 260,000 2/12/2007
35 123456 Widgets 1241 Widget
Town 260,000 1/18/2007
35 123456 Widgets 1241 Widget
Town 260,000 2/14/2007
35 135426 Applets 1241 Apples R
US 45,000 1/19/2007
35 135426 Applets 1241 Apples R
US 18,000 3/14/2007

etc to 1,000,000 records of information

the form shows the query data in the from detail section where all
information is shown. In the form header I have combo boxes

cboxDivision, cboxItem, cboxVend txtStartdate and txtEnddate
(for date range lookup)

so if I click cboxitem and select item 123456 and click cboxDivision
and select division 32 and hit the run query button that will filter
the results I SHOULD get this as a result. But I don't.

32 123456 Widgets 1241 Widget
Town 250,000 1/2/2007
32 123456 Widgets 1241 Widget
Town 50,000 2/5/2007

in the query I have the fields linked to the form combo boxes as such.



field: CORP_ITEM_CD
table: QryHisacctable
criteria: [Forms]![frmSSIMSPulls]![cboxItem]

I have each field linked to each combo box. Here is the Where in the
SQL
WHERE (((QryHisacctable.CORP_ITEM_CD)=[Forms]![frmSSIMSPulls]!
[cboxItem]));

currently I have no parameters listed in the query. Back on the form
when I run this query when I type in an item code in the combo box the
query runs fine and the correct results come back for that item
number. But when I add another criterion to the WHERE such as

WHERE (((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]![cboxSMIC])
AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]![frmSSIMSPulls]!
[cboxItem]));

The filter will not work and i get the error

ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)

So with your advice I went to Parameters and put in these two for the
two criterion.

[Forms]![frmSSIMSPulls]![cboxItem] Long
[Forms]![frmSSIMSPulls]![cboxSMIC] Long

When I run the query now I don't get the error but I don't get any
results back. Does this help or do you need anything else?


Not without knowing the source data and the values being used to filter
it. Can you show us a few rows of sample data, eliminating all but the
relevant fields, then show us the values being entered in your form
controls and the rows you expect your query to retrieve from those
sample rows?



[email protected] said:
I put in the parameters
[Forms]![frmSSIMSPulls]![cboxDivision] TEXT
[Forms]![frmSSIMSPulls]![cboxDC] TEXT
[Forms]![frmSSIMSPulls]![cboxSMIC] LONG
[Forms]![frmSSIMSPulls]![cboxItem] LONG
Click to expand...
It doesn't give that error anymore but no data gets pulled when I run
the query. Any ideas?
Click to expand...
(e-mail address removed) wrote:
I have this SQL
<snip>
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND ((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));
I have unbound comboboxes on a form where the criteria selected in
the comboboxes will go into the appropriate field in this query
above and filter the desired results. this works when I have one
WHERE statement in the query i.e. corp_item_cd, if this is the only
where in the query then the query runs fine, but when I add
multiple where statements like in the SQL above then I get this
error.
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
what does this mean? and why does it happen when I put more than one
where statement in the query?
why can't I have numerous Where statements?
Could you show us what you mean by "multiple where statements"? A
query can only have a single WHERE clause. I'm pretty sure you would
get an invalid syntax or invalid token error if you attempted to use
the word WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the
WHERE statement shown above has multiple criteria ... is this an
example of a query that fails?
The "invalid precision value" means that there is a datatype problem
in at least one of your comparisons. In order to get to the bottom
of it, you would need to show us the datatypes of all the fields in
the WHERE clause whose use causes the error.
FYI, numeric datatypes in Access do not have precision and scale the
way they do in IBM DB2. Precision is defined as the maximum number
of digits allowed in the number, and scale is defined as the number
of digits allowed to be used in the decimal portion, i.e., the
maximum number of decimal places. Precision and scale together
determine the maximum value that can be stored in the location. For
example, the largest value allowed with precision 5 and scale 2,
typically written as numeric(5,2) or decimal(5,2) is 999.99. Larger
numbers are not allowed and the attempt to do so will typically
cause an overflow error.
Values passed via ODBC to DB2 for comparison with values stored in
DB2 fields need to be converted by the ODBC driver to the appropriate
datatypes required by DB2. Your error suggests an inability to do so.
Perhaps it would help if you declared those form references as
parameters and assigned them explicit datatypes. You can do this via
the Query>Parameters menu option in Design View or by using a
PARAMETERS clause in SQL View:
PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry, but I would need to work with this first-hand to get to the
bottom of it. If you hard-code the values instead of using the form
controls, does the query return what you want?


it would be nice if I could copy and paste pictures but I'll try to
explain the best I know how.


query results pulled from database (lets say its all 1,000,000
records) here's an example

Division Item Desc Vend
Name Volume Date
32 123456 Widgets 1241 Widget
Town 250,000 1/2/2007
32 123456 Widgets 1241 Widget
Town 50,000 2/5/2007
33 123456 Widgets 1241 Widget
Town 20,000 1/26/2007
33 123456 Widgets 1241 Widget
Town 260,000 2/12/2007
35 123456 Widgets 1241 Widget
Town 260,000 1/18/2007
35 123456 Widgets 1241 Widget
Town 260,000 2/14/2007
35 135426 Applets 1241 Apples R
US 45,000 1/19/2007
35 135426 Applets 1241 Apples R
US 18,000 3/14/2007

etc to 1,000,000 records of information

the form shows the query data in the from detail section where all
information is shown. In the form header I have combo boxes

cboxDivision, cboxItem, cboxVend txtStartdate and txtEnddate
(for date range lookup)

so if I click cboxitem and select item 123456 and click cboxDivision
and select division 32 and hit the run query button that will filter
the results I SHOULD get this as a result. But I don't.

32 123456 Widgets 1241 Widget
Town 250,000 1/2/2007
32 123456 Widgets 1241 Widget
Town 50,000 2/5/2007

in the query I have the fields linked to the form combo boxes as such.



field: CORP_ITEM_CD
table: QryHisacctable
criteria: [Forms]![frmSSIMSPulls]![cboxItem]

I have each field linked to each combo box. Here is the Where in the
SQL
WHERE (((QryHisacctable.CORP_ITEM_CD)=[Forms]![frmSSIMSPulls]!
[cboxItem]));

currently I have no parameters listed in the query. Back on the form
when I run this query when I type in an item code in the combo box the
query runs fine and the correct results come back for that item
number. But when I add another criterion to the WHERE such as

WHERE (((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]![cboxSMIC])
AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]![frmSSIMSPulls]!
[cboxItem]));

The filter will not work and i get the error

ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)

So with your advice I went to Parameters and put in these two for the
two criterion.

[Forms]![frmSSIMSPulls]![cboxItem] Long
[Forms]![frmSSIMSPulls]![cboxSMIC] Long

When I run the query now I don't get the error but I don't get any
results back. Does this help or do you need anything else?


Not without knowing the source data and the values being used to
filter it. Can you show us a few rows of sample data, eliminating
all but the relevant fields, then show us the values being entered
in your form controls and the rows you expect your query to retrieve
from those sample rows?



[email protected] said:
I put in the parameters
[Forms]![frmSSIMSPulls]![cboxDivision] TEXT
[Forms]![frmSSIMSPulls]![cboxDC] TEXT
[Forms]![frmSSIMSPulls]![cboxSMIC] LONG
[Forms]![frmSSIMSPulls]![cboxItem] LONG
Click to expand...
It doesn't give that error anymore but no data gets pulled when I
run the query. Any ideas?
Click to expand...
(e-mail address removed) wrote:
I have this SQL
<snip>
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND
((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));
Click to expand...
I have unbound comboboxes on a form where the criteria selected in
the comboboxes will go into the appropriate field in this query
above and filter the desired results. this works when I have one
WHERE statement in the query i.e. corp_item_cd, if this is the
only where in the query then the query runs fine, but when I add
multiple where statements like in the SQL above then I get this
error.
Click to expand...
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
Click to expand...
what does this mean? and why does it happen when I put more than
one where statement in the query?
Click to expand...
why can't I have numerous Where statements?
Click to expand...
Could you show us what you mean by "multiple where statements"? A
query can only have a single WHERE clause. I'm pretty sure you
would get an invalid syntax or invalid token error if you
attempted to use the word WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the
WHERE statement shown above has multiple criteria ... is this an
example of a query that fails?
The "invalid precision value" means that there is a datatype
problem in at least one of your comparisons. In order to get to
the bottom of it, you would need to show us the datatypes of all
the fields in the WHERE clause whose use causes the error.
Click to expand...
FYI, numeric datatypes in Access do not have precision and scale
the way they do in IBM DB2. Precision is defined as the maximum
number of digits allowed in the number, and scale is defined as
the number of digits allowed to be used in the decimal portion,
i.e., the maximum number of decimal places. Precision and scale
together determine the maximum value that can be stored in the
location. For example, the largest value allowed with precision 5
and scale 2, typically written as numeric(5,2) or decimal(5,2) is
999.99. Larger numbers are not allowed and the attempt to do so
will typically cause an overflow error.
Click to expand...
Values passed via ODBC to DB2 for comparison with values stored in
DB2 fields need to be converted by the ODBC driver to the
appropriate datatypes required by DB2. Your error suggests an
inability to do so. Perhaps it would help if you declared those
form references as parameters and assigned them explicit
datatypes. You can do this via the Query>Parameters menu option in
Design View or by using a PARAMETERS clause in SQL View:
Click to expand...
PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
Click to expand...
Click to expand...
Click to expand...
 
Yes, when I hardcode Item Number in and other values in the query, the
correct filtered results come back on the form.

I'm sorry, but I would need to work with this first-hand to get to the
bottom of it. If you hard-code the values instead of using the form
controls, does the query return what you want?



[email protected] said:
it would be nice if I could copy and paste pictures but I'll try to
explain the best I know how.
Click to expand...
query results pulled from database (lets say its all 1,000,000
records) here's an example
Click to expand...
Division       Item           Desc              Vend
Name                Volume       Date
32            123456        Widgets         1241      Widget
Town       250,000     1/2/2007
32            123456        Widgets         1241      Widget
Town       50,000      2/5/2007
33            123456        Widgets         1241      Widget
Town       20,000      1/26/2007
33            123456        Widgets         1241      Widget
Town       260,000     2/12/2007
35            123456        Widgets         1241      Widget
Town       260,000     1/18/2007
35            123456        Widgets         1241      Widget
Town       260,000     2/14/2007
35            135426        Applets          1241      Apples R
US       45,000       1/19/2007
35            135426        Applets          1241      Apples R
US       18,000      3/14/2007
Click to expand...
etc to 1,000,000 records of information
Click to expand...
the form shows the query data in the from detail section where all
information is shown. In the form header I have combo boxes
Click to expand...
cboxDivision,   cboxItem,   cboxVend   txtStartdate and txtEnddate
(for date range lookup)
Click to expand...
so if I click cboxitem and select item 123456 and click cboxDivision
and select division 32 and hit the run query button that will filter
the results I SHOULD get this as a result. But I don't.
Click to expand...
32            123456        Widgets         1241      Widget
Town       250,000     1/2/2007
32            123456        Widgets         1241      Widget
Town       50,000      2/5/2007
Click to expand...
in the query I have the fields linked to the form combo boxes as such.
Click to expand...
field:     CORP_ITEM_CD
table:    QryHisacctable
criteria: [Forms]![frmSSIMSPulls]![cboxItem]
Click to expand...
I have each field linked to each combo box. Here is the Where in the
SQL
WHERE (((QryHisacctable.CORP_ITEM_CD)=[Forms]![frmSSIMSPulls]!
[cboxItem]));
Click to expand...
currently I have no parameters listed in the query. Back on the form
when I run this query when I type in an item code in the combo box the
query runs fine and the correct results come back for that item
number. But when I add another criterion to the WHERE such as
Click to expand...
WHERE (((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]![cboxSMIC])
AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]![frmSSIMSPulls]!
[cboxItem]));
Click to expand...
The filter will not work and i get the error
Click to expand...
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
Click to expand...
So with your advice I went to Parameters and put in these two for the
two criterion.
Click to expand...
[Forms]![frmSSIMSPulls]![cboxItem] Long
[Forms]![frmSSIMSPulls]![cboxSMIC] Long
Click to expand...
When I run the query now I don't get the error but I don't get any
results back. Does this help or do you need anything else?
Click to expand...
Not without knowing the source data and the values being used to
filter it. Can you show us a few rows of sample data, eliminating
all but the relevant fields, then show us the values being entered
in your form controls and the rows you expect your query to retrieve
from those sample rows?
(e-mail address removed) wrote:
I put in the parameters
[Forms]![frmSSIMSPulls]![cboxDivision] TEXT
[Forms]![frmSSIMSPulls]![cboxDC] TEXT
[Forms]![frmSSIMSPulls]![cboxSMIC] LONG
[Forms]![frmSSIMSPulls]![cboxItem] LONG
It doesn't give that error anymore but no data gets pulled when I
run the query. Any ideas?
(e-mail address removed) wrote:
I have this SQL
<snip>
WHERE (((QryHisacctable.DIVISION)=[Forms]![frmSSIMSPulls]!
[cboxDivision]) AND ((QryHisacctable.DST_CNTR)=[Forms]!
[frmSSIMSPulls]!
[cboxDC]) AND ((QryHisacctable.PCC_SMIC)=[Forms]![frmSSIMSPulls]!
[cboxSMIC]) AND ((QryHisacctable.CORP_ITEM_CD)=[Forms]!
[frmSSIMSPulls]!
[cboxItem]) AND
((QryHisacctable.VEND_NUM)=[Forms]![frmSSIMSPulls]!
[cboxVendor]));
I have unbound comboboxes on a form where the criteria selected in
the comboboxes will go into the appropriate field in this query
above and filter the desired results. this works when I have one
WHERE statement in the query i.e. corp_item_cd, if this is the
only where in the query then the query runs fine, but when I add
multiple where statements like in the SQL above then I get this
error.
ODBC--call failed
[IBM][CLI DRIVER]CLI0143E Invalid precision value. SQLSTATE =S1104
(#-99999)
what does this mean? and why does it happen when I put more than
one where statement in the query?
why can't I have numerous Where statements?
Could you show us what you mean by "multiple where statements"? A
query can only have a single WHERE clause. I'm pretty sure you
would get an invalid syntax or invalid token error if you
attempted to use the word WHERE twice or more in a query ...
Do you mean "multiple criteria in the WHERE statement"? If so, the
WHERE statement shown above has multiple criteria ... is this an
example of a query that fails?
The "invalid precision value" means that there is a datatype
problem in at least one of your comparisons. In order to get to
the bottom of it, you would need to show us the datatypes of all
the fields in the WHERE clause whose use causes the error.
FYI, numeric datatypes in Access do not have precision and scale
the way they do in IBM DB2. Precision is defined as the maximum
number of digits allowed in the number, and scale is defined as
the number of digits allowed to be used in the decimal portion,
i.e., the maximum number of decimal places. Precision and scale
together determine the maximum value that can be stored in the
location. For example, the largest value allowed with precision 5
and scale 2, typically written as numeric(5,2) or decimal(5,2) is
999.99. Larger numbers are not allowed and the attempt to do so
will typically cause an overflow error.
Values passed via ODBC to DB2 for comparison with values stored in
DB2 fields need to be converted by the ODBC driver to the
appropriate datatypes required by DB2. Your error suggests an
inability to do so. Perhaps it would help if you declared those
form references as parameters and assigned them explicit
datatypes. You can do this via the Query>Parameters menu option in
Design View or by using a PARAMETERS clause in SQL View:
PARAMETERS
[Forms]![frmSSIMSPulls]![cboxDC] Single
Click to expand...
Click to expand...
Click to expand...
 
Back
Top