RE: Visual Basic SQl statement

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

------------
Walden H Leverich III
President
Tech Software
(516)627-3800 x11
WaldenL@TechSoftInc.com
http://www.TechSoftInc.com



-----Original Message-----
From: Bill [mailto:brobins3d@yahoo.com]
Sent: Wednesday, December 26, 2001 10:57
To: midrange-l@midrange.com
Subject: Re: Visual Basic SQl statement


Mike,

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'

Bill

> 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 @yahoo.com address at http://mail.yahoo.com

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.