Database application query problem

[cool11]

Thanks for your reply and for the links, This is my current code:

    rs1.Open "CL1", ds1, adOpenDynamic, adLockOptimistic
    que = "SELECT DateDiff('h',0,Sum(end_time-call_in)) &  Format(Sum(end_time-call_in), ':nn:ss') as TotalTime "
    que = que + "From CL1 WHERE call_in Between #" + Trim(Text1.Text) + "# and #" + Trim(Text2.Text) + "#"
    que = que & " AND Format(call_in,'w') NOT IN(1,7) "
    que = que & " AND Int(call_in) NOT IN"
    
    If (List1.ListCount = 1) Then
    que = que & "(" & Int(CDate(List1.List(iloop))) & ") "
    Else
    For iloop = 0 To List1.ListCount - 1
    TempArray() = Split(List1.List(iloop), "/")
    MyDate = DateSerial(TempArray(2), TempArray(0), TempArray(1))

     If (iloop = List1.ListCount - 1) Then
    que = que & " " & Int(MyDate) & ") "
    Else
    If (iloop > 0) Then
    que = que & " " & Int(MyDate) & ", "
    Else
    que = que & " (" & Int(MyDate) & ", "
    End If
    End If
    
    Next iloop
    End If
    que = que & " AND end_time Is Not Null;"
    Set rs1 = ds1.Execute(que)
    MSFlexGrid1.TextMatrix(1, 5) = rs1("TotalTime").Value
    Text4.Text = que

The query now has no errors but the outcome is the same as the result of excluding weekends(the section for excluding dates is not effective even though it looks correct). Dunno where is it wrong?
The query on running looks like:

SELECT DateDiff('h',0,Sum(end_time-call_in)) & Format(Sum(end_time-call_in), ':nn:ss') as TotalTime From CL1 WHERE call_in Between #08/01/2008# and #08/31/2008# AND Format(call_in,'w') NOT IN(1,7) AND Int(call_in) NOT IN (8/28/2008, 8/29/2008, 8/22/2008, 8/21/2008) AND end_time Is Not Null;

Thanks

[cool11]

Hi got the solution, the #'s were missing for the dates,now its working fine .
Is the loop proper or am i making it too complicated?

Thanks so much

[si_the_geek]

It don't see how the code you posted could have created the query you showed - there is nothing which could output values like 8/28/2008, it should just be numbers like 34523 instead.

There are lots of If's in your code that aren't needed, as well as the dreaded CDate. Here is how I would do the same thing:

...
    que = que & " AND Int(call_in) NOT IN ("
    
    For iloop = 0 To List1.ListCount - 1
      TempArray() = Split(List1.List(iloop), "/")
      MyDate = DateSerial(TempArray(2), TempArray(0), TempArray(1))
      If (iloop = List1.ListCount - 1) Then
        que = que & Int(MyDate)
      Else
        que = que & Int(MyDate) & ", "
      End If
    Next iloop

    que = que & ") AND end_time Is Not Null;"
...

[cool11]

Hi Thanks, the problem was solved but you reduced its complexity, you Rock man!.
But still it wasn't giving proper results, the #'s were missing, the modified code is:

    For iloop = 0 To List1.ListCount - 1
      TempArray() = Split(List1.List(iloop), "/")
      MyDate = DateSerial(TempArray(2), TempArray(0), TempArray(1))
      If (iloop = List1.ListCount - 1) Then
        que = que & "#" & Int(MyDate) & "#"
      Else
        que = que & "#" & Int(MyDate) & "#, "
      End If
    Next iloop

    que = que & ") AND end_time Is Not Null;"

Thanks again

[si_the_geek]

I just tested, and it seems that Int returns a Date in this case - which is not what I intended, and is not safe either (even with the # characters).

It would be better to convert the values to to Long instead, eg:

        que = que & CLng(Int(MyDate))

[cool11]

Thanks so much Its almost done, without your help it couldn't have been done.

I have another database related query, should i create another topic? Its related to retrieving selected fields of all records from the database and displaying it in the MSflexgrid for making reports. I tried to search it but was unable to find any topics related to it.

Thanks

[si_the_geek]

Unless it is specifically related to the original question in this thread, it would be best as a new thread.