I have a table for which I have to create a report.The user must only enter the dates(from and to).The date field is present in the table.

Please help me to do this.

13 Years
Discussion Span
Last Post by udaywali

The first thing you want to do is select data that needs to be included in the report. So, write a small function like this:

private function GetSQL() as string
   dim sql as string
   sql = "Select * from mytable"
   sql = sql & " Where myDate <= #" & txtFromDate & "#"
   sql = sql & " And myDate >= #" & txtToDate & "#"
   GetSQL = sql
end function

Of course, I am assuming that user will enter date in text fields txtFromDate and txtToDate.

Next thing you want to do is to print the data on to a form. Include a picturebox on the form. Print to that picturebox.

private sub printReport()
   dim rs as ADODB.Recordset
   dim curY as integer
   dim th as integer
   set rs = new ADODB.recordset
   rs.connnection = cnn 'assuming this is opened on form load, etc.
   rs.open GetSQL(), adopenstatic
   picture1.currentx = 1250
   picture1.currenty = 210
   picture1.fontname = "Arial"
   picture1.fontsize = 12
   th = picture1.textHeight("junk")
   picture1.print "Heading for Report"
   curY = 210 + th + 100 'border from header
   do while not rs.eof
        printFld 100, curY, rs.field(0)
        printFld  400, curY, rs.field(1)
        'so on for other fields
        curY = curY + th     
        if curY > picture1.height then
           exit do
end sub 
private sub printFld (x as integer, y as integer, s as string)
   picture1.currentx = x
   picture1.currenty = y
   picture1.print s;  'you may skip the semi colon
end sub

I have only given the skeleton of the report generator. I have not included any error handling. I have not included page handling, report totals, etc. I assume ADO references are included in your project.

You can do the same thing with printer instead of picturebox, but with some exceptions like printer.enddoc, to send the report to printer. See VB help on enddoc, printer orientation, etc. if you wish to send it to printer.

Let me know if you need more help.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.