|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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]));
|
|
Linda RQ wrote:
[Quoted Text] >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]));
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
--
Marsh
MVP [MS Access]
|
|
[Quoted Text] >>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]));
>
>
> 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
>
> --
> Marsh
> MVP [MS Access]
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
|
|
Linda RQ wrote:
[Quoted Text] >
>>>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]));
>>
>>
>> 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
>
>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?
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.
--
Marsh
MVP [MS Access]
|
|
|