Data Grid Question 1

[Monguse]

I am using the Microsoft Datagrid 6.0 and while it does show all the data found in the Access database, What I would like to do is be able to give it (in this case) a date range for the information I want displayed. Is this possable and if so can someone point me to where I can find out how to do this.

Thanks "The beauty of the second amendment is, that it will not be needed until they try to take it." - Thomas Jefferson

WebMaster:

http://www.firearms-for-us.com

 

[BobWman]

use a query to return a recordset filtered on the date range. Then set the recordsource of the datagrid to the recordset. Don't forget to use Set.

Set DataGrid.RecordSource = rs

 

[CCLINT]

Click on the datacontrol that the data grid is connected to, and in the Properties window you will find a property called RecordSource. Change it to something like this:

Select * FROM Employees WHERE Birthdate BETWEEN #01/01/2002# AND Now ORDER BY Birthdate

OR

Select * FROM Employees WHERE Birthdate BETWEEN #01/01/2002# AND Now ORDER BY #12/31/2002#

Or In Code (changing data1 to the name of the actual datacontrol used):
data1.RecordSource="Select * FROM Employees WHERE Birthdate BETWEEN #01/01/2002# AND Now ORDER BY Birthdate"
data1.Refresh

Or, in code create a recordset object as stated above and set the DataGrid.RecordSource property to it.

 

[Monguse]

CCLINT - Tried your suggestion, however I am having a problem - I get an error message using this code to change what is being displayed. Can you or someone else look it over and let me know what I'm doing wrong?

This is the error message I get:

"No Value Given for one of more required parameter"

This is the code I am using:

StartDate = frmSchedule.mskStartDate.Text
EndDate = frmSchedule.mskEndDate.Text

Adodc1.RecordSource = "SELECT * FROM Schedule WHERE Date BETWEEN Val(StartDate) and Val(EndDate)"
Adodc1.Refresh "The beauty of the second amendment is, that it will not be needed until they try to take it." - Thomas Jefferson

WebMaster:

http://www.firearms-for-us.com

 

[caseye]

Which line gives you the error

 

[BobWman]

Your dates must be surrounded by either a single quote or # sign. I believe the pound sign is correct for Access(I haven't used anything but SQL Server for a long time).

Try something like this:

CCLINT - Tried your suggestion, however I am having a problem - I get an error message using this code to change what is being displayed. Can you or someone else look it over and let me know what I'm doing wrong?

This is the error message I get:

"No Value Given for one of more required parameter"

This is the code I am using:

StartDate = frmSchedule.mskStartDate.Text
EndDate = frmSchedule.mskEndDate.Text

Adodc1.RecordSource = "SELECT * FROM Schedule WHERE Date BETWEEN #" & StartDate & "# and #" & EndDate & "#"

 

[BobWman]

Some extra text was inserted in the last response:

Your dates must be surrounded by either a single quote or # sign. I believe the pound sign is correct for Access(I haven't used anything but SQL Server for a long time).

Try something like this:

Adodc1.RecordSource = "SELECT * FROM Schedule WHERE Date BETWEEN #" & StartDate & "# and #" & EndDate & "#"

 

[Monguse]

The line that gives the Error message is the adodc1.Refreash "The beauty of the second amendment is, that it will not be needed until they try to take it." - Thomas Jefferson

WebMaster:

http://www.firearms-for-us.com

 

[BobWman]

That is normal. The error is in the previous line, though.

The refresh method applies the change to the data control, and the change is incorrect.

 

[caseye]

BobWMan is right it is either the # sign or % the date must be surrounded by single qoutes and #/%

 

[Monguse]

Thanks for all the help BobWMan's suggestion worked fine.

"The beauty of the second amendment is, that it will not be needed until they try to take it." - Thomas Jefferson

WebMaster:

http://www.firearms-for-us.com

 

[CCLINT]

>CCLINT - Tried your suggestion, however I am having a problem

Monguse: the literal date in my suggestion is surround by # signs. And yours not, and that is the problem why yours didn't work....how you ended up using Val I do not know.
You will notice that the Now function doesn't have signs around it - you do not need them there. You can also do it with-out the # signs as long as the literal date is converted to a Date Value.....

In the 3 examples I gave I did make a mistake during copying and pasting in the 2nd answer though...