null function

[ryan.fitzpatrick3]

I have a date parameter in my query

=DateAdd("d",-15,Date())

I looks back 15 days. I search by vendors and sometimes some vendors
do not have any records in the last 15 days. How can I get a
messagebox stating that there are no records?

Ryan

 

[Douglas J. Steele]

On a query? You can't.

If you're using the query for a report, you can use the report's NoData
event.

If you're using the query for a form, you can check whether any data was
returned in the form's Open event.

 

[ryan.fitzpatrick3]

Ok good, I am using a query to go into a report. how does the NoData
event work? What do I do?

 

[Douglas J. Steele]

Try code like:

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No Data Returned"
Cancel = True

End Sub

Be aware, though, that cancelling the report like that will cause an run
time error 2501 ("The OpenReport action was cancelled") to be raised in the
module that opened the report. Make sure you have appropriate error
handling:

Private Sub cmdOpenReport_Click()
On Error GoTo ErrHandler

DoCmd.OpenReport "MyReport", acPrintPreview

EndRoutine:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume EndRoutine
End Select

End Sub

 

[ryan.fitzpatrick3]

Awesome that worked.

Try code like:

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No Data Returned"
Cancel = True

End Sub

Be aware, though, that cancelling the report like that will cause an run
time error 2501 ("The OpenReport action was cancelled") to be raised in the
module that opened the report. Make sure you have appropriate error
handling:

Private Sub cmdOpenReport_Click()
On Error GoTo ErrHandler

DoCmd.OpenReport "MyReport", acPrintPreview

EndRoutine:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume EndRoutine
End Select

End Sub