Pulling only Records After Last Payment Date

[LA Lawyer]

I am trying to use a form to open a report reporting only the charges since
the customer last paid a bill. Access 2007 fails when I try to determine
the LastPaymentDate by the CaseID number and confirming that this is a
payment. Here is my code (which fails on the asterik):

Dim LastPaymentDate As Date
* LastPaymentDate = DMax("MainDate", "CaseItems", "CaseItemType =
'Payment by Client' and CaseID= " & CaseIDNumber)
'MsgBox LastPaymentDate
DoCmd.OpenReport "Bills Report", acViewPreview, , "CaseID =" &
CaseIDNumber & " AND MainDate >= #" & LastPaymentDate & "#"

What am I doing wrong?

 

[Mike Painter]

I am trying to use a form to open a report reporting only the charges
since the customer last paid a bill. Access 2007 fails when I try to
determine the LastPaymentDate by the CaseID number and confirming
that this is a payment. Here is my code (which fails on the asterik):

Dim LastPaymentDate As Date
* LastPaymentDate = DMax("MainDate", "CaseItems", "CaseItemType =
'Payment by Client' and CaseID= " & CaseIDNumber)

Missing quotes and not enclosing field names with spaces.
DMax("MainDate", "CaseItems", "CaseItemType = '" & [Payment by Client] & "'
and CaseID= " & CaseIDNumber)

This assumes [payment by client] is text.


Spaces in field names is something to avoid like the plague. At 3:00AM it's
hard to tell [This field] from [this field]

For a query like this I usually either comment it out and replace it with a
msgbox with the code to make sure it looks right.

 

[L.A. Lawyer]

The reference to "Payment by Check" is text, not a reference to a field so
that was not the problem. Sorry about the confusion.

I am trying to use a form to open a report reporting only the charges
since the customer last paid a bill. Access 2007 fails when I try to
determine the LastPaymentDate by the CaseID number and confirming
that this is a payment. Here is my code (which fails on the asterik):

Dim LastPaymentDate As Date
* LastPaymentDate = DMax("MainDate", "CaseItems", "CaseItemType =
'Payment by Client' and CaseID= " & CaseIDNumber)

Missing quotes and not enclosing field names with spaces.
DMax("MainDate", "CaseItems", "CaseItemType = '" & [Payment by Client] &
"' and CaseID= " & CaseIDNumber)

This assumes [payment by client] is text.


Spaces in field names is something to avoid like the plague. At 3:00AM
it's hard to tell [This field] from [this field]

For a query like this I usually either comment it out and replace it with
a msgbox with the code to make sure it looks right.