Re: SELECT syntax

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Austin (anonymous_at_discussions.microsoft.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 06:56:13 -0800

The following syntax looks better:

  Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, S$, rsJH As ADODB.Recordset
  
  S = "Data Provider=SQLOLEDB;server=apsserver;database=jatest;trusted_connection=yes"
  cn.Provider = "MSDataShape"
  cn.Open S
  S = "SHAPE { SELECT ID, [Name] FROM Personnel } " _
    & " APPEND ( {SELECT EmpID, job_date FROM Job_History WHERE job_date >= COALESCE( " _
    & " (SELECT MAX(job_date) FROM job_History WHERE job_date <= '2004-01-01' ),'1900-01-01') } " _
    & " AS rsJH RELATE ID TO EmpID )"
  With rs
    .Open S, cn
    Do While .EOF = False
      Debug.Print "Emp="; !ID; !Name
      Set rsJH = .Fields("rsJH").Value
      With rsJH
        Do While .EOF = False
          Debug.Print " "; !EmpID; !Job_Date
          .MoveNext
        Loop
      End With
      Set rsJH = Nothing
      .MoveNext
    Loop
    .Close
  End With
  cn.Close

But still is not finding "the highest Job_History record before a given date" on a person by person basis.

Relevant Pages

  • Re: SELECT syntax
    ... I have tried the following syntax: ... Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, S$, rsJH As ADODB.Recordset ... This acts as I expected - it finds the highest Job_History before the date for ALL employees, and then appends the records from Job_History to the parent Personnel record. ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting rows syntax + row #ing code in protected sheet?
    ... mentioned WP above because I'm going to reference a handy piece of syntax I ... is there is an extra command to handle "cancel" in VB? ... > Dim i As Long ... > Exit Sub ...
    (microsoft.public.excel.programming)
  • Re: Using query values for field name in table
    ... You didn't need to change the syntax on the Fields collection to get it to ... See http://www.QBuilt.com for all your database needs. ... >>> Dim dbsGeneralThoracic As Database ... >>> Dim fOpenedDB As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: FTS Weighted Values Implementation Problem
    ... The syntax error comes from the simple quotes surrounding your search phrase. ... Dim SearchTerm1 As String ... Dim conn As New SqlConnection ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Programming for all fields in a table
    ... If you want to loop thorugh all records anyway, the syntax would be like ... Dim rs As DAO.Recordset ... rather than allenbrowne at mvps dot org. ... thank you (you must be an extremely experience programmer ...
    (comp.databases.ms-access)