help with access and word vba

[iris]

I have created a database in access in which there are 2 columns:
1. Delivery_date
2. DocumentName

I have also created a Word Userform in which I want to search all the
documents that were created between a range of dates. For example: all the
documents that were created between 28/04/2010 and 31/05/2010.

The result of this search will populate a listbox in the userform.

The userform contains 2 textboxes:
1. Txt_start_date
2. Txt_end_date

A search button

Listbox1

This is the code I wrote… but it doesn't work…. I hope you can find out what
I am doing wrong ….

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_end_date.Text & "' order BY delivery_date;", dbOpenSnapshot)

i = 0

ListBox1.Clear

With rsi

Do Until .EOF
ListBox1.AddItem (i)
ListBox1.ColumnCount = 2
ListBox1.BoundColumn = 2
ListBox1.ColumnWidths = "3.5 in;3.5 in; "

ListBox1.Column(1, i) = ![DocumentName]
ListBox1.Column(0, i) = ![ delivery_date]

.MoveNext
i = i + 1
Loop
End With

rsi.Close
dbDatabase.Close
Set rsi = Nothing
Set dbDatabase = Nothing

End Sub

I thank you in advance for your help!

Best,

Iris

[Doug Robbins - Word MVP]

On 1/06/2010 5:03 AM, iris wrote:
> I have created a database in access in which there are 2 columns:
> 1. Delivery_date
> 2. DocumentName
>
> I have also created a Word Userform in which I want to search all the
> documents that were created between a range of dates. For example: all the
> documents that were created between 28/04/2010 and 31/05/2010.
>
> The result of this search will populate a listbox in the userform.
>
> The userform contains 2 textboxes:
> 1. Txt_start_date
> 2. Txt_end_date
>
> A search button
>
> Listbox1
>
> This is the code I wrote… but it doesn't work…. I hope you can find out what
> I am doing wrong ….
>
> 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_end_date.Text& "' order BY delivery_date;", dbOpenSnapshot)

>
>
> i = 0
>
> ListBox1.Clear
>
> With rsi
>
> Do Until .EOF
> ListBox1.AddItem (i)
> ListBox1.ColumnCount = 2
> ListBox1.BoundColumn = 2
> ListBox1.ColumnWidths = "3.5 in;3.5 in; "
>
> ListBox1.Column(1, i) = ![DocumentName]
> ListBox1.Column(0, i) = ![ delivery_date]
>
> .MoveNext
> i = i + 1
> Loop
> End With
>
> rsi.Close
> dbDatabase.Close
> Set rsi = Nothing
> Set dbDatabase = Nothing
>
> End Sub
>
> I thank you in advance for your help!
>
> Best,
>
> Iris
>
>

See the Access examples on the following page of Greg Maxey's website:

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

And load the whole table into the list box and then use

With ListBox1
.BoundColumn = 2
For i = .ListCount - 1 To 0 Step -1
If .Value < Txt_start_date Or .Value > txt_end_date Then
.RemoveItem (i)
End If
Next i
End With

To remove the items that are outside the dates.

--
Hope this helps,
Doug Robbins - Word MVP
Please reply to the newsgroup unless you want to obtain my services on a
professional basis.

[iris]

Hi doug

I tried your solution with the second with loop.

I load the hole table to the listbox but the second with loop doesn't work -
nothing is removed from the listbox...

here is the code I used:

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 order BY
delivery_date;", dbOpenSnapshot)

i = 0

ListBox1.Clear

With rsi
Do Until .EOF
ListBox1.AddItem (i)
ListBox1.ColumnCount = 2
ListBox1.BoundColumn = 2
ListBox1.ColumnWidths = "3.5 in;3.5 in;"

ListBox1.Column(1, i) = ![delivery_date]
ListBox1.Column(0, i) = ![Subject]

.MoveNext
i = i + 1
Loop
End With

With ListBox1

.BoundColumn = 2
For i = .ListCount - 1 To 0 Step -1

If .Value < Txt_start_date Or .Value > Txt_end_date Then

.RemoveItem (i)
End If
Next i
End With

rsi.Close

dbDatabase.Close
Set rsi = Nothing
Set dbDatabase = Nothing
End Sub

> .
>

[Doug Robbins - Word MVP]

You may need to use the DateDiff() function to compare the dates.