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

 

[dhookom]

I would never alias a column with the name of a function: "AS [Date]". DateValue() will error if there are blanks/nulls.

I don't know why you would be using DateValue() if Completed is a date/time field. The "Format" property of a field has no effect on how the data is stored.

Try this:

SELECT TOP 100 qryTTNewOrders.ContractNum_Ver, qryTTNewOrders.Type, qryTTNewOrders.SalesOffice, qryTTNewOrders.Submitted, qryTTNewOrders.Completed, 
qryTTNewOrders.[Turnaround Time], DateValue([Completed]) AS [CompletedDate], [Forms]![HomePage]![StartDate] as StartDate, [Forms]![HomePage]![EndDate] As EndDate
FROM qryTTNewOrders;

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

That's a good point, I'll go ahead and change the name of the field.

And although the Format function doesn't change how the data is stored, it does cause the output in the query to come up as a string instead of a date value. Which becomes a problem when I need to compare the date value from the form with the string value from the query.

Travis
Charter Media

 

[dhookom]

I don't see any reference to the Format function in your SQL views. Where are you applying this? I typically never apply a format to a date field until it arrives at its final destination which is typically a form or report.

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

I tried the code you submitted and it originally returned every single record in the table instead of just the records that fall between the 2 dates that I specified, so I added the between criteria and it's now not showing any records again.

The format function is what I used before I started using DateValue(). I switched it because I figured that access couldn't compare a date value from my form with a string value from my query. I can change it back to format but I'm still not getting any of my records to appear.

Here's the updated SQL:

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

Travis
Charter Media

 

[dhookom]

So does qryTTNewOrders take a perfectly good date and convert it into a useless string? Again, there is typically now reason to format a date until it gets to a form or report. If you have a string date, convert it use CDate().

The query I had provided that showed all the records had two extra columns based on the dates entered into the controls on the form. Did they look as expected?



Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

It doesn't appear so.

When I setup qryTTNewOrders as a Make Table query and ran it, the [Completed] field came out as a date/time format and the [CompletedDate] field came out as a text format. That was when I was using the format function for the [CompletedDate] field. Once I switched to the DateValue function the output for [CompletedDate] changed to a date/time format.

The fields you added in the query came out blank when the query ran.

Travis
Charter Media

 

[dhookom]

The fields you added in the query came out blank when the query ran.

This suggests the references to the controls on the form are wrong or there are no values in the controls. Are you sure you have entered actual date values in the [Forms]![HomePage]![StartDate] and [Forms]![HomePage]![EndDate] controls?

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

There's information in those form fields and the format of them both is Short Date. And I'm able to see the value in those fields through a message box using VBA.

So I'm stumped as to where else I can look.

Travis
Charter Media

 

[dhookom]

Do you have code in the form that might automatically close it after some event? Do you understand the query I provided on "10 Apr 15 22:32"? It should have displayed the values from the text boxes if the form was indeed open and there were matching text boxes with values.


Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

No. The only code in there that is when the form loads, a VBA function runs to open another database, update a table of dates, and then close that other database and return back to the original database.

I do understand the query you provided. It basically just attaches the field values in the form to each record in my query. Which you're right it should have worked but I've checked everything I can think of and nothing seems out of place. I even went as far as changing the format of the fields in the form to just plain textboxes and I still can't get the values to show up.

Travis
Charter Media

 

[dhookom]

Are the text boxes on a subform?

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

No.

Travis
Charter Media

 

[dhookom]

When you have the form open and some dates entered, open the Debug window (press Ctrl+G) and enter:

?[Forms]![HomePage]![StartDate]

Do you see a date? If not your names are messed up.

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Yes. The date that's in my form field appears.

Travis
Charter Media

 

[dhookom]

If that's the case then the same expression should be available in the query.

With your form open and values entered into the text boxes, create a query with SQL view of:

SELECT TOP 1 [Forms]![HomePage]![StartDate] as StDate, [Forms]![HomePage]![EndDate] as EnDate 
FROM msysobjects

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

I copy and pasted exactly what you wrote into the SQL view of a new query and nothing came up.

Travis
Charter Media

 

[dhookom]

Not even a blank row?

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

Sorry. There was a single blank row.

Travis
Charter Media

 

[dhookom]

This suggests what?

Try use the Expression builder and drill down through loaded forms to find the actual form and control names.

Duane
Hook'D on Access
MS Access MVP