Strange query problem using dates...

[Teessider]

I create a querydef using the following code...

Private Sub Command74_Click()
    Dim sql As String
    Dim assType As String

    assType = "1"

    sql = "SELECT tblPeople.strPersonNINumber, tblPeople.strPersonforename, tblPeople.strPersonSurname, tblAssessments.strAssType, tblAssessments.dtmAssessmentDate, tblAssessments.blnPassFail " & _
        "FROM tblAssessments INNER JOIN tblPeople ON tblAssessments.lngPersonID = tblPeople.lngPersonID " & _
        "WHERE tblAssessments.strAssType=" & assType & " AND tblAssessments.dtmAssessmentDate Between #10/01/2008# And #11/04/2008# AND tblAssessments.blnPassFail=True"
    RunReport sql, "rptPreAssessmentsPassed"
End Sub

Private Function RunReport(sqlSource, rptName)

    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim strQry As String
    
'set variable values
    Set db = CurrentDb
    strQry = "qryReportQuery"
'Delete old query first - we want fresh data!
    db.QueryDefs.Delete strQry
'Create query definition
    Set qdef = db.CreateQueryDef(strQry, sqlSource)
'Open report for viewing
    DoCmd.OpenReport rptName, acViewPreview
' *** End of Code ***
End Function

Now, when it tries to run the report, I come up with a data type mismatch error...

So, I go into the query created and try to run it directly...same error, so I open it in design view...

Now, in the column dtmAssessmentDate I have in the criteria box the following:

Between #01/10/2008# And #04/11/2008#

Should be fine right???

But, if I select this text and Ctl-C Ctl-P it - Copy and repaste....the query works fine. Or say, if I delete a '#' (or any other part of the expression)and replace it - again it works fine...

...so why doesn't it work in the first place??? I'm baffled and got a feeling there is gonna be a REALLY simple answer...
Any help will be much appreciated, thanks...

 

[Kiwiman]

Howzit

It may have something to do with the assType string. I think that for strings the following syntax is required:

"yourfield = '" & strAssType & "'"

Try...

Private Sub Command74_Click()
    Dim sql As String
   [b] Dim assType As String[/b]

    assType = "1"

    sql = "SELECT tblPeople.strPersonNINumber, tblPeople.strPersonforename, tblPeople.strPersonSurname, tblAssessments.strAssType, tblAssessments.dtmAssessmentDate, tblAssessments.blnPassFail " & _
        "FROM tblAssessments INNER JOIN tblPeople ON tblAssessments.lngPersonID = tblPeople.lngPersonID " & _
        "WHERE [b]tblAssessments.strAssType='" & assType & "' AND[/b] tblAssessments.dtmAssessmentDate Between #10/01/2008# And #11/04/2008# AND tblAssessments.blnPassFail=True"
    RunReport sql, "rptPreAssessmentsPassed"
End Sub

 

[Teessider]

LOL, wierdly, I realised that after I posted, and I was coming back to try and delete my post out of pure embarressment...

Thanks for stating the obvious for me...I am an idiot...ignore me...

Obviously, when I was re-pasting the other text, access looks at the query again and automatically inserts the quotes for me!

In my defence...I had a problem earlier with the dates which was still there when I added quotes around that, so I then put that to the back of my mind and never considered it again!

3 hours scratching my head for such a stupidly obvious answer... *sigh*

Howzit

It may have something to do with the assType string. I think that for strings the following syntax is required:

"yourfield = '" & strAssType & "'"

Try...

Private Sub Command74_Click()
    Dim sql As String
   [b] Dim assType As String[/b]

    assType = "1"

    sql = "SELECT tblPeople.strPersonNINumber, tblPeople.strPersonforename, tblPeople.strPersonSurname, tblAssessments.strAssType, tblAssessments.dtmAssessmentDate, tblAssessments.blnPassFail " & _
        "FROM tblAssessments INNER JOIN tblPeople ON tblAssessments.lngPersonID = tblPeople.lngPersonID " & _
        "WHERE [b]tblAssessments.strAssType='" & assType & "' AND[/b] tblAssessments.dtmAssessmentDate Between #10/01/2008# And #11/04/2008# AND tblAssessments.blnPassFail=True"
    RunReport sql, "rptPreAssessmentsPassed"
End Sub

 

[Kiwiman]

Been there done that - well still doing that if I am honest. Eventually the syntax methods stay in my head.