You really don't want to do that, you'd force the AS/400 to run the digits
command for each row before comparing it. If there is an index that it could
use you just threw it out the window.

Mike, the statement you showed won't allow you to select records, it creates
a view. To select records you don't want the 'create view as' part. Can you
post the surrounding VBA code? I haven't hit any problems with numeric
fields in a long time. What versions are involved? (AS/400, Client Access,
ADO (you are using ADO, no?) and PC Operating system?)


Walden H Leverich III
Tech Software
(516)627-3800 x11

-----Original Message-----
From: Bill []
Sent: Wednesday, December 26, 2001 10:57
Subject: Re: Visual Basic SQl statement


Does it work correctly in MS Query, but throws an error when doing the
download to your application?  If so, this seems to be an error in MS's
products; they seem to cast the parameter's data type incorrectly.  Usually
when I've had problems it's because I'm trying to use a parameterized query
with a numeric field being one of the parameters.  This doesn't seem to be
your case, but I'll share what I do anyways.  Use the Digits() function to
turn your numeric field into an alpha/numeric field and then compare to a
character value.

In your case, the Where statement should be changed to:
Digits(PZBSB) > '00000000'


> I'm trying to run a sql statement using ODBC through a VBA.  The
> Database on the 400 has a Numeric field defined 8.0p(used as a date
> field). i've created a SQL statement that lets me select records from
> the file, however, when i try to condition the select based on the
> date field i get cryptic errors.
> Here is my select statement.
>   SQLstr = "create view DISPATCH_V as select * from UFDB,UFDBS,UCSRFD
> where PZBSB = P1BSB and P1MTR = PWMTR and PZBSB > 0"

Do You Yahoo!?
Get your free address at

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: To subscribe, unsubscribe,
or change list options,
or email:
Before posting, please take a moment to review the archives

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.