How do I compare dates in Word 2007?
in [Word VBA]
|
From: iris on 30 May 2010 07:16 Hello Greg, I know this is not a question about this specific post and I already posted the question as a new post... but I'm desperate... and still did't fined an answer to my problem... I don't know if I declare the variable right... or the problem is in the SQL query... PLEASE HELP!!! I have an access table called: "categories" this table contains all the documents I have created with their delivery date. in "categories" I have created 2 columns: "delivery_date", "subject" I have created a userform in WORD with the following fields: textbox - Txt_start_date textbox - Txt_end_date listbox - listbox1 search button When I press the "search" button I want to start a search that will upload to the list box all the documents between Txt_start_date and Txt_end_date. this is the code I wrote... which, unfortunatly doe's not work: Private Sub searchdate_Click() On Error Resume Next Dim dbDatabase As Database Dim rsi As Recordset Dim i As Integer Set dbDatabase = OpenDatabase("C:\masterfood.mdb") Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories WHERE delivery_date BETWEEN '" & Txt_start_date.Text & "' AND '" & Txt_start_date.Text & "' order BY delivery_date;", dbOpenSnapshot) i = 0 ListBox1.Clear With rsi Do Until .EOF ListBox1.AddItem (i) ListBox1.ColumnCount = 9 ListBox1.BoundColumn = 9 ListBox1.ColumnWidths = "3.5 in;0 in;0.7 in;1.5 in;1.5 in;0.7 in;0.7 in;0.7 in;0.7 in;" ListBox1.Column(9, i) = ![remarks] ListBox1.Column(8, i) = ![sender_l_name] ListBox1.Column(7, i) = ![sender_f_name] ListBox1.Column(6, i) = ![l_name] ListBox1.Column(5, i) = ![f_name] ListBox1.Column(4, i) = ![tafkid] ListBox1.Column(3, i) = ![irgun] ListBox1.Column(2, i) = ![delivery_date] ListBox1.Column(1, i) = ![Path] ListBox1.Column(0, i) = ![Subject] .MoveNext i = i + 1 Loop End With rsi.Close dbDatabase.Close Set rsi = Nothing Set dbDatabase = Nothing End Sub I hope you can help me with that... Thank you in advance! Iris "Greg Maxey" wrote: > Sub Test() > Dim Date1 As Date 'The mergefield Date > Dim Date2 As Date 'Today > Date1 = "August 2 2007" 'Simulated result of mergefield > Date2 = Now > If DateDiff("d", Date2, Date1) < 0 Then > MsgBox "Your number is up" > End If > End Sub > > -- > Greg Maxey/Word MVP > See: > http://gregmaxey.mvps.org/word_tips.htm > For some helpful tips using Word. > > > BobTrudo wrote: > > Is there some esoteric programming practice within Word 2007 that > > allows the comparison of dates? I.E., {Date1} <= {Date2} {Date1} is > > merged field and {Date2} is today's date {DATE}. So far, I have > > found nothing that works. I am attempting to create a Mergeformat > > renewal document when today's date has passed a merge field date. > > >
From: iris on 30 May 2010 07:21 Hello Greg, I've posted the question to Bob.... I hope this post doesn't complicate things... I know this is not a question about this specific post and I already posted the question as a new post... but I'm desperate... and still did't fined an answer to my problem... I don't know if I declare the variable right... or the problem is in the SQL query... PLEASE HELP!!! I have an access table called: "categories" this table contains all the documents I have created with their delivery date. in "categories" I have created 2 columns: "delivery_date", "subject" I have created a userform in WORD with the following fields: textbox - Txt_start_date textbox - Txt_end_date listbox - listbox1 search button When I press the "search" button I want to start a search that will upload to the list box all the documents between Txt_start_date and Txt_end_date. this is the code I wrote... which, unfortunatly doe's not work: Private Sub searchdate_Click() On Error Resume Next Dim dbDatabase As Database Dim rsi As Recordset Dim i As Integer Set dbDatabase = OpenDatabase("C:\masterfood.mdb") Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories WHERE delivery_date BETWEEN '" & Txt_start_date.Text & "' AND '" & Txt_start_date.Text & "' order BY delivery_date;", dbOpenSnapshot) i = 0 ListBox1.Clear With rsi Do Until .EOF ListBox1.AddItem (i) ListBox1.ColumnCount = 9 ListBox1.BoundColumn = 9 ListBox1.ColumnWidths = "3.5 in;0 in;0.7 in;1.5 in;1.5 in;0.7 in;0.7 in;0.7 in;0.7 in;" ListBox1.Column(9, i) = ![remarks] ListBox1.Column(8, i) = ![sender_l_name] ListBox1.Column(7, i) = ![sender_f_name] ListBox1.Column(6, i) = ![l_name] ListBox1.Column(5, i) = ![f_name] ListBox1.Column(4, i) = ![tafkid] ListBox1.Column(3, i) = ![irgun] ListBox1.Column(2, i) = ![delivery_date] ListBox1.Column(1, i) = ![Path] ListBox1.Column(0, i) = ![Subject] .MoveNext i = i + 1 Loop End With rsi.Close dbDatabase.Close Set rsi = Nothing Set dbDatabase = Nothing End Sub I hope you can help me with that... Thank you in advance! Iris "Greg Maxey" wrote: > Sub Test() > Dim Date1 As Date 'The mergefield Date > Dim Date2 As Date 'Today > Date1 = "August 2 2007" 'Simulated result of mergefield > Date2 = Now > If DateDiff("d", Date2, Date1) < 0 Then > MsgBox "Your number is up" > End If > End Sub > > -- > Greg Maxey/Word MVP > See: > http://gregmaxey.mvps.org/word_tips.htm > For some helpful tips using Word. > > > BobTrudo wrote: > > Is there some esoteric programming practice within Word 2007 that > > allows the comparison of dates? I.E., {Date1} <= {Date2} {Date1} is > > merged field and {Date2} is today's date {DATE}. So far, I have > > found nothing that works. I am attempting to create a Mergeformat > > renewal document when today's date has passed a merge field date. > > >
From: iris on 30 May 2010 08:55 I use a different approach.... I don't use the between query... but there is something wrong with my loop... it stops after the first resault it founds instead of running through all the database.... Private Sub searchdate_Click() On Error Resume Next Dim dbDatabase As Database Dim rsi As Recordset Dim i As Integer Dim stdate As Date Dim enddate As Date Set dbDatabase = OpenDatabase("C:\masterfood.mdb") Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories order BY delivery_date;", dbOpenSnapshot) 'Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories WHERE delivery_date BETWEEN '" & Txt_start_date.Text & "' AND '" & Txt_end_date.Text & "' order BY delivery_date;", dbOpenSnapshot) i = 0 ListBox1.Clear With rsi If ![delivery_date] < Txt_start_date.Text Or ![delivery_date] > Txt_end_date.Text Then MsgBox "not in range " & Txt_start_date Else Do Until .EOF ListBox1.AddItem (i) ListBox1.ColumnCount = 9 ListBox1.BoundColumn = 9 ListBox1.ColumnWidths = "3.5 in;0 in;0.7 in;1.5 in;1.5 in;0.7 in;0.7 in;0.7 in;0.7 in;" ListBox1.Column(9, i) = ![remarks] ListBox1.Column(8, i) = ![sender_l_name] ListBox1.Column(7, i) = ![sender_f_name] ListBox1.Column(6, i) = ![l_name] ListBox1.Column(5, i) = ![f_name] ListBox1.Column(4, i) = ![tafkid] ListBox1.Column(3, i) = ![irgun] ListBox1.Column(2, i) = ![delivery_date] ListBox1.Column(1, i) = ![Path] ListBox1.Column(0, i) = ![Subject] .MoveNext i = i + 1 Loop End If End With rsi.Close dbDatabase.Close Set rsi = Nothing Set dbDatabase = Nothing End Sub "Greg Maxey" wrote: > Sub Test() > Dim Date1 As Date 'The mergefield Date > Dim Date2 As Date 'Today > Date1 = "August 2 2007" 'Simulated result of mergefield > Date2 = Now > If DateDiff("d", Date2, Date1) < 0 Then > MsgBox "Your number is up" > End If > End Sub > > -- > Greg Maxey/Word MVP > See: > http://gregmaxey.mvps.org/word_tips.htm > For some helpful tips using Word. > > > BobTrudo wrote: > > Is there some esoteric programming practice within Word 2007 that > > allows the comparison of dates? I.E., {Date1} <= {Date2} {Date1} is > > merged field and {Date2} is today's date {DATE}. So far, I have > > found nothing that works. I am attempting to create a Mergeformat > > renewal document when today's date has passed a merge field date. > > >
From: Doug Robbins - Word MVP on 30 May 2010 17:50 On 30/05/2010 10:55 PM, iris wrote: > 'Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories WHERE > delivery_date BETWEEN '"& Txt_start_date.Text& "' AND '"& > Txt_end_date.Text& "' order BY delivery_date;", dbOpenSnapshot) Try using: Set rsi = dbDatabase.OpenRecordset("SELECT * FROM categories WHERE _ delivery_date BETWEEN #'" & Txt_start_date.Text & "'# AND #'" & _ Txt_end_date.Text & "'# order BY delivery_date;", dbOpenSnapshot) Alternatively, after loading all of the records into the listbox, you could use some code to iterated through the records starting from ..ListCount to 1 and stepping by minus one and delete each record if its data was outside the range of dates in which you are interested. -- Hope this helps, Doug Robbins - Word MVP Please reply to the newsgroup unless you want to obtain my services on a professional basis.
|
Pages: 1 Prev: PDF printer driver Next: Fillin fields in template behave differently on different workstat |