Using Start Date and End Date in Access Query

MS Access: Using a Date Between a Start Date and End Date for a Query

There are a number of ways of querying this depending on your end goal.

Have a read here for a good visual representation of how to get the results http://www.baldyweb.com/OverLap.htm

In a simple query hard coded to one date this would equate to Criteria like

[End Date] >= #02/14/2017# AND [Start Date] <= #02/14/2017#

MS Access automatically fill Query column based on start and end date

Assuming that your dates are stored using DateTime fields and that your date ranges are contiguous (no gaps), you can determine the appropriate session name for your date using the following correlated subquery:

SELECT q.*,
(
    SELECT TOP 1 r.Name 
    FROM Ranges r 
    WHERE q.MyDate <= r.[End date] ORDER BY r.[End date]
) AS SessionName
FROM [Records Query] q

Here, I assume that your date ranges are stored in a table called Ranges and that the date field in your Records Query query is called MyDate (change this to suit your data).

In the above example, I'm assuming you have the following setup:

Ranges

Records Query

SQL

Result

Unfortunately, you cannot simply inject this subquery into an UPDATE query -

UPDATE [Records Query] q
SET q.Session = 
(
    SELECT TOP 1 r.Name 
    FROM Ranges r 
    WHERE q.MyDate <= r.[End date] ORDER BY r.[End date]
)

...as the resulting query will not be updateable (as the new value references the table being updated).

As such, you will likely need to output the session names to and primary key from your Records Query query to a local table which can then be used to update your query - others may hopefully know a more elegant way to avoid this.

MS ACCESS Query dates range from linked Excel return wrong end date

This might be because the data has both date and time parts, so the combined datetime is greater than the date part alone. Try using DateValue():

WHERE DateValue(TransactionDetail.transactionDate) >= Forms![Reports]![Text0] AND DateValue(TransactionDetail.transactionDate) <= Forms![Reports]![Text1]

MS Access Query date range when last date not found

You're missing some parentheses that are affecting the evaluation order, causing the behavior that you're reporting.

What you want is for each of the BETWEEN portions to be evaluated completely before the OR option is evaluated, and you need to make sure that evaluation is done by surrounding the BETWEEN expressions in parentheses to guarantee the evaluation order.

This should correct it (untested, as you've not provided the test data necessary to create a test case).

UPDATE WXObs SET WXObs.SnowFlag = 1     
WHERE 
  (WXObs.StationID ="451409") 
And
  (
   (WxObs.ObsDate Between #1/3/2003# AND #3/29/2003#) OR
   (WxObs.ObsDate Between #11/16/2003# AND #5/7/2004#) OR
   (WxObs.ObsDate Between #10/30/2004# AND #4/30/2005#)
  );

Microsoft Access/SQL/Date Range/ when adding end date, query does not return results

Microsoft Access does not use quotes for a field name - quotes are used to define a string.

You are comparing your date to text. A letter is after numbers so your C for "Current Date" will be after any date.

I forgot how to handle names with spaces in Access - I think you use brackets -

SELECT *
FROM Intake
WHERE [Current Date] >= [Enter Start Date, mm/dd/yyyy]
AND [Current Date] <= [Enter End Date, mm/dd//yyyy];

https://msdn.microsoft.com/en-us/library/bb208930(v=office.12).aspx

Creating a form where user inputs start and end dates of a report

There are a few ways to perform this.

The easiest, and most straight forward, in my personal opinion, involve creating a form where the user(s) will be entering Start/End dates, using a query that captures all of the information necessary for your report (including your date parameters), and the report with your query as the recordsource.

Form

Create a form with 2 text box controls: Name them whatever you like (StartDateTxt, EndDateTxt, for example), and a button control.

Query

Now, create a query, that retrieves all of the correct information you need for this report. I know you said selling price, for starters.

For the date fields you want to narrow down your search by, you need to put them in the WHERE clause or the Criteria field in QBE. It might look something like:

[Forms]![YourFormName]![StartDateTxt] and [Forms]![YourFormName]![EndDateTxt]

Note: Referencing fields can sometimes be tricky.

Report

Next, you need to create a report. If you're new to this, you can use the Report Wizard located at: Create - Reports section - Report Wizard.

You can select your query from the dropdown list on the first prompt, and add the fields you desire/need. Click next: Here you can sort your order of appearance on the report. Click next: Then you can justify your layout - if you aren't familiar with the differences, you can play around with them. Click next: Select a 'theme' to be applied to your report (font/colors, etc). Click next: Then you can either preview the report or Modify the report's design. (Really, you can do either, by clicking either button and clicking print preview or design view.)

If you click on Preview the report - Access should ask you for any parameters your underlying query requires.

Back to the form.

Right click on your button control. -> Properties -> Event -> OnClick -> click the [...] icon to the very right of the row. Select code builder.

VBA

In-between Private Sub and End Sub, you're going to write

DoCmd.OpenReport "YourReportName"

You can also check to make sure your report isn't already open.

Dim ReportName As String
ReportName = "YourReportName"

            If CurrentProject.AllReports(ReportName).IsLoaded Then
                DoCmd.Close acReport, ReportName
            End If

            DoCmd.OpenReport ReportName, acViewPreview

I hope this helps you/answers your question.

Edit: If anyone wants to edit/add on to this either by means of clarification or another alternative approach, please feel free. Just let me know.

---

Related Topics