Parameter Query of Access is not Available in VB DataReport

[ashutoshvyas]

I am designing a data report in VB6. In data environment it is possible to selet tables and 'select quries'. But I want to generate a report based on parameters passed by user, so i had created a parameter query in ACCESS which is not available in the properties of command1.

SQL code of query is as under :
SELECT Customers.accou nt_no, Customers.card_ no, Customers.name, payments.amount , payments.paymen t_date, dlt_3.[Second last Date], total_amount.[Sum Of amount]
FROM ((Customers INNER JOIN dlt_3 ON Customers.accou nt_no = dlt_3.account_n o) INNER JOIN total_amount ON Customers.accou nt_no = total_amount.ac count_no) INNER JOIN payments ON Customers.accou nt_no = payments.accoun t_no
WHERE (((payments.pay ment_date) Between [enter date 1 :] And [enter date 2 :]))
ORDER BY Customers.accou nt_no;

Please help me to generate report based on user input.

[ashutoshvyas]

Hello Everybody.
I had solved the above question by adding sql command1 in the data environment and by writing the procedure for form which can accept the parameters from the user and will pass value to the query and then generation of report based on that query.

But a new problem had arised.
I want to give serial numbers to all the records which are generated in the report by the means of above mentioned query.

Help from any one is appriciated.

[JonJacobs]

SQLServer has Identity fields that are basically auto-increment numerics. I don't know if Access has something like that, but if it does, then create a temporary table with an auto-increment column and select your report records into the table, then make your report based on the new table, that now includes a "serial number"

HTH

Jon

[ashutoshvyas]

SQLServer has Identity fields that are basically auto-increment numerics. I don't know if Access has something like that, but if it does, then create a temporary table with an auto-increment column and select your report records into the table, then make your report based on the new table, that now includes a "serial number"

HTH

Jon

Thanks a lot, Jon. I will try this and very soon I will reply that it was possible to perform or not & it works or not.

[ashutoshvyas]

Thanks a lot, Jon. I will try this and very soon I will reply that it was possible to perform or not & it works or not.

dear all
i am pasting the code with errors, an effort to take this problem towards solution. If anyone can turn it into functionality, it will be a great help for me.

' I KNOW THIS CODE IS WITH ERRORS BUT I WANT TO MAKE IT ERROR FREE IF POSSIBLE
' SO THAT I CAN GENERATE A REPORT WHICH ACCEPTS DATE AS PARAMETER
' AND FILTER THAT RECORDS BY DATE AND STORES RESULTS IN TEMPORARY TABLE
' SO THAT RECORD COUNT CAN BE DONE AND THEN REPORT CAN BE GENERATED.

Private Sub Command1_Click( )
'The If block closes the recordset if it was previously open before
'running the parameterized query.

With DataEnvironment 1
If .rsCommand1.Sta te = adStateOpen Then
.rsCommand1.Clo se
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Tex t)

' This If block checks to determine if any records are returned
' by the parameter. Then it shows a report if records are returned.
' Or displays a Message Box if no records are returned.

If .rsCommand1.Rec ordCount > 0 Then
Set rptashu.DataSou rce = DataEnvironment 1

' code inserted to prepare a new table with record count

Dim i As Long
'Delete current data in A_Temp_Table
sql = "DELETE * FROM temptable"
Con.Excute sql

'select the data needed
sql = "SELECT * FROM dataenvironment 1" ' selecting all the fields
Set rs = Con.Excute(sql)
i = 1
While Not rs.EOF
'insert row for temporary table with serial column
sql = "INSERT INTO temptable (Serial,account _no,card_no,nam e,amount,paymen t_dat e,Second last Date,Sum Of amount) VALUES(" & i & "," & rs.Fields(1) & "," & rs.Fields(2) & "," & rs.Fields(3) & "," & rs.Fields(4) & "," & rs.Fields(5) & "," & rs.Fields(6) & "," & rs.Fields(7)
Con.Excute sql
rs.MoveNext
Wend
'now, showing Report with data load from temp table
' report shown by parameter paseed as date, and taking all the records from temp table filtered by date and with record count

rptashu.Show
Else
MsgBox "No Titles found"
End If
End With
End Sub