Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1172

Trying to pass a date as a parameter to my Acces query in VBA code

Show all headers | View raw

I have the following code which is supposed to execute a query and
output the results to a new table. I have a problem however with
passing the date as a parameter. I take the date from a cell on the
spreadsheet, The cell has a formula in it "=now() -1".

However, I get the error message: "Data type mismatch in criteria
expression" when executing the statement "l_qdfTmp.Execute
dbFailOnError".

How can I set this date so I dont get this mismatch error?

-------------------

Public Sub RunQuery(p_sQueryName As String, p_dtContextDate As Date)
    Dim l_qdfTmp As QueryDef
    Dim l_dtContextDate As Date

    On Error GoTo ErrorHandler

    'Delete query
    On Error Resume Next

    m_dbsControl.TableDefs.Delete
(p_sQueryName)                        'delete rule query results table
    m_dbsControl.QueryDefs.Delete ("Create" &
p_sQueryName)             'delete create table query for rule query
    On Error GoTo ErrorHandler

    'Set parameter values
    Set l_qdfTmp = m_dbsControl.CreateQueryDef("Create" &
p_sQueryName, "SELECT * INTO tbl" & p_sQueryName & " FROM [" &
p_sQueryName & "];")
    l_dtContextDate = CDate(p_dtContextDate)
    l_qdfTmp.Parameters("@ContextDate") = l_dtContextDate

    l_qdfTmp.Execute dbFailOnError

    m_lRecordCount = l_qdfTmp.RecordsAffected
    Set m_rsResult = Nothing

    'Close querydef
    l_qdfTmp.Close

    'Update recordset
    'Jet workspace should be used because Find instruction only
available in Jet workspace
    Set m_rsSecurityInfo = Nothing

    'Update counter
    m_lSecurityInfoNewCount = m_lSecurityInfoNewCount + 1

    Exit Sub
ErrorHandler:
    Call ErrorHandler(Err)

    'Close open resources
    If Not (l_qdfTmp Is Nothing) Then l_qdfTmp.Close

    Exit Sub
    Resume Next
End Sub

Back to comp.lang.basic.visual.misc | Previous | Next — Next in thread | Find similar

Thread

Trying to pass a date as a parameter to my Acces query in VBA code colmkav <colmjkav@yahoo.co.uk> - 2012-06-06 08:36 -0700
  Re: Trying to pass a date as a parameter to my Acces query in VBA code "Theo Tress" <rbk@online.de> - 2012-06-07 11:31 +0200
    Re: Trying to pass a date as a parameter to my Acces query in VBA code colmkav <colmjkav@yahoo.co.uk> - 2012-06-07 06:01 -0700
      Re: Trying to pass a date as a parameter to my Acces query in VBA code "Theo Tress" <rbk@online.de> - 2012-06-09 17:15 +0200

csiph-web