Re: SQL query help
- From: ryan.fitzpatrick3@xxxxxxxxxxx
- Date: Thu, 4 Jun 2009 13:25:26 -0700 (PDT)
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?
On Jun 4, 12:06 pm, "Bob Barrows" <reb01...@xxxxxxxxxxxxxxx> wrote:
ryan.fitzpatri...@xxxxxxxxxxx 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
--
HTH,
Bob Barrows
.
- Follow-Ups:
- Re: SQL query help
- From: ryan . fitzpatrick3
- Re: SQL query help
- References:
- SQL query help
- From: ryan . fitzpatrick3
- Re: SQL query help
- From: Bob Barrows
- SQL query help
- Prev by Date: Re: text to date and criteria
- Next by Date: RE: Using the format function for a percent with four decimal plac
- Previous by thread: Re: SQL query help
- Next by thread: Re: SQL query help
- Index(es):
Relevant Pages
|
