Help my SQL please... simple SELECT with ISNULL

[AJLoehr]

I am trying to pull all records from REDBALLtbl where REDBALLtbl.CT (date) is between today and 7 days prior to today PLUS (AND) REDBALLtbl.TT (text) is NULL.

sevendaysql = "SELECT DISTINCT * FROM REDBALLtbl WHERE REDBALLtbl.CT = BETWEEN " & Format(DateDiff("d", 7, Now()), "dd-mmm-yy") & " AND " & Format(Now(), "dd-mmm-yy") & " AND REDBALLtbl.TT ISNULL)"

Am I supposed to have date delimiters in "#"... I tried it and got the same error. I get the missing operator in query expression. Highlighting the where clause. Is it the lack of text delimiters " ' " for ISNULL... do you use text delimiters when saying ISNULL?? I've tried it with and without... and get the same errors... so I'm not sure what the problem is.

Any help would be greatly appreciated.

A.J.

 

[dhookom]

You had one too many ")", no date delimiters, and incorrect IS NULL. Try

sevendaysql = "SELECT DISTINCT * FROM REDBALLtbl WHERE REDBALLtbl.CT = BETWEEN #" & Format(DateDiff("d", 7, Now()), "dd-mmm-yy") & "# AND #" & Format(Now(), "dd-mmm-yy") & "# AND REDBALLtbl.TT IS NULL"

Duane
Hook'D on Access
MS Access MVP

 

[AJLoehr]

I tried it and it still didn't work... let me give you the whole code:

    Dim DB As DAO.Database
    Dim rst As DAO.Recordset
    Dim sevendaysql As String
    sevendaysql = "SELECT DISTINCT * FROM REDBALLtbl WHERE REDBALLtbl.CT = BETWEEN #" & Format(DateDiff("d", 7, Now()), "dd-mmm-yy") & "# AND #" & Format(Now(), "dd-mmm-yy") & "# AND REDBALLtbl.TT IS NULL"
    Debug.Print sevendaysql
    Set DB = CurrentDb
    [highlight]Set rst = DB.OpenRecordset(sevendaysql)[/highlight]

Appreciate the help by the way... I used to be decent at this simple SQL, but either I'm getting old or just not doing it enough to remember the little things by memory.

 

[dhookom]

What's the error message?
Try stick this line prior to the openrecordset so you can determine the exact SQL statement.

Debug.Print "sevendaysql: " & sevendaysql

I'm not sure why you are using DateDiff() when I think you want to subtract days. You also had "BETWEEN =" which is wrong.

Does this work for you? Do you actually need to use the time portion of the dates?

   sevendaysql = "SELECT DISTINCT * FROM REDBALLtbl WHERE CT  BETWEEN Date()-7 AND Date() AND TT IS NULL"

Duane
Hook'D on Access
MS Access MVP