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: 'email@example.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:firstname.lastname@example.org] Sent: Wednesday, December 26, 2001 10:57 To: email@example.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-Lfirstname.lastname@example.org 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-Lemail@example.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.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.