Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: sql works in access but not in vba :(

    I tried the all sqls in access and they returned the desired results but in access but in my vba sqlRECk & sqlMgmtCk

    returned null values, why would it work in access but not in VBA?
    In the immediat window i ran both sqlRECk & sqlMgmtCk and the results were :
    sqlMgmtCk =
    SELECT Max(ter.checkNum) FROM ter WHERE (((ter.checkDate) = #10/15/2003#) AND ((ter.CheckNum) like "*MGMT"));

    sqlRECk =
    SELECT Max(ter.checkNum) FROM ter WHERE (((ter.checkDate) = #10/15/2003#) AND ((ter.CheckNum) like "*-RE"));


    Can anyone help pls thnks

    M~

    Option Compare Database
    Option Explicit

    Private Sub Form_Load()

    Dim cn As Object
    Dim rs As Object

    Dim dLastCkDate As Date
    Dim lstRECk As Integer
    Dim lstMgmtCk As Integer

    Dim sqlCkDate As String
    Dim sqlRECk As String
    Dim sqlMgmtCk As String

    sqlCkDate = "SELECT Max([CheckDate]) FROM ter;"
    sqlRECk = "SELECT Max(ter.checkNum) FROM ter WHERE (((ter.checkDate) = #"
    sqlMgmtCk = "SELECT Max(ter.checkNum) FROM ter WHERE (((ter.checkDate) = #"

    Set cn = Application.CurrentProject.Connection
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sqlCkDate, cn, 1 ' 1 = adOpenKeyset

    If (rs.EOF) Then
    MsgBox "END OF FILE"
    Else
    dLastCkDate = rs.Fields(0).Value
    Me.txtDate.Value = rs.Fields(0).Value
    rs.Close
    sqlMgmtCk = sqlMgmtCk & dLastCkDate & "#) AND ((ter.CheckNum) like ""*MGMT""));"
    rs.Open sqlMgmtCk, cn, 1

    If Not (rs.EOF) Then
    rs.MoveFirst
    Me.txtMgmt.Value = rs(0).Value
    Else
    MsgBox "EOF"
    End If
    rs.Close
    sqlRECk = sqlRECk & dLastCkDate & "#) AND ((ter.CheckNum) like ""*-RE""));"
    rs.Open sqlRECk, cn, 1
    If Not (rs.EOF) Then
    rs.MoveFirst
    Me.txtRE.Value = rs(0).Value
    Else
    MsgBox "EOF"
    End If
    rs.Close
    End If
    End Sub

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    When running your SQL in code replace the "*" with "%" ... The asterisk is the wildcard in QBE but in SQL its the percent sign.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •