Using 2 types of criteria when searching for a record 1

[Danielle17]

I need to be able to choose a customer name and then a range of dates to produce a report that shows activities during those dates. I have already created a form with a combo box that contains the customer names and 2 text boxes where dates can be typed in. Here is the code that I've been experimenting with:

Private Sub cmdFindRecord_Click()
On Error GoTo Err_cmdFindRecord_Click

Dim dSQL As String
Dim stDocName As String
Dim strCrit As String

stDocName = "GageReport"

' strCrit = "SELECT Info.Customer" & _
' " FROM Info" & _
' " WHERE "[Customer] = " & Me.cboCustomer.Value" & _
' " AND(#" & Me.txtDateOne.Value & "# And #" & Me.txtDateTwo.Value & "# Between Info.CalDone AND Info.CalDue);"

'strCrit = "[Customer] = " & Me.cboCustomer.Value & _
' "[CalDue] BETWEEN #" & Me.txtDateOne.Value & "# And #" & Me.txtDateTwo.Value & "#;"

strCrit = "[Customer] = " & Me.cboCustomer & _
"AND(#" & Me.txtDateOne.Value & "# And #" & Me.txtDateTwo.Value & "# Between Info.CalDone AND Info.CalDue);"

DoCmd.OpenReport stDocName, acViewPreview, , strCrit

Exit_cmdFindRecord_Click:
Exit Sub

Err_cmdFindRecord_Click:
MsgBox Err.Description
Resume Exit_cmdFindRecord_Click

End Sub

I'm still working on it....I know how to get it to pull up a report for a certain customer but when I throw the dates in it doesn't seem to work. I'm not exactly sure how to write the part of the code for the date event.... Any help...thanks.

 

[JoeMiller]

Why don't you just add the criteria to the query feeding the report? Then whenever your run the report it will look at the form and restrict the records returned to the values there.

Under Customer criteria in your query:
[Forms]![MyFormName]![cboCustomer]

Under CalDue criteria in your query:
Between [Forms]![MyFormName]![txtDateOne] and [Forms]![MyFormName]![txtDateTwo]

HTH Joe Miller
joe.miller@flotech.net

 

[Danielle17]

Thanks....
Originally I wasn't running the report off of a query, so I created a query identical to the table....it works when I create a command button that runs a query....how do I get it to run a report off of that query now...without the query showing?

 

[Danielle17]

gosh.....stupid question...I got it to run a report.....thanks so much!

 

[JoeMiller]

Change the record source of your report from the table to the query. Now open the report with data filled out on the form and your all set.

Joe Miller
joe.miller@flotech.net

 

[thowe]

This is exactly what I want to do, but I don't understand the code (not a programmer) I want to do this with a macro/button command. Is this possible? I want to be able to pull up multiple criteria too. Anyone know how to do this with the macro commands built in to Access 97?

 

[robmkimmons]

thowe,

This may sound complicated to explain but in reality it is quite simple. Once you follow the steps described by JoeMiller above, you can create what you're looking for through some simple code OR you can also create a macro to run it as well. I will try to explain the two different ways to make this happen... given that you first follow the steps that Joe gave:

ie. Create a query that will return the data you desire, create a form where the criteria will be specified, link the form to your query criteria as Joe stated above, have the record source of your report read from that query.

*whew!*

Now that you have done that, create a command button on your same form that you have the specifying criteria. Under the properties, On Click section of that command button, you can now either have a macro run the report or write simple code to do the same... as follows:

MACRO: (a little tough to simulate here)

Action: OpenReport
Action Arguments (at bottom)
Report Name: YourReportNameHere
View: Print Preview (or however you want it)

save the macro and put the macro name under the "On Click" section of the command button.

~~~~~~~~~~~~~~~

CODE:

Sub cmdViewReport_OnClick()

    DoCmd.OpenReport "YourReportNameHere", acPreview, "", ""

End Sub

Hope that helps!
~Rob

 

[thowe]

Thank you Rob. I will try that. Thanks for your helpful comments!