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

[colmkav]

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

[Theo Tress]

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

[colmkav]

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.

[Theo Tress]

Post by colmkav
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
"...".