UTSWPeds
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_DateBe gin] 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_D ateBegin.V alue Is Null Or Forms!frm_DateRanges.txt_D ateEnd.Val ue Is Null Then
MsgBox ("Please select a Begin and End Date for your request")
End If
stBegin = Forms!frm_DateRanges.txt_D ateBegin.V alue
stEnd = Forms!frm_DateRanges.txt_D ateEnd.Val ue
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.
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_D
MsgBox ("Please select a Begin and End Date for your request")
End If
stBegin = Forms!frm_DateRanges.txt_D
stEnd = Forms!frm_DateRanges.txt_D
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.
This is not really a valid SQL statement:
SELECT [Forms]![frm_DateRanges]![ txt_DateBe gin] AS X;
This will evaluate ti something like:
SELECT 5/28/2013 as X
Not sure what you are trying to do with this...?
\
SELECT [Forms]![frm_DateRanges]![
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_D ateBegin.V alue Is Null Or Forms!frm_DateRanges.txt_D ateEnd.Val ue 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
> 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_Da te] >=" & "#" & stBegin & "#" & " AND " & [Termination_Date] <=" & "#" & stEnd & "#"
Me.FilterOn=True
Something like this?:
Me.Filter="[Termination_Da
Me.FilterOn=True
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
;-)
ASKER