UtterAccess Discussion Forums > Another Where Clause question

Full Version: Another Where Clause question

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

Macros

Jul 8 2008, 10:22 PM

And my phrase for the day is "Where Clause"

I have a form with a button to open another form

The 2nd form has a subform
On the subform is a control called txtPaidDate that holds a value called PaidDate

When I open the form it filters the form for just the selected Customer
I also want it to only show the events that have nothing in the PaidDate Field.
This is what I have on the click event, but it still shows the records with a paiddate, I've tried a few variations but no luck.

CODE

Private Sub cmdShowUnpaid_Click()
On Error GoTo Err_cmdShowUnpaid_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmCustEvents"

    [color="red"]stLinkCriteria = "[ID]=" & Me![ID] & " AND [PaidDate]="""[/color]

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdShowUnpaid_Click:
    Exit Sub

Err_cmdShowUnpaid_Click:
    MsgBox Err.Description
    Resume Exit_cmdShowUnpaid_Click

End Sub

The Form with the button is called frmCustomers
The form it opens is called frmCustEvents
The subform with the PaidDate field is called frmCustEventsSubForm
The PaidDate filed is DataType Date

It is definately the second part of the criteria that is the problem, without & " AND [PaidDate]="""" it works fine

Regards
Brendan

Steve Schapel

Jul 8 2008, 10:26 PM

Brendan,

It is doubtful that the PaidDate field would contain "" in fact if it is a Date/Time data type this would not be possible.

Try it like this:

stLinkCriteria = "[ID]=" & Me![ID] & " AND [PaidDate] Is Null"

Macros

Jul 8 2008, 10:42 PM

hmmm, still no luck, it pops up a dialog box wanting a value for PaidDate

Steve Schapel

Jul 9 2008, 01:13 AM

I really don't think luck is a factor here, Brendan.

Is PaidDate the name of a field in the Record Source of the frmCustEvents form?

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.