NoData message to include parameter value of a query.

[randria]

Hello,

I have a query based report and when there is no data , I would like to show
in the message the parameter of the query which is a Year date
e.g: if the user entered 2011 and there is no data for that year, he gets a
message like, " couldn't find any record for 2011".
I know how to do this if a form was used to open the report, but in this
case I am not using a form so I am wondering if it is possible to show the
parameter value of the query instead.

Many thanks

 

[Marshall Barton]

I have a query based report and when there is no data , I would like to show
in the message the parameter of the query which is a Year date
e.g: if the user entered 2011 and there is no data for that year, he gets a
message like, " couldn't find any record for 2011".
I know how to do this if a form was used to open the report, but in this
case I am not using a form so I am wondering if it is possible to show the
parameter value of the query instead.

Using a form is a better way to do that. OTOH, if you
really feel you want to use the quick and dirty prompting
parameter, then you can use the **exact** same prompt text
as if it were a field in the query.

E.g. If the query criteria is something like [Enter Year],
then a report text box can use the control source
expression:
=" couldn't find any record for " & [Enter Year]

 

[John Spencer]

I don't believe there is a way to do this if NO records are returned.
You would have to use a form and its controls or a function to set
global variable in a VBA module

Perhaps you could use something like the following. The following is all
speculation on my part and I have not tested this solution.

Dim gVarInput as Variant

Public fSetVariant(v as Variant)
gVarInput = v
fSetVariant = Null
End Function

Then in your query you would have to include a call to the function and
pass in the parameter.

The query would look something like:

SELECT fSetVariant([Find What]) as x,
SomeTable.*
FROM SomeTable
WHERE SomeField = [Find What]

The report's No Data event then could use the global variable -
MsgBox "No record matched " & gVarInput



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I have a query based report and when there is no data , I would like to show
in the message the parameter of the query which is a Year date
e.g: if the user entered 2011 and there is no data for that year, he gets a
message like, " couldn't find any record for 2011".
I know how to do this if a form was used to open the report, but in this
case I am not using a form so I am wondering if it is possible to show the
parameter value of the query instead.

Using a form is a better way to do that. OTOH, if you
really feel you want to use the quick and dirty prompting
parameter, then you can use the **exact** same prompt text
as if it were a field in the query.

E.g. If the query criteria is something like [Enter Year],
then a report text box can use the control source
expression:
=" couldn't find any record for " & [Enter Year]

Whoops. I forgot the part about checking for no data:

=IIf(Report.NoData, " couldn't find any record for " &
[Enter Year], Null)

 

[randria]

Gentlemen, thank you very much for your suggestions. What I learned from both
of you is that it is better to use forms. I did try both options.
Many thanks.

I don't believe there is a way to do this if NO records are returned.
You would have to use a form and its controls or a function to set
global variable in a VBA module

Perhaps you could use something like the following. The following is all
speculation on my part and I have not tested this solution.

Dim gVarInput as Variant

Public fSetVariant(v as Variant)
gVarInput = v
fSetVariant = Null
End Function

Then in your query you would have to include a call to the function and
pass in the parameter.

The query would look something like:

SELECT fSetVariant([Find What]) as x,
SomeTable.*
FROM SomeTable
WHERE SomeField = [Find What]

The report's No Data event then could use the global variable -
MsgBox "No record matched " & gVarInput



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,

I have a query based report and when there is no data , I would like to show
in the message the parameter of the query which is a Year date
e.g: if the user entered 2011 and there is no data for that year, he gets a
message like, " couldn't find any record for 2011".
I know how to do this if a form was used to open the report, but in this
case I am not using a form so I am wondering if it is possible to show the
parameter value of the query instead.

Many thanks