make a datareport with condition

[abu taher]

I made a datareport simple. just connect. but I want to make it with condition. I have 2
dtpicker for date. I want 1 day report or weekly or monthly report by dtpicker. example
I want 03/11/08 report or 01/11/08 to 03/11/08 report. But I dont know how make it. Where I write code or how I collect it. Help me. I want I select a date from dtpicker or form both dtpicker and click a button then the report show.

[debasisdas]

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

[AUGXIS]

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.

[abu taher]

>> 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.

[AUGXIS]

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

[abu taher]

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.

[abu taher]

I need help. please help me with details.

[abu taher]

help me

[QVeen72]

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

[abu taher]

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.

[QVeen72]

Hi,

For Numeric(Long)
adBigInt, Long

For Text :
adVarChar, String

Regards
Veena

[denine]

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

[abu taher]

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.

[QVeen72]

Hi,

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

Select EmpID,EmpName From EmpMas Where EmpID = ?

Regards
Veena

[abu taher]

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.

[abu taher]

Please any body help me.

[abu taher]

anybody help me please

[QVeen72]

Hi,

Post your SQL statement here..

Regards
Veena

[abu taher]

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.

[abu taher]

anybody can help me with this thread please

[QVeen72]

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

[abu taher]

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.

[abu taher]

please help me with this problem.

[QVeen72]

Hi,

Install Visual Studio ServicePack6 or higher..

Regards
Veena

[jireh]

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

[abu taher]

I already installed service pack 6.

[abu taher]

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

[abu taher]

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.