make a datareport with condition

you need to frame the sql dynamically at runtime and pass the same to the report.

hi. you can write the query first. then i think you have to set the record source of your report
to the query you made.

>> you need to frame the sql dynamically at runtime and pass the same to the report.

how can I pass to the report?

>>hi. you can write the query first. then i think you have to set the record source of your report
to the query you made.

How can I connect between the button and the report. I meant when I select date and press button then how the report show by code.

hi. you try to make a query first.say

rs.Open "Select appdate, name from tablename where  appdate between '" & date1 & "' AND '" & date2 & "'"

then set the record source of the report

Set repo.DataSource = rs
repo.Title = "Hello"
repo.Show

when I made report I use adodc control by DSN. table select by code like
"select * from table1". I use adodc control in full project and connect with property
not by code.
so help me with details code. I didn't understand your code.

I need help. please help me with details.

help me

Hi Abu,

Well... Follow these Steps:
Open a Data Project
Add a Connection Object (Configure to your database)
Add A Command Object (Say Command1) And Right-Click
Goto properties and Select Option SQL Statement and Type :
Select * From MyTable Where MyDate Between ? And ?
Click on Apply,
Parameter Tab Is Enabled, Go there..
Set these properties for both Parameters:
DataType = adDBDate
HostDataType =Date (VT_Date)
And Apply,
Save the Project
Now Design the DataReport1 on this Command Object...

In The Calling Form, Place 2 Date Pickers say DTFr and DTTo

Place Command Button And Show the Report like this :

Load DataEnvironment1
With DataEnvironment1
If .rsCommand1.State <> 0 Then .rsCommand1.Close
.Command1 Format(DTFr.Value,"dd-mm-yyyy"), Format(DTTo.Value,"dd-mm-yyyy")
End With
DataReport1.Show

I HOPE IT IS CLEAR..

Regards
Veena

thanks veena its work well.
but I have a few question.
1. Set these properties for both Parameters:
DataType = adDBDate
HostDataType =Date (VT_Date)
if I want to search number then what i need to change?
if need to search name or string then....?
please help me with this question.

Hi,

For Numeric(Long)
adBigInt, Long

For Text :
adVarChar, String

Regards
Veena

hello abu,
pls try this code if this will work.

("Select * from TableName where IDate <= cDate (' " & CStr(DTPicker1.Value) & " ') and IDate >= cDate(' " & CStr(DTPicker2.Value) & " ')")

or
If DTPicker1.Value <= !IDate And DTPicker2.Value >= !IDate Then

Regards,

denine

Dear veena
I have a problem. I write code in sql like
select * from table1
so all field show. help me what I write for particular field. I write....
select Student name from table1
what I write? some time parametre not show.

Hi,

Yes, You can write all the field names from the table...
eg:

Select EmpID,EmpName From EmpMas Where EmpID = ?

Regards
Veena

I write like you but no field show but parametre was shown. If i write only this
select * from table1
then show all fields. but when I write your past code it was worked.

Please any body help me.

anybody help me please

Hi,

Post your SQL statement here..

Regards
Veena

SELECT * FROM incomereport WHERE AccountinfoDate AND IncomeDate BETWEEN ? AND ?

1st I write this code and it work after I write for other

SELECT Student ID, Student Name, Jamat Name, Feeding Charge,
Exam Fee, Roll No, Jamat Fee, Others Fee, AccountinfoDate,
Father 's Name,Category,Contact No,Address,Admission Date From stdreport Where Student ID ?
this code not work.

anybody can help me with this thread please

Hi,

Change your where condition to :

Where [Student ID] = ?

Similarly, in the sql statement, enclose all field names with square brackets, or remove Space from field names :

SELECT [Student ID], [Student Name]...

Also, remove Single Quote from Fieldname "Father's name"

Regards
Veena

thanks veena. now its work. but a problem when i input a number in text box (and press a button) for show the report a msg show.

Run time error 2147217887(80040e21)
[micorsoft][ODBC microsoft access driver] optional feature not implemented.

i write code in the button:
Load DataEnvironment2
With DataEnvironment2
If .rsCommand1.State <> 0 Then .rsCommand1.Close
.Command1 Text2.Text
End With
DataReport2.Show

please help me for this.

please help me with this problem.

Hi,

Install Visual Studio ServicePack6 or higher..

Regards
Veena

sorry i didnt read all replies... lol. veena already answered it...

I already installed service pack 6.

please help me please.................

i write code in the button:
Load DataEnvironment2
With DataEnvironment2
If .rsCommand1.State <> 0 Then .rsCommand1.Close
.Command1 Text2.Text
End With
DataReport2.Show

what problem in this code? any body help me.