find files between a range of dates

[iris]

Hello everybody,

I have created a user form in word with access database.

In the database I've inserted a list of files with their creation dates.

I want to create a serach wich will load a listbox with all the files
between a range of dates... for example:

find all files tha their creation date is bigger then 12/01/2010 and lower
then 22/02/2010 and list them in a listbox...

I hope someone can help me...

thank you

iris

[iris]

I post this message to give you the details of my application:

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 this is a better explanation to my problem.

Thank you in advance!

Iris

[Doug Robbins - Word MVP]

Maybe you can make use of the information in the article "Getting access to
the Document Properties of a Word file” at:

http://www.word.mvps.org/FAQs/MacrosVBA/DSOFile.htm

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"iris" <ir...@discussions.microsoft.com> wrote in message
news:4027E309-8789-43EC...@microsoft.com...

[iris]

Hi Doug,

Thank you for your response.

I know how to retrieve all the information and how to use it... my problem
is only with the search ....

My only problem right now is how to search for files in a range of dates...

for example: find all the documents between 22/01/2010 to 2/3/2010.

I think it's all about the sintax in the SQL query or that I did'nt declare
a variable.... If you can please look at the code I will be greatful!

Thank you

Iris

[Doug Robbins - Word MVP]

You should be able to make use of the Dir$ command and test the data of each
file, adding it if it is within the range.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"iris" <ir...@discussions.microsoft.com> wrote in message

news:DFB34E05-93CA-4628...@microsoft.com...

[iris]

I read my data from the data base not from the folder directory

[Doug Robbins - Word MVP]

Sorry, I missed your second post in this thread and did not realise that you
were trying to get the data out of Access.

I think that the following

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)

should be:

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)

That is, remove the apostrophes that you had in addition to the quotes
around & Txt_start_date.Text & and & Txt_start_date.Text & (which should be
& Txt_end_date.Text &)

I just noticed that you were looking for files between the start_date and
the start_date so maybe that is what the problem is and the apostrophes
don't make any difference.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"iris" <ir...@discussions.microsoft.com> wrote in message

news:92B18BB3-165C-49C8...@microsoft.com...

[iris]

Hi Doug,

Sorry to say... it still does't work.....

I will write the question again... in a different way... maybe I will
explain my self better this time.... I appologize for the inconvenience...

So... Here it is:

I have created a Word Template in which I'm sending data to an Access
database.

The data includes:
- Document path
- Document delivery_date

I also created a Userform in word in which I want to retrieve a list of
documents from the DATABASE and load it to a listbox. This user form contains
2 textboxes and a "send" button:
- Txt_start_date
- Txt_end_date
- ListBox1

The criteria of the search is: retrieve the documents between a range of
dates: Txt_start_date - Txt_end_date

This is the code I've wrote:

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

End Sub

For some reason… the code does not work…. I am missing something – I don't
know what… maybe I don't declare a variety right? Maybe I didn't write the
SQL query right…. Something is wrong and I don't know what!

If you can please help me, I will be grateful!

Thank you very much!

[Doug Robbins - Word MVP]

Try

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 seem to recall that when using dates in SQL, they need to be delimited
with # #

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"iris" <ir...@discussions.microsoft.com> wrote in message

news:6C988BF5-7B6E-4063...@microsoft.com...