Query records between two dates 1

[newguy86]

I'm having an issue getting a query to populate records based on the date range selected in my form.

I have a form that has 2 fields for dates (Start Date and End Date) so that when I run my reports I can specify the date range that I want to report off of. Both fields in the form are formatted as Short Date.

In my query I have a column that displays a reformatted date based on the completed date/time column from the table. Here's how I accomplished that:

DateValue([Completed])

Now in the criteria section of that column, I put in the following criteria so that I could select dates on my form and then when the query runs it will only display the records between the selected dates:

Between [Forms]![HomePage]![StartDate] And [Forms]![HomePage]![EndDate]

The issue that I'm having is that it doesn't return any records at all. I've checked to make sure that there are records that should be returning, the form field is setup as a date format (Short Date to be specific), after running the query as a Make Table query I've confirmed that the "Date" column in my query is coming through as a date value, and so I'm out of ideas as to what it could be.

Here's the SQL if that helps:

SELECT qryTTNewOrders.ContractNum_Ver, qryTTNewOrders.Type, qryTTNewOrders.SalesOffice, qryTTNewOrders.Submitted, qryTTNewOrders.Completed, qryTTNewOrders.[Turnaround Time], DateValue([Completed]) AS [Date]
FROM qryTTNewOrders
WHERE (((DateValue([Completed])) Between [Forms]![HomePage]![StartDate] And [Forms]![HomePage]![EndDate]));


Travis
Charter Media

 

[newguy86]

Correct me if I'm wrong. From a logical standpoint, that suggests either the reference or the data is either incorrect or missing and that the user should double check the setup of their form and make sure that their references are correct and that the data they want is available.

In this specific case, I have no idea what that suggests becuase given all of the evidence, it should have populated something in that query instead of returning a blank row.

Under loaded forms the only form listed in there says "HomePage", and here's the full list of objects for that form:

<Form>
StartDate_Label
StartDate
EndDate_Label
EndDate
Process_Data
Generate_Reports
Detail

Both StartDate and EndDate are in that list, the spelling matches up perfectly with the references in my query, and the form fields both have a date value in them (As was proven using the Debug Window you suggested earlier).

I'm all out of ideas. Do you know of anything else we could look at?

Travis
Charter Media

 

[dhookom]

I'm at a loss as to why the values from the controls don't display in the queries.

I never filter reports like you are attempting. I use code in a command button like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.StartDate) Thne
    strWhere = strWhere & " AND Completed >= #" & Me.StartDate & "# "
End If
If Not IsNull(Me.EndDate) Thne
    strWhere = strWhere & " AND Completed <= #" & Me.EndDate & "# "
End If
debug.Print strWhere
DoCmd.OpenReport "[Your Report Name Here]", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I forgot to mention, you would remove the date filtering from the saved query. If Completed is a string value, convert it with CDate([Completed]).

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Ok.

To be honest, at this point I've spent way too much time trying to figure this issue out so I'll give your alternate approach a try tomorrow morning when I'm back in the office.

I'll let you know what happens.

Travis
Charter Media

 

[newguy86]

I've tried using the code that you provided and I ran into a bit of an issue.

Because of the large amount of historical data in the table, my process is taking forever to run the report. So I need a way to filter out the data that doesn't fall within the date range before I reformat the data and run the report.

Oddly enough, when I attempted one last time to use the Between function on the first stage of queries I setup (where I take the data and seperate it out by status) and referenced the dates like I had been trying to do all along, for some reason it worked without any issues. I don't know why it worked. I just had a hunch and figured I would try it out.

Now as you would imagine after everything yesterday, I don't trust this to work flawlessly. So I was wondering if there was a way to apply the code you sent me at "13 Apr 15 21:52" or some form of it to a select query that I'm using to reorganize the data before reporting off of it?

Travis
Charter Media

 

[dhookom]

You can use similar code with a little DAO to change the SQL property of a saved query faq701-7433.

Duane
Hook'D on Access
MS Access MVP