Member Avatar

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.

Member Avatar

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

Member Avatar

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.

Member Avatar

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

Member Avatar

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
Member Avatar

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.

Member Avatar

I need help. please help me with details.

Member Avatar

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

Member Avatar

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.

Member Avatar

Hi,

For Numeric(Long)
adBigInt, Long

For Text :
adVarChar, String

Regards
Veena

Member Avatar

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

Member Avatar

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.

Member Avatar

Hi,

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

Select EmpID,EmpName From EmpMas Where EmpID = ?

Regards
Veena

Member Avatar

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.

Member Avatar

Hi,

Post your SQL statement here..

Regards
Veena

Member Avatar

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.

Member Avatar

anybody can help me with this thread please

Member Avatar

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

Member Avatar

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.

Member Avatar

please help me with this problem.

Member Avatar

Hi,

Install Visual Studio ServicePack6 or higher..

Regards
Veena

Member Avatar

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

Member Avatar

I already installed service pack 6.

Member Avatar

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

Member Avatar

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.