Report Designer Query

[turtle]

I have a program that uses an Access database and the report designer built into VB 6.0.

Now for the problem. I need to do a date range search using input from txtBeginDate (beginning date) and txtEndDate (ending date) text boxes. I want the report to show only the dates that fall between the beginning and ending dates that were selected by the user in the previous text boxes. So how do I do it ? Do I do an SQL Query ? How do I get only the desired information to print out to the report?

Oh... my client does not want to use a full blown Crystal Report. (Don't want to have to buy Crystal....in other words)

Can anyone help me out?

Thanks

[pinkpanther2070]

take a look at the simple data environment thread and alter the sql string to your liking

This code may give you a little more flexibility

Hope this helps
Pinkpanther

[Negative0]

Something like this: (I did not type in IDE so there may be some small snytax errors)

'IN THE FORM
Sub Command1_Click()
  Call DataReport1.sSetup(txtDate1.text,txtDate2.text)
DataReport1.show
End Sub

'In the DataReport
Public Sub sSetup(sDate as string, eDate as string)
dim tmprs as adodb.recordset
dim strSQL as string

strSQL = "SELECT * FROM table where datefield between '" & sDate & "' and '" & eDate & "'"
tmprs.open strSQL,objConn,adOpenForwardOnly, adLockReadOnly

Set me.datasource = tmprs
End Sub

Hope this helps,

[turtle]

The code to show the report is in a command buttons click event. So where should the parts of the code go? In the form load event or the command.click event?

Sorry to ask a dumb question...

[turtle]

where in the DataReport should this piece of code go?

'In the DataReport
Public Sub sSetup(sDate as string, eDate as string)
dim tmprs as adodb.recordset
dim strSQL as string

strSQL = "SELECT * FROM table where datefield between '" & sDate & "' and '" & eDate & "'"
tmprs.open strSQL,objConn,adOpenForwardOnly, adLockReadOnly

Set me.datasource = tmprs
End Sub

Should this go in the form that is calling the report or in the DataReport_Initialize function, or should I create a function ? I am a little confused.

Thanks for Your help.

[Negative0]

The IN THE DATAREPORT code should go in the datareport as a new function call sSetup.

The IN THE FORM code should go on the form that is calling the datareport where the dates are specified.

Hope this clears it up,

[turtle]

when i tried running the program the operation stopped when it got to the Data Report code.

It said that the objConn was not defined. Can You help me with this also?

Thanks

[Negative0]

Turtle,

In response to your PM:

What is objConn?

objConn is a connection object. So if you have a connection object already out there for your database replace objConn with your connection name. If you do not have one you can set one up like this:

dim objConn as new adodb.connection
objConn.Open   "Driver={Microsoft Access Driver (*.mdb)};" & _
                "Dbq=C:\YourFile.mdb"

Hope this helps,

[turtle]

I keep coming up with an error...

Object variable or with block variable not set

and it highlights this piece of code

tmprs.open strSQL,objConn,adOpenForwardOnly, adLockReadOnly

[Negative0]

Change this line:

dim tmprs as adodb.recordset

to

dim tmprs as new adodb.recordset

The recordset object was dimmed but not created. The new keyword creates the object.

Hope this helps,