If statement in a report?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda RQ - 29 Sep 2006 00:04 GMT

Using Access 2003

I have a report based on this query below. It shows the names and years
worked for employees for the month I type in. I would like to put a control
on the report that says "No Anniversarys this month". 2 problems. I don't
know if I would add an expression in the query then add a control bound to
that field or if there is a way to put the expression in a control on the
report to do this. I think it will be an If statement but not sure about
that either.

Here it is in LindaLanguage.
If Years Worked is Null then enter this "No Anniversaries This Month".

I can imagine a few problems with this...there would be 2 null fields, the
name and the years worked. Do I need to put both fields in the expression?

SELECT qryDepartmentEmployees.Name, qryDepartmentEmployees.dttHireDate,
DateSerial(Year(Date()),Month([dttHireDate]),Day([dtthireDate])) AS
HappyAnniversary, Format([dttHireDate],"mmmm") AS MonthName,
DateDiff("yyyy",[dttHireDate],[HappyAnniversary]) AS [Years Worked],
qryDepartmentEmployees.FandLName
FROM qryDepartmentEmployees
WHERE (((Format([dttHireDate],"mmmm"))=[Enter Month Name]));

Reply to this Message

Marshall Barton - 29 Sep 2006 05:16 GMT

>Using Access 2003
>
[quoted text clipped - 19 lines]
>FROM qryDepartmentEmployees
>WHERE (((Format([dttHireDate],"mmmm"))=[Enter Month Name]));

If the query returns no records, there won't be anything to
check for Null. However, a text box expression orVBA Code
in an event procedure can check the report's HasData
property.

I suggest that you use VBA code in the detail sectionTo make
your text boxes invisible and make a lable control with your
message visible.

If Me.HasData Then
    Me.txtName.Visible = False
    Me.txtHireDate.Visible = False
        . . .
    Me.lblNone.Visible = True
End If

Signature

Marsh
MVP [MS Access]

Reply to this Message

Linda RQ - 29 Sep 2006 13:48 GMT

>>Here it is in LindaLanguage.
>>If Years Worked is Null then enter this "No Anniversaries This Month".
[quoted text clipped - 26 lines]
> Me.lblNone.Visible = True
> End If

I'll have time this weekend I hope to make this work. I looked very quickly
and if I go into the properties of my text box for Name, I don't have an
options in the Event tab. If I R click in the detail section I have 3 event
choices. I am thinking that it would be in the "OnFormat"?

I was looking up Null on the microsoft site and after reading this, I
thought Null was what I was needing.
a.. Use the IIf and IsNull functions to test whether a value is Null, and
then return an appropriate value.

So if I had one field in a query that has records but other fields are
blank, those would be the null records? But since my query is asking for
records that don't exist the fields aren't null, they just don't exist?

Thanks

Linda

Reply to this Message

Marshall Barton - 29 Sep 2006 19:05 GMT

>>>Here it is in LindaLanguage.
>>>If Years Worked is Null then enter this "No Anniversaries This Month".
[quoted text clipped - 40 lines]
>blank, those would be the null records? But since my query is asking for
>records that don't exist the fields aren't null, they just don't exist?

You are correct on all counts.

I should have been more explicit about putting the code in
the detail section's Format event procedure.

From the tone of your question, I think you may be
unfamiliar with using event procedures so I want to make
clear that the OnFormat property that you see in the detail
section's property sheet needs to contain:
[Event Procedure]
which is available as a choice in the property's drop down
list. After that is done, click on the builder button (with
three dots), which will automatically take you to the Visual
Basic Editor and position the cursor in the event procedure
ready for you to enter/edit the code.

As an aside to your question, you will eventually get into
trouble using reserved words (e.g. Name, Date, Count, etc)
for the names of things you create. Access tries to
minimize the trouble spots, but I think that just makes
things less clear. Because it's difficult to determine
where you can and can not use a reserved word for your own
items, it is a best practice to never use them.
Furthermore, because there is a very long list of reserved
words (and no complete list exists), you should at least
avoid using most common words in English and the language of
your version of Access.

Signature

Marsh
MVP [MS Access]

Reply to this Message