How do I compare dates in Word 2007?

[BobTrudo]

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.

 

[Greg Maxey]

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

 

[Graham Mayor]

You can compare dates in fields also,

{IF {Mergefield Date1 \@ "yyyyMMdd"} <= {Date \@ "yyyyMMdd"} "Do this"
"otherwise do this"}

See http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[BobTrudo]

Many thanks for the reply. Sometimes, the really obvious eludes me.

 

[BobTrudo]

Thanks Graham. Had I initially done a more thorough search of the forum,
your previous postings would have answered my questions. Thanks again.

 

[iris]

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

 

[iris]

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

 

[iris]

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

 

[Doug Robbins - Word MVP]

'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.