INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query records between two dates

Query records between two dates

(OP)
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

RE: Query records between two dates

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:

CODE --> SQL

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

RE: Query records between two dates

(OP)
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

RE: Query records between two dates

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

RE: Query records between two dates

(OP)
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:

CODE

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

RE: Query records between two dates

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

RE: Query records between two dates

(OP)
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

RE: Query records between two dates

Quote (newguy86)

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

RE: Query records between two dates

(OP)
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

RE: Query records between two dates

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

RE: Query records between two dates

(OP)
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

RE: Query records between two dates

(OP)
No.

Travis
Charter Media

RE: Query records between two dates

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

CODE --> ImmediateWindow

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

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

Duane
Hook'D on Access
MS Access MVP

RE: Query records between two dates

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

Travis
Charter Media

RE: Query records between two dates

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:

CODE --> SQL

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

Duane
Hook'D on Access
MS Access MVP

RE: Query records between two dates

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

Travis
Charter Media

RE: Query records between two dates

(OP)
Sorry. There was a single blank row.

Travis
Charter Media

RE: Query records between two dates

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

RE: Query records between two dates

(OP)
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

RE: Query records between two dates

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:

CODE --> vba

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

RE: Query records between two dates

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

RE: Query records between two dates

(OP)
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

RE: Query records between two dates

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close