Add date search to my form with the combo boxes 1

[juniormint2009]

Hey All, the help I got was awesome! Thanks again! I am realizing that I really don't need my status combo box but will probably leave it on my form anyway incase I want to search by that, now I have a new question.
How would I add a search function to search for date ranges ?
My field is called "Opened Time" and is a date/time type that appears like this "11/21/2008 6:46:17 AM", now I would like to be able to search for records say after a certain date and or time, what would be the way I could insert that into my form to go along with my other 3 combo boxes, I guess I really don't need to search by time, just date, like all records after "11/21/2008" and before "11/27/2008" or any date I pick.
Thanks again!!

 

[dhookom]

You might want to create a couple new fields in your query like:
[tt][blue]
StartDateateValue([Opened Time])
EndDateateValue([Opened Time])
[/blue][/tt]
Then use the same expressions as the previous reply only instead of "=", use ">=" and "<=".

<=Forms!frmYourForm!txtEndDate or Forms!frmYourForm!txtEndDate Is Null

and

>=Forms!frmYourForm!txtStartDate or Forms!frmYourForm!txtStartDate Is Null

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

Thank you again dhookom! I am in the process of adding it, I have been working on outputing the query to excel and having decent luck. I am burnt out now though, gotta work on this tommorow. Thanks again for all the help! Now I have a great query and my my forms are looking good. This is going to be an intense project. Have a great night all!

 

[juniormint2009]

ok, below is my SQL, I made a table tblOpentime with date() as the default value for the field Opened Time, I named the text box txtOpentime the form fills in the defualt date but the search does not pull it based on the date, all else works fine if I take out that date part, I figured I would make a table called tblOpentime with 1 field called Opened Time with date/time type as a typical date like 3/23/2009, my database has the date as "11/21/2008 6:46:17 AM", is that why ?? I just want to be able to put in a date to pull records on or after that date if they choose it.

SELECT dbo_probsummarym1.*
FROM dbo_probsummarym1
WHERE (((dbo_probsummarym1.[Opened Time])>=[Forms]![comp_search]![txtOpentime]) AND ((dbo_probsummarym1.[Opened By])=[Forms]![comp_search]![cboAgents]) AND ((dbo_probsummarym1.[Ticket Status])=[Forms]![comp_search]![cboStatus]) AND ((dbo_probsummarym1.Severity)=[Forms]![comp_search]![cboSeverity])) OR (((dbo_probsummarym1.[Opened By])=[Forms]![comp_search]![cboAgents]) AND ((dbo_probsummarym1.[Ticket Status])=[Forms]![comp_search]![cboStatus]) AND ((dbo_probsummarym1.Severity)=[Forms]![comp_search]![cboSeverity]) AND (([Forms]![comp_search]![txtOpentime]) Is Null)) OR (((dbo_probsummarym1.[Opened By])=[Forms]![comp_search]![cboAgents]) AND ((dbo_probsummarym1.[Ticket Status])=[Forms]![comp_search]![cboStatus]) AND ((dbo_probsummarym1.Severity)=[Forms]![comp_search]![cboSeverity]) AND (([Forms]![comp_search]![txtOpentime]) Is Null)) OR (((dbo_probsummarym1.[Ticket Status])=[Forms]![comp_search]![cboStatus]) AND ((dbo_probsummarym1.Severity)=[Forms]![comp_search]![cboSeverity]) AND (([Forms]![comp_search]![cboAgents]) Is Null)) OR (((dbo_probsummarym1.[Opened By])=[Forms]![comp_search]![cboAgents]) AND ((dbo_probsummarym1.Severity)=[Forms]![comp_search]![cboSeverity]) AND (([Forms]![comp_search]![cboStatus]) Is Null)) OR (((dbo_probsummarym1.Severity)=[Forms]![comp_search]![cboSeverity]) AND (([Forms]![comp_search]![cboAgents]) Is Null) AND (([Forms]![comp_search]![cboStatus]) Is Null)) OR (((dbo_probsummarym1.[Opened By])=[Forms]![comp_search]![cboAgents]) AND ((dbo_probsummarym1.[Ticket Status])=[Forms]![comp_search]![cboStatus]) AND (([Forms]![comp_search]![cboSeverity]) Is Null)) OR (((dbo_probsummarym1.[Ticket Status])=[Forms]![comp_search]![cboStatus]) AND (([Forms]![amtrak_help_desk]![cboAgents]) Is Null) AND (([Forms]![comp_search]![cboSeverity]) Is Null)) OR (((dbo_probsummarym1.[Opened By])=[Forms]![comp_search]![cboAgents]) AND (([Forms]![comp_search]![cboStatus]) Is Null) AND (([Forms]![comp_search]![cboSeverity]) Is Null)) OR ((([Forms]![comp_search]![cboAgents]) Is Null) AND (([Forms]![comp_search]![cboStatus]) Is Null) AND (([Forms]![comp_search]![cboSeverity]) Is Null));

 

