VBA for Showing Report If Field is blank/null?

[MBM]

I have created an overdue payment letter and linked the report to a form. How do I display the report only if the Date Paid field from the current record on the form is blank/null? I need to add a condition to this line?

stWhere = "[Job Id]=" & Me![Job Id]

Here is the on click code :

Private Sub cmdOverduePayment_Click()
On Error GoTo Err_cmdOverduePayment_Click

    Dim stDocName As String
    Dim stWhere As String
    
    stDocName = "rptLetter"
    stWhere = "[Job Id]=" & Me![Job Id]
    
    DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_cmdOverduePayment_Click:
    Exit Sub

Err_cmdOverduePayment_Click:
    MsgBox Err.Description
    Resume Exit_cmdOverduePayment_Click
    
End Sub

 

[Beetle]

strWhere = "[Job Id]=" & Me![Job Id] & " And [Date Paid] Is Null"

 

[MBM]

Doesn't work. It shows the first record in the preview regardless of which record is displayed in the form. Do I need to enter any validation rules in the Date Paid field or does simply pressing enter and not entering a date make the value null?

 

[Beetle]

A Date/Time field will either have a date value or it will be Null. I re-read your first post and think maybe we should clarify a bit.

In your application can you have more than one record with the same Job Id but with different values (or Null) in the Date Paid field?

Also, I may have mis-understoood your post to begin with.

Do you;

1) Want to open the report only if the Date Paid of the current record is Null and have the report display all records that match the current Job Id.

OR

2) Want to open the report in all cases and have it display records that match the Job Id and have a Null Date Paid

OR

3) Something else?

 

[MBM]

A Date/Time field will either have a date value or it will be Null. I re-read your first post and think maybe we should clarify a bit.

In your application can you have more than one record with the same Job Id but with different values (or Null) in the Date Paid field?

No. There is one Job Id per field.

Also, I may have mis-understoood your post to begin with.

Do you;

1) Want to open the report only if the Date Paid of the current record is Null and have the report display all records that match the current Job Id.

OR

2) Want to open the report in all cases and have it display records that match the Job Id and have a Null Date Paid

2. Yes.

 

[Beetle]

No. There is one Job Id per field.

May be I should reword the question.

Is each Job Id unique within the table, or can the same Job Id appear in more than one record in the table?

2. Yes.

Hmmmm.....that's what should have happened with the where condition posted earlier. So you're saying that in your form the current record had a Job Id of, let's say 5, but the report displayed a record (or records) with a different Job Id?

 

[MBM]

May be I should reword the question.

Is each Job Id unique within the table, or can the same Job Id appear in more than one record in the table?

Job Ids are automatically generated numbers so unique to a record.

Hmmmm.....that's what should have happened with the where condition posted earlier. So you're saying that in your form the current record had a Job Id of, let's say 5, but the report displayed a record (or records) with a different Job Id?

If I add the And [Date Paid] Is Null condition the report displays the same record in the report, for some reason the second one, irrespective of the record being displayed in the form. Without the And [Date Paid] the record on the form is displayed in the report.

 

[PeterF]

Does the form has a subform or does the report has a subreport.
Can you post the SQL of the recordsource of the report?

 

[MBM]

Yes the form has a subform.

The Record Source is :

SELECT Customer.*, [Job List].[Job Id] FROM Customer INNER JOIN [Job List] ON Customer.[Customer Id]=[Job List].[Customer Id];