-
Jun 3rd, 2020, 06:28 AM
#1
Thread Starter
Member
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
-
Jun 3rd, 2020, 08:45 AM
#2
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:
Dim sql = "SELECT COUNT (*) FROM Docstable WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1) OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)" Dim dateFrom = DatePickerFrom.Value.Date Dim dateTo = DatePickerTo.Value.Date Dim count As Integer Using connection As New OleDbConnection("connection string here"), command As New OleDbCommand(sql, connection) With command.Parameters .Add("@DateFrom1", OleDbType.Date).Value = dateFrom .Add("@DateFrom2", OleDbType.Date).Value = dateFrom .Add("@DateTo1", OleDbType.Date).Value = dateTo .Add("@DateTo2", OleDbType.Date).Value = dateTo End With connection.Open() count = CInt(command.ExecuteScalar()) End Using
Last edited by jmcilhinney; Jun 4th, 2020 at 08:13 AM.
-
Jun 3rd, 2020, 08:48 AM
#3
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:
Dim sql = <sql> SELECT COUNT (*) FROM Docstable WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1) OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2) </sql> '... Using connection As New OleDbConnection("connection string here"), command As New OleDbCommand(sql.Value, connection) '... End Using
Last edited by jmcilhinney; Jun 4th, 2020 at 08:14 AM.
-
Jun 3rd, 2020, 08:52 AM
#4
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.
-
Jun 3rd, 2020, 07:25 PM
#5
Re: How to detect one date field value is later/greater than the other date field?
Originally Posted by jmcilhinney
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...
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 3rd, 2020, 09:09 PM
#6
Re: How to detect one date field value is later/greater than the other date field?
Originally Posted by .paul.
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.
-
Jun 4th, 2020, 07:49 AM
#7
Thread Starter
Member
Re: How to detect one date field value is later/greater than the other date field?
Originally Posted by jmcilhinney
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:
Dim sql = "SELECT COUNT (*)
WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1)
OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)"
Dim dateFrom = DatePickerFrom.Value.Date
Dim dateTo = DatePickerTo.Value.Date
Dim count As Integer
Using connection As New OleDbConnection("connection string here"),
command As New OleDbCommand(sql, connection)
With command.Parameters
.Add("@DateFrom1", OleDbType.Date).Value = dateFrom
.Add("@DateFrom2", OleDbType.Date).Value = dateFrom
.Add("@DateTo1", OleDbType.Date).Value = dateTo
.Add("@DateTo2", OleDbType.Date).Value = dateTo
End With
connection.Open()
count = CInt(command.ExecuteScalar())
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)'."
-
Jun 4th, 2020, 07:57 AM
#8
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)'."
Last edited by .paul.; Jun 4th, 2020 at 08:20 AM.
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 4th, 2020, 08:06 AM
#9
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.
-
Jun 4th, 2020, 08:10 AM
#10
Re: How to detect one date field value is later/greater than the other date field?
COUNT(*) FROM whateveryourtableis ... etc
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 4th, 2020, 08:13 AM
#11
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.
-
Jun 4th, 2020, 08:14 AM
#12
Re: How to detect one date field value is later/greater than the other date field?
Originally Posted by jmcilhinney
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 5th, 2020, 07:54 PM
#13
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|