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

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

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

Back to article view | Back to comp.lang.basic.visual.misc

Contents

  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

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

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

[toc] | [next] | [standalone]

#1187

Without entering your code deeper,  think..

>"SELECT * INTO tbl" & p_sQueryName & " FROM ...

it's never a good idea to insert variables directly, because there might be 
blanks or other unallowed characters in the variable, so simply use somewhat 
like

"SELECT * INTO " & fnConvert(p_sQueryName)....

where fnConvert adds chr$(34) around the variable's content.

You might just try this before digging deeper.



Regards,
TT 

[toc] | [prev] | [next] | [standalone]

#1189

On Jun 7, 11:31 am, "Theo Tress" <r...@online.de> wrote:
> Without entering your code deeper,  think..
>
> >"SELECT * INTO tbl" & p_sQueryName & " FROM ...
>
> it's never a good idea to insert variables directly, because there might be
> blanks or other unallowed characters in the variable, so simply use somewhat
> like
>
> "SELECT * INTO " & fnConvert(p_sQueryName)....
>
> where fnConvert adds chr$(34) around the variable's content.
>
> You might just try this before digging deeper.
>
> Regards,
> TT

thanks. Not completely sure what you mean by the syntax required. is
fnconvert SQL or VBA? Looks like VBA the way you have written the
above.

[toc] | [prev] | [next] | [standalone]

#1221

> thanks. Not completely sure what you mean by the syntax required. is
> fnconvert SQL or VBA? Looks like VBA the way you have written the
> above.


Im not sure if it works in VBA - in VB6 you can use a user-written VB6 
function in a SQL statement just like this:

SELECT * FROM myTable WHERE myFunc(Fieldname) = AnyValue

(Perhaps Fieldname must be written in brackets like this [Fieldname], try 
it.)

with

Function myFunc(Fieldname as String) as String
    myFunc = Chr$(34) & Fieldname & Chr$(34)
End Function

The point is that a user defined function myFunc can be called from a 
SQL-Statement.

What I wanted to say with my previous contribution was that SQL sntax is 
sensible for embedded blanks in data inserted via variables, so it is always 
a good idea not to use the variable directly but  its content embedded in 
"...". 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.basic.visual.misc

csiph-web