Query my Databse using Dates

Try the site below for a good tutorial, also remember to ensure that the data type for the date field in the table is date/time.

http://www.geekgirls.com/databasics_07.htm#daterange

Hi. You need to set up a forms dialog in the Access forms for the from and to date. You can find an example of this in the Northwinds database. Below is the code for VB. You need to set up 2 text boxes for the user to enter the from and to date and then the program will pass them back to Access and execute the query.
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRS As New ADODB.Recordset
Dim objParam As ADODB.Parameter
Dim count As Integer

'set up the database pointer
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=E:\ACCESS\CONTRACT HAULERS\HAULER.mdb;User ID=ADMIN"
objRS.CursorType = adOpenDynamic
objRS.LockType = adLockOptimistic

'setting the Active Connection for the command object
objCmd.ActiveConnection = objConn

'Providing the query name as commandtext
objCmd.CommandText = "[DATE RANGE QUERY]"
objCmd.CommandType = adCmdStoredProc

'create the parameter and populate it
Set objParam = objCmd.CreateParameter("@Fromdate", adChar, adParamInput, 10, 0)
objCmd.Parameters.Append objParam
Set objParam = objCmd.CreateParameter("@Todate", adChar, adParamInput, 10, 0)
objCmd.Parameters.Append objParam

'Assign the values for the parameters
objCmd.Parameters("@Fromdate").Value = CStr(txtFromDate)
objCmd.Parameters("@Todate").Value = CStr(txtToDate)
objCmd.CommandText = "[DATE RANGE WITH PLANT AND VENDOR QUERY]"
'Set the recordset to the command object result
Set objRS = objCmd.Execute

If you need any additional help, email me.
Cathy
covcat@riverstonegrp.com

Take a look at the sample NorthWind.MDB.
Open the Sales by Year Report in design view.
Open the Properties display for the report and choose the Event tab.
Click in the On Open "[Event Procedure]" and then the "..." button.
This is the code usedto use a parameter selection to create the report.

In the query, create fields that represents the dates in text format "yyyymmdd". by doing this, you can refer to the date as a string and it also understands >, <, Between. You can also order them in chronological order as it sorts by year, month,day.

IE: If your date field is called "FirstDate", in your query QBE Grid, in one of the columns enter THIS_DATE:Format(FirstDate,"yyyymmdd"). This will create a field in the query called THIS_DATE with the date formatted as a string value.

Request the user enter the dates in as the above format (if its a parameter query) or if calling from a form, the date fields (call fields txtDate1 and txtDate2) as date formats and open the report with the following code

Dim strCriteria as String
strCriteria = "THIS_DATE >= '" & Format(txtDate1,"yyyymmdd") & "' And THIS_DATE <= '" & Format(txtDate2,"yyyymmdd") & "'"

DoCmd.OpenReport "MyReport", , , strCriteria

Be sure you are using the date formatted correctly (i.e., not using dd/mm/yyyy if the database is set up for US style of mm/dd/yyyy).

One way to do this easily is to build a form with a control for the start date (txtFrom), the end date (txtTo), and a print report button (cmdPrint).

txtFrom and txtTo should include a format property to format them in the correct date formats.

When user clicks cmdPrint, do the following (I'm using Access 97 code, so you may need a few minor changes):
' check that dates have been entered (use whatever dates are appropriate for your data)
if me.txtFrom < #1/1/1990# or me.txtTo < #1/1/2010# then
msgbox "You forgot to enter the dates!"
exit sub
end if

docmd.OpenReport("rptName",acViewNormal,,"[DataDate] Between #" & me.txtFrom & "# And #" & me.txtTo & "#")

The # signs are necessary because you are using dates, therefore the where clause for dates of 1/1/2000 and 1/31/2000 will end up as "[DataDate] Between #1/1/2000# and #1/31/2000#" when passed to open the report.

You can handle this other ways, like just building a query to use as the record source for the report where the query includes the criteria prompts (enter as >= [Start date] and <= [End date] so the user will beprompted because of the brackets), but the form is cleaner, a better user interface, and ends up easier to use.