date search 2

[RufussMcGee]

Known facts..
Table called tblDataLOG inside this table have the following Customer Name, FollowUP and description.

Query called qrySearch inside the query under the followup field am using the following parameter '>[Beginning Date] And <[ending date]'

Forms called frmSearchDate with two date boxes and one preview button with the following code for preview button.

Private Sub Preview_Click()
If IsNull([Beginning Date]) Or IsNull([Ending Date]) Then
msgbox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Date"
Else
If [Beginning Date] > [Ending Date] Then
msgbox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Date"
Else
Me.Visible = False
End If
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "qrySearch"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

The thing that I do not understand is how to tie the form frmSearchDate to the qrySearch and then pass all the infomation another another form called frmSearchResults.

In Summary all I am trying to do is prompt the user to enter a start and ending date for a search and give up a form that shows the results.

Thanks.

 

[DJN]

Use the text boxes on the form as the parameters in the query. i.e. Between Forms!frmFormName!txtStartDate And Forms!frmFormName!txtEndDate I've used txtStartDate and txtEndDate as text box names, but you should use your own.

 

[RufussMcGee]

The thing how do you being the parameter to the form? The access book that I am using really does not cover that subject.

Thanks

 

[PHV]

Query called qrySearch inside the query under the followup field am using the following parameter '>[highlight][Forms]![frmSearchDate]![/highlight][Beginning Date] And <[highlight][Forms]![frmSearchDate]![/highlight][Ending Date]'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DJN]

The text boxes on the form provide the parameters.When you run the query a parameter box will pop up, if you have the parameters in the criteria field.

 

[RufussMcGee]

Another question what would the code be the surpress the automatic pop boxes with Textbox the needs to be filled in on a form. Guess the thing that cannot understand is getting the information in the right place.

Made this easy search page with errors code so that the user has to enter the dates.

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click
If IsNull([txtStartDate]) Then
msgbox "Enter Starting Date !"
Exit Sub
End If

Dim stDocName As String

stDocName = "rptResults"
DoCmd.OpenReport stDocName, acPreview

Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
msgbox Err.Description
Resume Exit_btnPreview_Click

End Sub

Then the opens the form rptResults which is the original table with all info in it. In the form under Due Date change the control soure to read "=[tblDataLOG]![Due Date]>[Forms]![frmSearchME]![txtStartDate]" but does not return the results that I am looking for. Quite new to all this VB and do not understand. Any help all be great.

Thanks.

 

[DJN]

OK lets start from scratch. Create a new form. On the form place two unbound text boxes, txtStartDate txtEndDate.Close and save the form as frmSearch. Next, create a new query. In the design grid choose your table. Choose the fields that will appear in the report. Now, in the criteria of the field that is your date field, put the following: Between [forms]![frmSearch]![txtStartDate] And [forms]![frmSearch]![txtEndDate] Save the query, qryReport. Open the form frmSearch in design mode and add a command button, cmdPrintReport. Let the wizard create the button. You want Report Operations, Print Report. In the record source of the report, rptMyReport, or whatever name you have called it, put qryReport. Save it. Now, when you open the report you should get the data that is bounded by the dates chosen in the frmSearch. That should do it. Any problems, give me your email address and I will email you a small demo DB

 

[RufussMcGee]

THANKS DJN that did the trick. Feel like a fool know that I see how do get the results I needed. It was very easy to follow your steps and saw where my mistake was happening.

Excellent Help.
Thanks again.

 

[DJN]

No need to feel fooish. Sometimes you can't see the wood for the trees. Anyway, glad to be of help.