|
Yes, but it's not a date, it's an 8 position numeric field that the programmers use as a date. Big difference. -Walden ------------ Walden H Leverich III President Tech Software (516)627-3800 x11 WaldenL@TechSoftInc.com http://www.TechSoftInc.com -----Original Message----- From: Brad Jensen [mailto:brad@elstore.com] Sent: Wednesday, December 26, 2001 14:27 To: midrange-l@midrange.com Subject: Re: Visual Basic SQl statement If that date was a PC SQL date, you would want #20021211# ,as best I remember. Don't know what happens when you go to the AS/400 thru ODBC or OLE The # at the start and end are field delimiters like quotes for text, that say this is a date "Walden H. Leverich" wrote: > > Gee, this will be a pain to debug, it's littered with the COM objects > from the mapping program, what fun! <G>. First, for the record, you > are using VB not VBA, VBA is a stripped down version of VB. > > As for debugging, I do see two problems. First: the way you are > attempting to calculate the date won't work. Given your code: > > >CurrDate = CStr(MyYear) + CStr(MyMonth) + CStr(MyDay) > > IF MyMonth >=10 and MyDay >=10 all will be good. For example, a date > of 12/26/2001 would end up as 20011231 which gives you your date. > However, a date of 9/7/2001 would give you 200197 not 20010907, you > are missing leading zeros. > > Why not replace everything from "Dim d" through "Currdate=" with one > statement: > > Currdate = Format(date, "YYYYMMDD") > > Second, when you load SQLstr with the date (the commented out code) > I'll bet you get an error about Currdate not being a field on the > tables, no? Currdate is a field in the VB program, yes, but the SQL > statement will run on the database server and it doesn't know about > currdate. The easiest thing to do is change the SQLstr assignment as > follows: > > SQLstr = "create view DISPATCH_V as select PZBSB, PZNAM, PZBDT cast as > char, PZSCD, PZARA, P1RTE, P1STOP, P1SCD, PWLAT, PWLONG from UFDB, > UFDBS, UCSRFD where PZBSB = P1BSB and P1MTR = PWMTR and PZBDT = " + > cstr(Currdate) > > Note I've remove CurrDate from the string and instead I'm concating > the value of currdate onto the end of the sql statement. This will > result in a sql statement that says: > > ...and P1MTR = PWMTR and PZBDT = 20020101 > > When the date is 1/1/2002. > > Hope this helps. > > -Walden > > ------------ > Walden H Leverich III > President > Tech Software > (516)627-3800 x11 > WaldenL@TechSoftInc.com > http://www.TechSoftInc.com > > -----Original Message----- > From: Smith, Mike [mailto:Mike_Smith@RGCResources.com] > Sent: Wednesday, December 26, 2001 12:29 > To: 'midrange-l@midrange.com' > Subject: RE: Visual Basic SQl statement > > Walden, > i'm on V4r5. client access express. win2k. > > as for the code, i didn't really write it(because i don't know enough > about VB to try to write it). i am using a package called ESRI. It > is a mapping software, and the code is a sample code(they provided) > that allows me to plot points on to the map. here is the code. Thanks > for any help you can provide. > > Private Sub UIButtonControl2_Click() > '++ adds an OLE DB table as an XYEvent layer > '++ > On Error GoTo ErrorHandler: > > Dim d > d = Format(Date, "Short Date") > MyDay = Day(d) > MyMonth = Month(d) > MyYear = Year(d) > > Dim curdate As String > CurrDate = CStr(MyYear) + CStr(MyMonth) + CStr(MyDay) > > '++ New property set for workspacefactory > Dim pPropset As IPropertySet > Set pPropset = New PropertySet > > '++ Provider = > pPropset.SetProperty "CONNECTSTRING", "Provider=MSDASQL.1;Data > source=RGOCCSTM;User ID=;Password=" > > Dim pWorkspaceFact As IWorkspaceFactory > Set pWorkspaceFact = New OLEDBWorkspaceFactory > > '++ Create the new workspace/feature workspace objects > Dim pWorkspace As IWorkspace > Set pWorkspace = pWorkspaceFact.Open(pPropset, 0) > > Dim pFeatWorkspace As IFeatureWorkspace > Set pFeatWorkspace = pWorkspace > > '++ If a sub-set of the table is required and views are supported by > the data source > '++ create a view of the table selection you wish to use. > Dim SQLstr As String > SQLstr = "create view DISPATCH_V as select * from UFDB,UFDBS,UCSRFD > where PZBSB = P1BSB and P1MTR = PWMTR where PZBSB > 0" > 'SQLstr = "create view DISPATCH_V as select PZBSB, PZNAM, PZBDT cast > as char, PZSCD, PZARA,P1RTE,P1STOP,P1SCD,PWLAT, PWLONG from > UFDB,UFDBS,UCSRFD where PZBSB = P1BSB and P1MTR = PWMTR and PZBDT = > Currdate" > > '++ Create the view and fetch the datasetnames from the workspace > pWorkspace.ExecuteSQL SQLstr > > Dim pEnumDataset As IEnumDatasetName > Dim pDataset As IDatasetName > > Set pEnumDataset = pWorkspace.DatasetNames(esriDTAny) > Set pDataset = pEnumDataset.Next > > Do Until pDataset Is Nothing > If pDataset.Name = "RGOCCSTM.DISPATCH_V" Then > Exit Do > End If > Set pDataset = pEnumDataset.Next > Loop > > '++ Create the new table object from the dataset name > Dim pTable As ITable > Set pTable = pFeatWorkspace.OpenTable(pDataset.Name) > > Dim pDoc As IMxDocument > Set pDoc = ThisDocument > Dim pMap As IMap > Set pMap = pDoc.FocusMap > > Dim pTableName As IName > Dim pDS As IDataset > Set pDS = pTable > Set pTableName = pDS.FullName > > '++ Set the field properties for the event theme > Dim pXYEvent2FieldsProperties As IXYEvent2FieldsProperties > Set pXYEvent2FieldsProperties = New XYEvent2FieldsProperties > With pXYEvent2FieldsProperties > .XFieldName = "PWLONG" > .YFieldName = "PWLAT" > .ZFieldName = "" > End With > > '++ Set the spatial reference for the event theme > Dim pSpatialReferenceFactory As ISpatialReferenceFactory > Dim pProjectedCoordinateSystem As IProjectedCoordinateSystem > > Set pSpatialReferenceFactory = New SpatialReferenceEnvironment > Set pProjectedCoordinateSystem = > pSpatialReferenceFactory.CreateProjectedCoordinateSystem(esriSRProjCS_ > NAD198 > 3UTM_11N) > > Dim pXYEventSourceName As IXYEventSourceName > Set pXYEventSourceName = New XYEventSourceName > > '++ Combine all the properties for the event theme > With pXYEventSourceName > Set .EventProperties = pXYEvent2FieldsProperties > Set .SpatialReference = pProjectedCoordinateSystem > Set .EventTableName = pTableName > End With > > Dim pName As IName > Dim pXYEventSource As IXYEventSource > Set pName = pXYEventSourceName > Set pXYEventSource = pName.Open > > '++ Create a new feature layer object for the event theme > Dim pflayer As IFeatureLayer > Set pflayer = New FeatureLayer > Set pflayer.FeatureClass = pXYEventSource > pflayer.Name = "Todays Dispatching" > > '++ Add the layer extension (required to ensure when you edit > '++ the layer's Source properties and click the Set Data Source > '++ button, the correct "Add XY Events" Dialog appears) > Dim pLayerExt As ILayerExtensions > Dim pRESPageExt As New XYDataSourcePageExtension > Set pLayerExt = pflayer > pLayerExt.AddExtension pRESPageExt > > '++ add the new layer to the display > pMap.AddLayer pflayer > > Exit Sub > ErrorHandler: > MsgBox Err.Number & Err.Description > End Sub > > -----Original Message----- > From: Walden H. Leverich [mailto:WaldenL@TechSoftInc.com] > Sent: Wednesday, December 26, 2001 11:52 AM > To: 'midrange-l@midrange.com' > Subject: 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. > _______________________________________________ > 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. > _______________________________________________ > 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. > _______________________________________________ > 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. -- Brad Jensen brad@elstore.com President Electronic Storage Corporation Tulsa OK USA 918-664-7276 LaserVault Report Retrieval & Data Mining www.Laservault.com www.eufrates.com - Add distance learning to your site with easy course preparation _______________________________________________ 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com 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.