Where Clause Formulation

[Guest]

Hi all -

Application: Complaint Ticketing System based on a Table/Form named 'CList'
with:
1. Y/N field 'TClosed' (checked=Complaint resolved, unchecked=Complaint Open)
2. Date field 'CDate' (date that complaint was initially entered)

I'm struggling with the formulation of a where clause in the following
procedure.
The procedure is a Click event procedure for a button on the "CList" form
that should list unresolved complaints (TClosed=False) that are less than 3
days old (System Date minus CDate<3).

Private Sub btnListRecentOpenComplaints_Click()
DoCmd.OpenForm "CList", acFormDS, , "TClosed = False And Date-CDate<3",
acFormEdit, acWindowNormal
End Sub

"TClosed = False And Date-CDate<3" is incorrect because the Date function is
inside of the quotes and it's not recognized as a function. There may be
other problems with this clause, too. Any advice is appreciated.

 

[Marshall Barton]

Application: Complaint Ticketing System based on a Table/Form named 'CList'
with:
1. Y/N field 'TClosed' (checked=Complaint resolved, unchecked=Complaint Open)
2. Date field 'CDate' (date that complaint was initially entered)

I'm struggling with the formulation of a where clause in the following
procedure.
The procedure is a Click event procedure for a button on the "CList" form
that should list unresolved complaints (TClosed=False) that are less than 3
days old (System Date minus CDate<3).

Private Sub btnListRecentOpenComplaints_Click()
DoCmd.OpenForm "CList", acFormDS, , "TClosed = False And Date-CDate<3",
acFormEdit, acWindowNormal
End Sub

"TClosed = False And Date-CDate<3" is incorrect because the Date function is
inside of the quotes and it's not recognized as a function. There may be
other problems with this clause, too.

When you use a function in any context outside VBA, you must
use the parenthesis:
"TClosed = False And Date()-CDate<3"

The rest looks ok, but in a case where the CDate field might
contain a time part, it is safer to use the DateDiff
function:
"TClosed = False And DateDiff(""d"", CDate, Date())<3"

 

[Guest]

Thank you Marshall. I was experimenting around with the argument
parentheses, but just couldn't hit the mark. It worked perfectly and I did
convert to the DateDiff function.

Also, as you noted, the function is being used "outside the context of VBA."
Is it correct to say that this is because the DoCmd property exposes the form
itself to VBA and the Where Clause argument is "passed" to Access via the
OpenForm method? (Just trying to get my Access definitions and concepts
under control...).

 

[Marshall Barton]

Thank you Marshall. I was experimenting around with the argument
parentheses, but just couldn't hit the mark. It worked perfectly and I did
convert to the DateDiff function.

Also, as you noted, the function is being used "outside the context of VBA."
Is it correct to say that this is because the DoCmd property exposes the form
itself to VBA and the Where Clause argument is "passed" to Access via the
OpenForm method? (Just trying to get my Access definitions and concepts
under control...).

That's correct. The DoCmd method is executed by Access'
Application object, not by VBA.

It easy to tell when something will be executed in the VBA
environment. When you type a statement using odd case (e.g.
dAtE), VBA will redisplay the statment using the item's
declaration case (so don't declare a variable named dAtE
;-)