Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help my SQL please... simple SELECT with ISNULL

Status
Not open for further replies.

AJLoehr

Programmer
Oct 3, 2008
26
US
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.
Code:
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.
 
You had one too many ")", no date delimiters, and incorrect IS NULL. Try
Code:
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
 
I tried it and it still didn't work... let me give you the whole code:

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.
 
What's the error message?
Try stick this line prior to the openrecordset so you can determine the exact SQL statement.
Code:
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?
Code:
   sevendaysql = "SELECT DISTINCT * FROM REDBALLtbl WHERE CT  BETWEEN Date()-7 AND Date() AND TT IS NULL"

Duane
Hook'D on Access
MS Access MVP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top