Link to home
Start Free TrialLog in
Avatar of UTSWPeds
UTSWPedsFlag for United States of America

asked on

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.
Avatar of UTSWPeds
UTSWPeds
Flag of United States of America image

ASKER

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.
Avatar of Jeffrey Coachman
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...?
\
>   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
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
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.
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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"