[dhookom]

Rather than trying to figure all this out, I would just build a SQL string in code and set it as the SQL property of a saved query:

Dim strSQL as String
strSQL = "SELECT dbo_probsummarym1.* FROM dbo_probsummarym1 WHERE 1 = 1 "
If Not IsNull(Me.txtOpentime) Then
   strSQL = strSQL & " AND DateValue([Opened Time])>=#" & Me.txtOpenTime & "# "
End If
If Not IsNull(Me.cboAgents) Then
    [Green]'assumes Opened By is text[/green]
    strSQL = strSQL & " AND [Opened By] = """" & Me.cboAgents & """ "
End If
[green]'do the same with the other controls to build strSQL[/green]
CurrentDb.QueryDefs("queryToExportName").SQL = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

hmm, all that just for the date ? I was actually hopeing to ad like a calender in there to select it.
Didn't realize it was so hard.
Oh well, guess I will try it that way.
Thanks for the help!

 

[dhookom]

You don't have to do it the code way. It's just how I would create the solution. You can use a calendar form to pick the date.

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

Thanks dhookom, I understand your logic and totally appreciate the help. But what I really need to do is have a between date function on that same page as my combo boxes so they can pick records in a date range like after 1/1/2009 and before 1/10/2009 and also with the criteria from the combo box, like certain people (my Opened By choice combo box), I need to add a few more combo boxes which I am hoping goes ok but the date option is really important so I was hoping I could just add that easily to the form and in the query, like put a 2 text boxes on the form for Startdate and Enddate and have it do a >=startdare and <= enddate. I assumed it would be easy but I am not getting the results I expected. Is it because the date field Opened Time in my main table is the long date like "11/21/2008 6:46:17 AM" ??
I am unsure of the correct properties on the form when adding the text boxes for startdate and enddate.
It does not have to be a calender, I just thought that would look nice.
Any easy way to do the text boxes on the form and add it to the query so it goes along with the combo box criteria ??
Thanks again!!

 

[dhookom]

Did you try my first suggestion? I don't see any use of DateValue(). Your SQL contains "txtOpentime" which isn't anywhere in my suggestion.

To be honest, I don't want to decipher your SQL view and the where clause. I just thought you would create two new columns in your query and set the criteria as I suggested.

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

what I tried was adding a text box to my form in short date format called txtstartdate, then in my query, I tried to add the criteria in a new cell with the "opened by" field as the field and the same database dbo_probsummarym1 and the criteria below, but it doesn't work. If I do just a seperate query with this criteria it works, it pulls records opened by the date on or after what I input in my form into text box txtstartdate.
But why can't I just add this into my existing query so it also goes with the combo boxes??
>=[Forms]![comp_search]![txtstartdate]

 

[dhookom]

Why would you set the date criteria "in a new cell with the "opened by" field as the field"? Isn't the Opened By field the agent and not a date?

I thought I suggested you use the criteria under [Opened Time] and further modify the field by wrapping it in the DateValue() function.

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

sorry, typo, I added the field "Opened Time" to the query with the criteria set to, the table is the same as the others, dbo_probsummarym1
Criteria is below. If I use it in another query with just that as the parameter it works but if I try and add it to this query with the other 3 options it doesn't pull the records with opened time field after what I fill in the text box txtstartdate.

>=[Forms]![comp_search]![txtstartdate]

where txtstartdate is a text box of short date type on my form.

 

[dhookom]

What are you entering into txtStartDate? Are your dates m/d/y or some other format? What do you see if you enter:
>=#1/31/2009#

What do you get if you use my suggested expression and leave txtStartDate empty:
>=[Forms]![comp_search]![txtstartdate] OR [Forms]![comp_search]![txtstartdate] Is Null

When you view your query, are all of the criteria in the same line?


Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

In my form, I enter a date like 02/01/09.
I entered the criteria into my query, I inserted a new colum so its the 1st set of parameters, when I close and save it and then view it again, the criteria for the opened time field is >=[Forms]![comp_search]![txtstartdate]
and then it adds a columm for the is null part.
The field is set to >=[Forms]![comp_search]![txtstartdate]
and the criteria is Is Null, the others are the same with the fields from my combo boxes but no Is Null, seems the Is Null goes into the 1st colum of those sets each time and they all work fine with my 3 combo box parameters, just not with this date. The txtstartdate text box is set to short date type.

 

[juniormint2009]

So is this possible or am I doomed ??

 

[dhookom]

Ok, try repost your SQL view. But you might also try to explain what you mean by "it adds a column for the is null part". How could a field be set to ">=[Forms]![comp_search]![txtstartdate]"? I would expect that to be in the criteria under you date column.

Duane
Hook'D on Access
MS Access MVP