MS Access form issue with passing dates to a query

My issue is that I have a form with two unbound text boxes (txt_DateBegin, txt_DateEnd) that I’m trying to use to pass date parameters to an “On Click” event on a button on the same form (frm_DateRanges).  I want users to key in date ranges, then the event will pass those dates to a query where clause.  When I try this, I get and “Object Required” error.  Addionally, when I try to run a query with the value from the form, I get a blank result:  [SELECT [Forms]![frm_DateRanges]![txt_DateBegin] AS X;]  
When I change the format in properties from Short Date to make it a true text value, the query works, it passes the text value when I run the select statement above.
My VBA for the “On Click” event is below:
Private Sub cmd_Terms_Click()
On Error GoTo Err_cmd_Terms_Click

    Dim stDocName As String
    Dim stBegin As Date
    Dim stEnd As Date
    Dim stCriteria As String
   
    If Forms!frm_DateRanges.txt_DateBegin.Value Is Null Or Forms!frm_DateRanges.txt_DateEnd.Value Is Null Then
     MsgBox ("Please select a Begin and End Date for your request")
    End If
   
    stBegin = Forms!frm_DateRanges.txt_DateBegin.Value
    stEnd = Forms!frm_DateRanges.txt_DateEnd.Value
    stCriteria = "[Termination_Date] >= #" & stBegin & "# and [Termination_Date] <= #" & stEnd & "#"
    Debug.Print stCriteria

    stDocName = "qry_FacGenRpt_ALL"
    DoCmd.OpenQuery stDocName, acNormal, acReadOnly
    DoCmd.ApplyFilter , stCriteria

Exit_cmd_Terms_Click:
    Exit Sub

Err_cmd_Terms_Click:

    MsgBox Err.Description
    Resume Exit_cmd_Terms_Click

End Sub

Any help getting these dates to pass appropriately is much appreciated.
UTSWPedsBudget ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UTSWPedsBudget ManagerAuthor Commented:
My issue could be in my VBA, but I don't think so because of the fact that I can't even pass a value to a select statement as mentioned above.
0
Jeffrey CoachmanMIS LiasonCommented:
This is not really a valid SQL statement:
SELECT [Forms]![frm_DateRanges]![txt_DateBegin] AS X;  

This will evaluate ti something like:
   SELECT 5/28/2013 as X

Not sure what you are trying to do with this...?
\
0
jerryb30Commented:
>   If Forms!frm_DateRanges.txt_DateBegin.Value Is Null Or Forms!frm_DateRanges.txt_DateEnd.Value Is Null Then
 >    MsgBox ("Please select a Begin and End Date for your request")
>    End If
You don't send the user back to do anything.

I also think you need to check if the value is a legal date.

ReEnter:
If isdate(me.DateBegin) and Isdate(me.DateEnd) then
  Do something
else
MsgBox ("Please select a Begin and End Date for your request")
goto ReEnter
endif
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
I may be missing something but, why not Just Filter the form from the criteria?
Something like this?:

    Me.Filter="[Termination_Date] >=" & "#" & stBegin & "#" & " AND " & [Termination_Date] <=" & "#" & stEnd & "#"
    Me.FilterOn=True
0
UTSWPedsBudget ManagerAuthor Commented:
1) The select statement is just a test on my end to see if the text boxes are passing values...they are not.
2) The null thing will be deleted, it is not the thrust of my problem here.
3) Filter vs. Criteria aside, my issue is that the text boxes are not passing any values when I click the command buttons...when I click, I get an 'Object Required' error.

The first and foremost problem I want to solve is why when I have dates input into the form, they won't pass to the criteria or filter or whatever else I'm using.  Until I can utilize the date values the user inputs into the text boxes, all else is irrelevant.
0
Jeffrey CoachmanMIS LiasonCommented:
This is what I mean, it is not clear why you are doing what you are doing...?

1. You did not post any info on the query, but it would need parameters included in the criteria to look at the date textboxes in order to work. (I will presume that you have this in place...)

2. Also Comment out your error handling so you know what line of code it fails on.

3. The code fails with "Object Required" because you cannot use "Is Null" in this way, you must use IsNull()

4. You also need an:     GoTo Exit_cmd_Terms_Click
...line after the message box to avoid the "invalid use of null" error.

5. You are opening the query,  then in the next line you are trying to apply the filter (for the form I presume)
This will not work, Docmd.Apply filer will try to execute for the "current object that has the focus*, ..in this case it is the query, so Docmd.Apply filter is not a valid command for a query...  You must instead use:
Me.filter/me.FilterOn=True (as I stated in my example)
(This will filter the form whether it has the focus or not)


All in all, this code works just fine for me...

Private Sub cmd_Terms_Click()
On Error GoTo Err_cmd_Terms_Click

 Dim stDocName As String
 Dim stBegin As Date
 Dim stEnd As Date
 Dim stCriteria As String


    If IsNull(Forms!frm_DateRanges.txt_DateBegin) Or IsNull(Forms!frm_DateRanges.txt_DateEnd) Then
        MsgBox ("Please select a Begin and End Date for your request")
        GoTo Exit_cmd_Terms_Click
    End If
   
    stBegin = Forms!frm_DateRanges.txt_DateBegin.Value
    stEnd = Forms!frm_DateRanges.txt_DateEnd.Value
    stCriteria = "[Termination_Date] >= #" & stBegin & "# and [Termination_Date] <= #" & stEnd & "#"
    Debug.Print stCriteria

    stDocName = "qry_FacGenRpt_ALL"
    DoCmd.OpenQuery stDocName, acNormal, acReadOnly
   
    Me.Filter = stCriteria
    Me.FilterOn = True

Exit_cmd_Terms_Click:
    Exit Sub

Err_cmd_Terms_Click:

    MsgBox Err.Description
    Resume Exit_cmd_Terms_Click

End Sub



Hope this clears all of your issue up...
;-)

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UTSWPedsBudget ManagerAuthor Commented:
Very nice clean up of an error on my end in the IsNull() area.  I assume the problem was using Me! instead of Forms!frm_DateRanges.  Effectively working code now, so I'm going to give this an "A"
0
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.