By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,900 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,900 IT Pros & Developers. It's quick & easy.

Parameter Query of Access is not Available in VB DataReport

P: 18
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.account_no, Customers.card_no, Customers.name, payments.amount, payments.payment_date, dlt_3.[Second last Date], total_amount.[Sum Of amount]
FROM ((Customers INNER JOIN dlt_3 ON Customers.account_no = dlt_3.account_no) INNER JOIN total_amount ON Customers.account_no = total_amount.account_no) INNER JOIN payments ON Customers.account_no = payments.account_no
WHERE (((payments.payment_date) Between [enter date 1 :] And [enter date 2 :]))
ORDER BY Customers.account_no;

Please help me to generate report based on user input.
Aug 2 '07 #1
Share this Question
Share on Google+
4 Replies


P: 18
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.
Aug 3 '07 #2

P: 25
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
Aug 3 '07 #3

P: 18
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.
Aug 5 '07 #4

P: 18
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 DataEnvironment1
If .rsCommand1.State = adStateOpen Then
.rsCommand1.Close
End If

' This passes in the value entered into the TextBox.

.command1 CDate(text1.Text)

' 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.RecordCount > 0 Then
Set rptashu.DataSource = DataEnvironment1

' 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 dataenvironment1" ' 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,name,amount,payment_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
Aug 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.