View All Records CMD button error

[Jan :\)]

Hi all, Access 2007, Vista SP2, Ultimate 32 bit

I have a filter form on which I have combo boxes to filter by specific
criteria. I also have a command button that should open the record display
form to show all records with the following behind the button:
~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cmdViewAll_Click()
On Error GoTo Err_cmdViewAll_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMedsRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewAll_Click:
Exit Sub

Err_cmdViewAll_Click:
MsgBox Err.Description
Resume Exit_cmdViewAll_Click
End Sub
~~~~~~~~~~~~~~~~~~~~

However, when clicked I am getting the following error:

"This expression is typed incorrectly; or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Not sure what that means in this case, as I used the command button tab to
create the button, nothing fancy. However, I have a From/To date function
set up in the related query to filter selected data from the combo boxes by
date period. The SQL for that is:

SELECT tblMeds.Provider, tblMeds.ProvFrom, tblMeds.RxName, tblMeds.RxNo,
tblMeds.RxDosage, tblMeds.RxStrength, tblMeds.RxType, tblMeds.RxFor,
tblMeds.IssueDate, tblMeds.LastFilled, tblMeds.RefillNo, tblMeds.RenewDate,
tblMeds.Active, tblMeds.InactiveDate, tblMeds.Comment
FROM tblMeds
WHERE (((tblMeds.IssueDate) Between Forms!frmMedsFilter!TxtDate1 And
Forms!frmMedsFilter!TxtDate2)) Or (((Forms!frmMedsFilter!TxtDate1) Is Null))
Or (((Forms!frmMedsFilter!TxtDate2) Is Null));

I am not sure if the date function is causing the error, but, if so is there
a way to disregard that function when we want to view all records. Any
suggestions would be very much appreciated.

Jan

 

[Daryl S]

Jan -

What do you want to happen if TxtDate1 or TxtDate2 is null? Do you want all
records returned? Or do you want to filter on any date you have (e.g.
IssueDate > TxtDate1 if TxtDate2 is null)? If you want the latter, try
changing your WHERE clause to this:

WHERE (((tblMeds.IssueDate >= nz(Forms!frmMedsFilter!TxtDate1,#1/1/1900#))
And
(tblMeds.IssueDate <= nz(Forms!frmMedsFilter!TxtDate2,#12/31/2200#)))) ;

The two dates are arbitrary, but should be well out of range that any valid
date should be.

 

[Jan :\)]

Thanks, Daryl, sorry to be so late in responding, and I do appreciate your
time to reply.

Yes...all I want all records returned. I want it to simply display all the
records without any filtering, that the problem is presented.

Thank you for stepping in.

Jan

 

[Daryl S]

Jan -

Did you try the WHERE clause I gave you? That should do it for you.