Calculating intervals using Dates
-
Hi Hope you can help. I've got data saved in SQL DataTable in 1 column is the date an invoice was sent. 2nd column is a Bit(Boolean). This tells me whether the invoice has been paid. I want to work it so that on form load, a msgbox tells the User to send a reminder for a particular invoice. The code I'm using works fine for 1st 2 rows and then misses out all other rows. Any ideas please? :confused: Dim CurrentDate As Date Dim ReminderDate As Date Dim InvoiceDate As Date Dim msgbxReminder As MsgBoxResult CurrentDate = Date.Now.Date InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate") ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows If dr("Cleared") Is System.Convert.DBNull Then If CurrentDate = ReminderDate Then msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next Thanks all ;)
Kris MCP
-
Hi Hope you can help. I've got data saved in SQL DataTable in 1 column is the date an invoice was sent. 2nd column is a Bit(Boolean). This tells me whether the invoice has been paid. I want to work it so that on form load, a msgbox tells the User to send a reminder for a particular invoice. The code I'm using works fine for 1st 2 rows and then misses out all other rows. Any ideas please? :confused: Dim CurrentDate As Date Dim ReminderDate As Date Dim InvoiceDate As Date Dim msgbxReminder As MsgBoxResult CurrentDate = Date.Now.Date InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate") ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows If dr("Cleared") Is System.Convert.DBNull Then If CurrentDate = ReminderDate Then msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next Thanks all ;)
Kris MCP
Hi, I think there are at least two flaws: 1. you should not compare for equality
if CurrentDate=ReminderDate, since doing so you might miss a reminder; you need to use an inequality. 2. The reminder date depends on the order, so it MUST be calculated inside the ForEach loop, using data pertaining to the current row. Hope this helps. :)Luc Pattyn [Forum Guidelines] [My Articles]
Voting for dummies? No thanks. X|
-
Hi, I think there are at least two flaws: 1. you should not compare for equality
if CurrentDate=ReminderDate, since doing so you might miss a reminder; you need to use an inequality. 2. The reminder date depends on the order, so it MUST be calculated inside the ForEach loop, using data pertaining to the current row. Hope this helps. :)Luc Pattyn [Forum Guidelines] [My Articles]
Voting for dummies? No thanks. X|
Hi Luc, Thanks for that, I can see what you're saying. By having the reminder date outside the ForEach loop, it's not being read on each loop. I've changed the code to For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) If dr("Cleared") Is System.Convert.DBNull And ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) Then If CurrentDate <> ReminderDate Then Else msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next End Sub However, still getting stuck on 2nd row of table. Have I misunderstood something? :~ Thanks alot
Kris MCP
-
Hi Luc, Thanks for that, I can see what you're saying. By having the reminder date outside the ForEach loop, it's not being read on each loop. I've changed the code to For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) If dr("Cleared") Is System.Convert.DBNull And ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) Then If CurrentDate <> ReminderDate Then Else msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next End Sub However, still getting stuck on 2nd row of table. Have I misunderstood something? :~ Thanks alot
Kris MCP
KrisnNala wrote:
If CurrentDate <> ReminderDate
you made it worse! equals is not good, since that only fores on one specific day, and your code may not run every day. You need greater-equal. And is InvoiceDate really the same for every order??? Try to focus a bit. :)
Luc Pattyn [Forum Guidelines] [My Articles]
Voting for dummies? No thanks. X|
-
KrisnNala wrote:
If CurrentDate <> ReminderDate
you made it worse! equals is not good, since that only fores on one specific day, and your code may not run every day. You need greater-equal. And is InvoiceDate really the same for every order??? Try to focus a bit. :)
Luc Pattyn [Forum Guidelines] [My Articles]
Voting for dummies? No thanks. X|
-
Hi Luc, Thanks for that, I can see what you're saying. By having the reminder date outside the ForEach loop, it's not being read on each loop. I've changed the code to For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) If dr("Cleared") Is System.Convert.DBNull And ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) Then If CurrentDate <> ReminderDate Then Else msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next End Sub However, still getting stuck on 2nd row of table. Have I misunderstood something? :~ Thanks alot
Kris MCP
KrisnNala wrote:
ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) If dr("Cleared") Is System.Convert.DBNull And ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) Then
The second half of the condition will always be true. You just set the variable to the same value that you are comparing it to.
KrisnNala wrote:
If CurrentDate <> ReminderDate Then Else
That is the exact same thing as an equality comparison. What happens if the program is not run every single day? If the reminder date happens to be on the weekend, then on monday it will not be equal any more. Use
ReminderDate <= CurrentDateto get all invoices that has passed the reminder date.Despite everything, the person most likely to be fooling you next is yourself.
-
Hi Hope you can help. I've got data saved in SQL DataTable in 1 column is the date an invoice was sent. 2nd column is a Bit(Boolean). This tells me whether the invoice has been paid. I want to work it so that on form load, a msgbox tells the User to send a reminder for a particular invoice. The code I'm using works fine for 1st 2 rows and then misses out all other rows. Any ideas please? :confused: Dim CurrentDate As Date Dim ReminderDate As Date Dim InvoiceDate As Date Dim msgbxReminder As MsgBoxResult CurrentDate = Date.Now.Date InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate") ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows If dr("Cleared") Is System.Convert.DBNull Then If CurrentDate = ReminderDate Then msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next Thanks all ;)
Kris MCP
KrisnNala wrote:
InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate")
You have to get the invoice date inside the loop, otherwise you will use the same invoice date for all records.
Despite everything, the person most likely to be fooling you next is yourself.
-
KrisnNala wrote:
InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate")
You have to get the invoice date inside the loop, otherwise you will use the same invoice date for all records.
Despite everything, the person most likely to be fooling you next is yourself.
Hi Guff Thanks alot for your help. Still have a problem that it's not moving through each row of the table. I know it's something really stupid I'm missing but getting annoyed with my self. Code is now Dim CurrentDate As Date Dim ReminderDate As Date Dim InvoiceDate As Date Dim msgbxReminder As MsgBoxResult CurrentDate = Date.Now.Date For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows If dr("Cleared") Is System.Convert.DBNull Then InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate") ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) If ReminderDate <= CurrentDate Then msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next Thanks Alot:confused:
Kris MCP
-
Hi Guff Thanks alot for your help. Still have a problem that it's not moving through each row of the table. I know it's something really stupid I'm missing but getting annoyed with my self. Code is now Dim CurrentDate As Date Dim ReminderDate As Date Dim InvoiceDate As Date Dim msgbxReminder As MsgBoxResult CurrentDate = Date.Now.Date For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows If dr("Cleared") Is System.Convert.DBNull Then InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate") ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) If ReminderDate <= CurrentDate Then msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.") End If End If Next Thanks Alot:confused:
Kris MCP
-
You still read the invoice date from the same data row eventhough you do it inside the loop. Read the invoice date from the data row
drinstead.Despite everything, the person most likely to be fooling you next is yourself.