Solved: query based on user-entered dates

[AJS]

Hi,

I'm pretty new to access vba programming. I'd like to create a report that prompts the user to enter a start date and an end date, and then uses those dates as a filter in the "job completed" date field on all available records. I'm not sure how to code this. I've tried making a seperate calendar form and table but I don't seem to be able to use this approach, as there is no defined relationship between the two tables. I'm also not sure how to then create a report using these user-entered dates! Any ideas?

Thanks, Aaron

[rconverse]

Well, to create a query with dates, just surround the dates in #'s.



dim date1 as date, date2 as date

strsql = ...WHERE ((tblOpenPO.[Enter Dt]) Between #" & date1 & "# And #" & date2 & "#)...


How is the report being triggered, via a button on a form? Are you going to have someone input the dates into a form? You could also use two input boxes that the person could enter a start and end date.

HTH
Roger

[AJS]

How is the report being triggered, via a button on a form? Are you going to have someone input the dates into a form? You could also use two input boxes that the person could enter a start and end date.

Yes, the query is being triggered via a button on the form. I'd like to use two input boxes, but I'm not sure how to show calendars in such.

Thanks, Aaron

[Trevor]

create a report and a query both with the fields you want displayed, set the controlesource(or recordsoucre) of the report to the name of the query,
then build a form with 2 textboxes [Datefrom] , and [Date To] then in the criteria of the query you made add the line: >=[forms]![form Name]![DateFrom] And <=[forms]![form name]![DateTo].

on your form create a button (call it whatever u'd like) it will be used to create your report , and on the on click event for the button past code:
Dim stDocName As String

stDocName = "reportname"
If Len(Me.DateFrom & vbNullString) = 0 Or Len(Me.DateTo & vbNullString) = 0 Then
MsgBox "Please ensure that a report date range is entered into the form", _
vbInformation, "Required Data..."
Exit Sub
Else
DoCmd.OpenReport stDocName, acPreview
End If
Exit_Print_Preview_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_Print_Preview_Click


End Sub ' note when you copy and past make sure you only have 1 end sub at the end of this button code

[DarkSprout]

Or you could write a Function that Looks at the date field on the form.


Public Function qryStartDate() As Date
On Error Resume Next
qryStartDate = Forms![frm_FormName].Form![ctlControlName]
End Function


Then in your Queries Criteria field you could use >=qryStartDate()
or what ever your condition...

Hopy this Helps,
=Darryl=

[AJS]

Thank you all for your input! The form is now working as intended (FYI I wound up using Trevor's suggestion, but all input is appreciated).

Cheers, Aaron