Solved: Specify Query Criteria at Runtime in Excel VBA

[kbsudhir]

I am connecting my excel file to Access 2003 Database which has query.
I have a "where" condition in that query with "Between" Clause.

I want to pull the data between two dates ex. Between 1st Jan 2008 & 31st Jan 2008.

This is the query which I am writing in my excel VBA to initiate the query in the database.

rs.Open "select * from IndividualData where Date Between'" & StartDate & "' AND '" & EndDate & "'", cn, adOpenStatic, adLockReadOnly

To allow the user to provide the start data I am using an input box.
StartDate for getting the initial date
and
EndDate to get the End Date.

I am getting an error in the above line of code, I want to know whats the correct syntax for the above code.

Any help regarding the same is appreciated.

Thanks
Sudhir

[Bob Phillips]

Give tis a try



rs.Open "select * from IndividualData where Date Between #" & Format(StartDate, "d-mmmm-yyyy") & "# AND #" & _
Format(EndDate "d-mmmm-yyyy") , cn, adOpenStatic, adLockReadOnly

[kbsudhir]

Thanks Xld. I tried the code, below is what I got while executing.

If I use the code as it is, then I am getting the whole code in Red.

But IF I change it to

rs.Open "select * from IndividualData where Date Between #" & Format(StartDate, "d-mmmm-yyyy") & "# AND #" & _
Format(EndDate , "d-mmmm-yyyy") , cn, adOpenStatic, adLockReadOnly

I just added (,) after EndDate. Now the code is not shown in Red.

Then When executing the code I am getting the following error

Run-time Error '-2147217900(80040e14)':

Syntax error in date in query expression 'Date Between #1-January-2008# AND #31-January-2008'

Then If I change the code to -
rs.Open "select * from IndividualData where Date Between #" & Format(StartDate, "d-mmmm-yyyy") & "# AND #" & _
Format(EndDate, "d-mmmm-yyyy") & "#", cn, adOpenStatic, adLockReadOnly

i.e. added ( & "#") after (Format(EndDate, "d-mmmm-yyyy"))

Then I am getting teh following error-

Run-time error '-2147217904(80040e10)':
No Value given for one or more required parameters.

Can you please let me know where I am going wrong.

I really appreciate your help.

Thanks
Sudhir

[Bob Phillips]

Can you post the workbook and the mdb file, we will solve it very quickly with that?

[kbsudhir]

Here is the .mdb file.

[kbsudhir]

Here is the excel file.

[jammer6_9]

.

[kbsudhir]

Hello,

Any body can help me out here