Results 1 to 13 of 13

Thread: How to detect one date field value is later/greater than the other date field?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    How to detect one date field value is later/greater than the other date field?

    I have one textbox(displays count result), one button(performs the counting), and two DateTimePickers(for date period from and to) in a form. I am using MS Access database for my program with two date fields(date1 and date2). What I want is to count how many records dated with(dates given from datetimepickerfrom and datetimepickerto). The counting is with condition like: If date2 is greater or later than date1 then the counting will base on date2, else on date1. Any suggestion or modification in my code is highly appreciated.


    This is my code but it results error:"Item cannot be found in the collection corresponding to the requested name or ordinal."
    countrec = New ADODB.Recordset
    With countrec

    If .Fields("Date2").Value > .Fields("Date1").Value Then
    .Open("select count(*) as count2 from Docstable where Date2 >=#" & DateTimePickerfrom.Value.Date & "# and Date2 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
    textbox1.Text = .Fields("count2").Value
    .Close()
    else
    .Open("select count(*) as count1 from Docstable where Date1 >=#" & DateTimePickerfrom.Value.Date & "# and Date1 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
    textbox1.Text = .Fields("count1").Value
    .Close()

    end if

    End With

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to detect one date field value is later/greater than the other date field?

    If you're using .NET then you should use .NET and stop pretending that you're still using VB6. If you have a teacher or a boss who still thinks it's the 1980s then I guess you're out of luck but, otherwise, join the rest of us in 2020. Here's the same ADO.NET and pure SQL solution I provided at Stack Overflow:
    vb.net Code:
    1. Dim sql = "SELECT COUNT (*)
    2.            FROM Docstable
    3.            WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1)
    4.            OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)"
    5. Dim dateFrom = DatePickerFrom.Value.Date
    6. Dim dateTo = DatePickerTo.Value.Date
    7. Dim count As Integer
    8.  
    9. Using connection As New OleDbConnection("connection string here"),
    10.       command As New OleDbCommand(sql, connection)
    11.     With command.Parameters
    12.         .Add("@DateFrom1", OleDbType.Date).Value = dateFrom
    13.         .Add("@DateFrom2", OleDbType.Date).Value = dateFrom
    14.         .Add("@DateTo1", OleDbType.Date).Value = dateTo
    15.         .Add("@DateTo2", OleDbType.Date).Value = dateTo
    16.     End With
    17.  
    18.     connection.Open()
    19.  
    20.     count = CInt(command.ExecuteScalar())
    21. End Using

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to detect one date field value is later/greater than the other date field?

    I'm not 100% sure whether VB 2012 supports multiline String literals or not. If not, use an XML literal:
    vb.net Code:
    1. Dim sql = <sql>
    2.               SELECT COUNT (*)
    3.               FROM Docstable
    4.               WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1)
    5.               OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)
    6.           </sql>
    7. '...
    8.  
    9. Using connection As New OleDbConnection("connection string here"),
    10.       command As New OleDbCommand(sql.Value, connection)
    11.     '...
    12. End Using

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to detect one date field value is later/greater than the other date field?

    BTW, you said on SO that you're using VB 2010 but here you say that you're using VB 2012. It may not matter in this case but please provide accurate information because it may be important.
    Last edited by jmcilhinney; Jun 3rd, 2020 at 09:09 PM. Reason: Fixed typo.

  5. #5
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,458

    Re: How to detect one date field value is later/greater than the other date field?

    Quote Originally Posted by jmcilhinney View Post
    BTW, you said on SO that you're using VB 2010 but here you say that you're using VB 2010. It may not matter in this case but please provide accurate information because it may be important.
    Said VB 2010, and said VB 2010 on stackoverflow... Some kinda discrepancy there

    Edit: Says VB 2012 here...

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to detect one date field value is later/greater than the other date field?

    Quote Originally Posted by .paul. View Post
    Said VB 2010, and said VB 2010 on stackoverflow... Some kinda discrepancy there

    Edit: Says VB 2012 here...
    My outrage seems rather misplaced when i can't even type properly. Fixed my post.

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to detect one date field value is later/greater than the other date field?

    Quote Originally Posted by jmcilhinney View Post
    If you're using .NET then you should use .NET and stop pretending that you're still using VB6. If you have a teacher or a boss who still thinks it's the 1980s then I guess you're out of luck but, otherwise, join the rest of us in 2020. Here's the same ADO.NET and pure SQL solution I provided at Stack Overflow:
    vb.net Code:
    1. Dim sql = "SELECT COUNT (*)
    2.            WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1)
    3.            OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)"
    4. Dim dateFrom = DatePickerFrom.Value.Date
    5. Dim dateTo = DatePickerTo.Value.Date
    6. Dim count As Integer
    7.  
    8. Using connection As New OleDbConnection("connection string here"),
    9.       command As New OleDbCommand(sql, connection)
    10.     With command.Parameters
    11.         .Add("@DateFrom1", OleDbType.Date).Value = dateFrom
    12.         .Add("@DateFrom2", OleDbType.Date).Value = dateFrom
    13.         .Add("@DateTo1", OleDbType.Date).Value = dateTo
    14.         .Add("@DateTo2", OleDbType.Date).Value = dateTo
    15.     End With
    16.  
    17.     connection.Open()
    18.  
    19.     count = CInt(command.ExecuteScalar())
    20. End Using
    Thanks for your effort my friend but it still gives me an error: "Additional information: Syntax error (missing operator) in query expression 'COUNT (*) WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1) OR (Date2 > Dateto1 AND Dateto2 BETWEEN @DateFrom2 AND @DateTo2)'."

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,458

    Re: How to detect one date field value is later/greater than the other date field?

    Try this...

    COUNT (*) WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1) OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)'."

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: How to detect one date field value is later/greater than the other date field?

    I think a From is missing

    so From what Table ?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,458

    Re: How to detect one date field value is later/greater than the other date field?

    COUNT(*) FROM whateveryourtableis ... etc

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: How to detect one date field value is later/greater than the other date field?

    Yeah, oops. I missed the FROM clause but you really should have been able to work that out for yourself if you'd bothered to actually look at the SQL and try to figure out what the issue might have been. I have edited that post.

  12. #12
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,458

    Re: How to detect one date field value is later/greater than the other date field?

    Quote Originally Posted by jmcilhinney View Post
    Yeah, oops. I missed the FROM clause but you really should have been able to work that out for yourself if you'd bothered to actually look at the SQL and try to figure out what the issue might have been. I have edited that post.
    I missed it too. There were other errors in the sql string the op posted

  13. #13

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to detect one date field value is later/greater than the other date field?

    Thanks for the correction Sir. Sorry if I'm giving you the confusion.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width