Can someone see what's wrong with this query statement PLS?

[AncientTiger]

I need a second set of eyeballs looking at this, because I've stared at it until mine are crossed...

This query SHOULD be returning FOUR records, however the query is returning 0... and I just cannot figure out why.

------------------------------------
Over 20 years of programming, and still learning every day!

 

[dhookom]

Use debug.print to place the sql string in the debug window. Then copy and paste the SQL statement into a blank query SQL view.

I think you want to combine/add the date and times together.

Next time, copy and paste the code and data so it might be possible for us to actual build your table and test your code without having to type it all out.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

try somethig like this. I did my query in MS Query in Excel, but I get 4 rows...

Sub unusual()
    Dim sdate As Date, edate As Date, stime As Date, sSQL As String
    
    sdate = DateValue("1-15-15")
    edate = sdate + 1
    stime = TimeValue("06:00")
    
'you don't want these
'    sdate = sdate - 1
'    edate = edate + 1
    
    sSQL = "select * "
    sSQL = sSQL & "from [Unusual$] "
    sSQL = sSQL & "where DATEADDED + TIMEADDED between #" & sdate & "# + #" & stime & "# "
    sSQL = sSQL & "  and  #" & edate & "# + #" & stime & "# "
    
    Debug.Print sdate + stime
    Debug.Print edate + stime
    Debug.Print sSQL
     
    With ActiveSheet.ListObjects(1).QueryTable
        .Sql = sSQL
        .Refresh False
    End With
End Sub

[pre]
ADDED_DT_TM
1/15/2015 9:35
1/15/2015 13:43
1/15/2015 14:25
1/16/2015 1:12
[/pre

 

[SkipVought]

oops, here's the query

Sub unusual()
    Dim sdate As Date, edate As Date, stime As Date, sSQL As String
    
    sdate = DateValue("1-15-15")
    edate = sdate + 1
    stime = TimeValue("06:00")
    
'you don't want these
'    sdate = sdate - 1
'    edate = edate + 1
    
[b]    sSQL = "select DATEADDED + TIMEADDED as [ADDED_DT_TM] "
    sSQL = sSQL & "from [Unusual$] "
    sSQL = sSQL & "where DATEADDED + TIMEADDED between #" & sdate & "# + #" & stime & "# "
    sSQL = sSQL & "  and  #" & edate & "# + #" & stime & "# "
[/b]    
    Debug.Print sdate + stime
    Debug.Print edate + stime
    Debug.Print sSQL
     
    With ActiveSheet.ListObjects(1).QueryTable
        .Sql = sSQL
        .Refresh False
    End With
End Sub

 

[dhookom]

Actually the table name is a bit longer:

    sSQL = "select DATEADDED + TIMEADDED as [ADDED_DT_TM] "
    sSQL = sSQL & "from [SummaryUnusualIncidents] "
    sSQL = sSQL & "where DATEADDED + TIMEADDED between #" & sdate & "# + #" & stime & "# "
    sSQL = sSQL & "  and  #" & edate & "# + #" & stime & "# "

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Yea, I shortened up the table name for my test.

 

[AncientTiger]

Sorry I haven't replied sooner... busy busy busy.

I developed a work around that bypassed the stubborn SQL query (just pulled ALL records from the table, popped 'em into a dimmed array, then did a loop to sort and pick programmatically what I wanted.).

Thanks for the replies, and again, sorry for not replying sooner than this

------------------------------------
Over 20 years of programming, and still learning every day!