By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,991 Members | 1,887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,991 IT Pros & Developers. It's quick & easy.

Calling an IIf statement

P: n/a
OK, I am thoroughly confused. All I am trying to do is fill in a
Status field on my form with the condtions below, with all my theory
being based on what I have read in various newsgropups. The IIf
statement works in the forms Status field when I use the IIf wording
as the control source. Obviously this is not saving into the field
with the text I want but I found it useful as a check. On the form in
the Status field, I've popped in =Status() into the OnClick field with
the theory that once the user clicks into this field, the data is
updated by referencing the Status module. What simple step(s) am I
missing??

Do I need to call the function from the OnOpen property of the whole
form and then somehow pop and result into the Status field?

Function Status() As TextBox

Dim Status As Sring

Status = IIf(Date <= [Calibration Required], "Instrument OK to use",
IIf(Date >= [Calibration Required], "Out of Date - Calibration
Required"))

End Function

Stinky (thoroughly confused) Pete ;-)

Mar 9 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"Stinky Pete" <Pe**********@symbionhealth.comwrote in message
news:11**********************@64g2000cwx.googlegro ups.com...
OK, I am thoroughly confused. All I am trying to do is fill in a
Status field on my form with the condtions below, with all my theory
being based on what I have read in various newsgropups. The IIf
statement works in the forms Status field when I use the IIf wording
as the control source. Obviously this is not saving into the field
with the text I want but I found it useful as a check. On the form in
the Status field, I've popped in =Status() into the OnClick field with
the theory that once the user clicks into this field, the data is
updated by referencing the Status module. What simple step(s) am I
missing??

Do I need to call the function from the OnOpen property of the whole
form and then somehow pop and result into the Status field?

Function Status() As TextBox

Dim Status As Sring

Status = IIf(Date <= [Calibration Required], "Instrument OK to use",
IIf(Date >= [Calibration Required], "Out of Date - Calibration
Required"))

End Function

Stinky (thoroughly confused) Pete ;-)

Does that function actually work?

I only say this because;

- Sring should be String

- you have nested one IIf inside another, yet I think they don't have the
required set of arguments. I haven't used IIf for a while but don't you
need to insert two outcomes for true and false? Also what happens if the
the Date equals [Calibration Required] - both outcomes would be ok according
to this code.
How about;

Status = IIf(Date <= [Calibration Required], "Instrument OK to use", "Out of
Date - Calibration Required")

Mar 9 '07 #2

P: n/a
On Mar 9, 12:11 pm, "Deano" <d...@mailinator.comwrote:
"Stinky Pete" <Peter.Mor...@symbionhealth.comwrote in message

news:11**********************@64g2000cwx.googlegro ups.com...


OK, I am thoroughly confused. All I am trying to do is fill in a
Status field on my form with the condtions below, with all my theory
being based on what I have read in various newsgropups. The IIf
statement works in the forms Status field when I use the IIf wording
as the control source. Obviously this is not saving into the field
with the text I want but I found it useful as a check. On the form in
the Status field, I've popped in =Status() into the OnClick field with
the theory that once the user clicks into this field, the data is
updated by referencing the Status module. What simple step(s) am I
missing??
Do I need to call the function from the OnOpen property of the whole
form and then somehow pop and result into the Status field?
Function Status() As TextBox
Dim Status As Sring
Status = IIf(Date <= [Calibration Required], "Instrument OK to use",
IIf(Date >= [Calibration Required], "Out of Date - Calibration
Required"))
End Function
Stinky (thoroughly confused) Pete ;-)

Does that function actually work?

I only say this because;

- Sring should be String

- you have nested one IIf inside another, yet I think they don't have the
required set of arguments. I haven't used IIf for a while but don't you
need to insert two outcomes for true and false? Also what happens if the
the Date equals [Calibration Required] - both outcomes would be ok according
to this code.

How about;

Status = IIf(Date <= [Calibration Required], "Instrument OK to use", "Out of
Date - Calibration Required")- Hide quoted text -

- Show quoted text -
Deano, thanx for your response and pointing out my typo! I've updated
the string, but the field is still not being populated which is
frustrating. It'd be nice to find a simple explanation of how to
properly call a function and will still keep on hunting through the
archives.

Stinky Pete ;-)

Mar 9 '07 #3

P: n/a

"Stinky Pete" <Pe**********@symbionhealth.comwrote in message
news:11**********************@t69g2000cwt.googlegr oups.com...
On Mar 9, 12:11 pm, "Deano" <d...@mailinator.comwrote:
"Stinky Pete" <Peter.Mor...@symbionhealth.comwrote in message

news:11**********************@64g2000cwx.googlegro ups.com...


OK, I am thoroughly confused. All I am trying to do is fill in a
Status field on my form with the condtions below, with all my theory
being based on what I have read in various newsgropups. The IIf
statement works in the forms Status field when I use the IIf wording
as the control source. Obviously this is not saving into the field
with the text I want but I found it useful as a check. On the form in
the Status field, I've popped in =Status() into the OnClick field with
the theory that once the user clicks into this field, the data is
updated by referencing the Status module. What simple step(s) am I
missing??
Do I need to call the function from the OnOpen property of the whole
form and then somehow pop and result into the Status field?
Function Status() As TextBox
Dim Status As Sring
Status = IIf(Date <= [Calibration Required], "Instrument OK to use",
IIf(Date >= [Calibration Required], "Out of Date - Calibration
Required"))
End Function
Stinky (thoroughly confused) Pete ;-)
Does that function actually work?

I only say this because;

- Sring should be String

- you have nested one IIf inside another, yet I think they don't have
the
required set of arguments. I haven't used IIf for a while but don't you
need to insert two outcomes for true and false? Also what happens if
the
the Date equals [Calibration Required] - both outcomes would be ok
according
to this code.

How about;

Status = IIf(Date <= [Calibration Required], "Instrument OK to use",
"Out of
Date - Calibration Required")- Hide quoted text -

- Show quoted text -

Deano, thanx for your response and pointing out my typo! I've updated
the string, but the field is still not being populated which is
frustrating. It'd be nice to find a simple explanation of how to
properly call a function and will still keep on hunting through the
archives.

Stinky Pete ;-)

Oh, I thought you'd copied and pasted it!
Another thing that occurs to me is that the line;

Function Status() As TextBox

should be defined as a string because that's what the function is returning.

The way you're assigning the Function seems ok, though you could always try
writing some code in the click event of the control;

me.txtStatus = Status

Mar 9 '07 #4

P: n/a
On Mar 9, 7:05 pm, "Deano" <d...@mailinator.comwrote:
"Stinky Pete" <Peter.Mor...@symbionhealth.comwrote in message

news:11**********************@t69g2000cwt.googlegr oups.com...


On Mar 9, 12:11 pm, "Deano" <d...@mailinator.comwrote:
"Stinky Pete" <Peter.Mor...@symbionhealth.comwrote in message
>news:11**********************@64g2000cwx.googlegr oups.com...
OK, I am thoroughly confused. All I am trying to do is fill in a
Status field on my form with the condtions below, with all my theory
being based on what I have read in various newsgropups. The IIf
statement works in the forms Status field when I use the IIf wording
as the control source. Obviously this is not saving into the field
with the text I want but I found it useful as a check. On the form in
the Status field, I've popped in =Status() into the OnClick field with
the theory that once the user clicks into this field, the data is
updated by referencing the Status module. What simple step(s) am I
missing??
Do I need to call the function from the OnOpen property of the whole
form and then somehow pop and result into the Status field?
Function Status() As TextBox
Dim Status As Sring
Status = IIf(Date <= [Calibration Required], "Instrument OK to use",
IIf(Date >= [Calibration Required], "Out of Date - Calibration
Required"))
End Function
Stinky (thoroughly confused) Pete ;-)
Does that function actually work?
I only say this because;
- Sring should be String
- you have nested one IIf inside another, yet I think they don't have
the
required set of arguments. I haven't used IIf for a while but don't you
need to insert two outcomes for true and false? Also what happens if
the
the Date equals [Calibration Required] - both outcomes would be ok
according
to this code.
How about;
Status = IIf(Date <= [Calibration Required], "Instrument OK to use",
"Out of
Date - Calibration Required")- Hide quoted text -
- Show quoted text -
Deano, thanx for your response and pointing out my typo! I've updated
the string, but the field is still not being populated which is
frustrating. It'd be nice to find a simple explanation of how to
properly call a function and will still keep on hunting through the
archives.
Stinky Pete ;-)

Oh, I thought you'd copied and pasted it!

Another thing that occurs to me is that the line;

Function Status() As TextBox

should be defined as a string because that's what the function is returning.

The way you're assigning the Function seems ok, though you could always try
writing some code in the click event of the control;

me.txtStatus = Status- Hide quoted text -

- Show quoted text -
Deano,

I did copy and paste it, but did not actually proof read what I copied
into the function. Just shows how careful you need to be when
copying. I tried the txtbox bit as that was the most logical thing I
could first think of as I wanted to populate a text box. After
reading your first post, I changed it to string. Despite still not
having all this clear in my mind, at least I am still learning.

Stinky Pete ;-)

Mar 9 '07 #5

P: n/a
"Stinky Pete" <Pe**********@symbionhealth.comwrote in
news:11**********************@n33g2000cwc.googlegr oups.com:
On Mar 9, 7:05 pm, "Deano" <d...@mailinator.comwrote:
>"Stinky Pete" <Peter.Mor...@symbionhealth.comwrote in message

news:11**********************@t69g2000cwt.googleg roups.com...


On Mar 9, 12:11 pm, "Deano" <d...@mailinator.comwrote:
"Stinky Pete" <Peter.Mor...@symbionhealth.comwrote in message
>news:11**********************@64g2000cwx.googlegr oups.com...
OK, I am thoroughly confused. All I am trying to do is fill in
a Status field on my form with the condtions below, with all my
theory being based on what I have read in various newsgropups.
The IIf statement works in the forms Status field when I use
the IIf wording as the control source. Obviously this is not
saving into the field with the text I want but I found it
useful as a check. On the form in the Status field, I've popped
in =Status() into the OnClick field with the theory that once
the user clicks into this field, the data is updated by
referencing the Status module. What simple step(s) am I
missing??
Do I need to call the function from the OnOpen property of the
whole form and then somehow pop and result into the Status
field?
Function Status() As TextBox
Dim Status As Sring
Status = IIf(Date <= [Calibration Required], "Instrument OK to
use", IIf(Date >= [Calibration Required], "Out of Date -
Calibration Required"))
End Function
Stinky (thoroughly confused) Pete ;-)
Does that function actually work?
I only say this because;
- Sring should be String
- you have nested one IIf inside another, yet I think they don't
have
the
required set of arguments. I haven't used IIf for a while but
don't you need to insert two outcomes for true and false? Also
what happens if
the
the Date equals [Calibration Required] - both outcomes would be
ok
according
to this code.
How about;
Status = IIf(Date <= [Calibration Required], "Instrument OK to
use",
"Out of
Date - Calibration Required")- Hide quoted text -
- Show quoted text -
Deano, thanx for your response and pointing out my typo! I've
updated the string, but the field is still not being populated
which is frustrating. It'd be nice to find a simple explanation of
how to properly call a function and will still keep on hunting
through the archives.
Stinky Pete ;-)

Oh, I thought you'd copied and pasted it!

Another thing that occurs to me is that the line;

Function Status() As TextBox

should be defined as a string because that's what the function is
returning.

The way you're assigning the Function seems ok, though you could
always try writing some code in the click event of the control;

me.txtStatus = Status- Hide quoted text -

- Show quoted text -

Deano,

I did copy and paste it, but did not actually proof read what I copied
into the function. Just shows how careful you need to be when
copying. I tried the txtbox bit as that was the most logical thing I
could first think of as I wanted to populate a text box. After
reading your first post, I changed it to string. Despite still not
having all this clear in my mind, at least I am still learning.

Stinky Pete ;-)
It's quite difficult to get the cow's digestive system to work well after
the cow has been slaughtered. I suggest you just tell us what you would
like to do with the cow and forget the part betwen Function Status as
TextBox and End Function. Just drop it into that big metal container that
runs on a rail between the two rows of cows.

On another note, at least in some versions of Access IIf's in VBA are
problemtic as they evaluate both results, whether or not the condition is
true. This is inefficient and can result in errors. IIf's in SQL do not
evaluate both results if the condition is true. (Using a function that
works 2 ways is 2 much for my little mind; I choose not 2 use IIfs.)

In VBA it can efficient to use a function and If statements than to use
IIf. The function call can then be used as an in-line expression.

It might be asked why MS maintains barbarisms such as Kill and IIf and
SendKeys. My guess is that it is to maintain backwards compatibility,
that these methods give the unwashed a misplaced self-confidence which is
good for sales, and because MS has every intention of letting VBA die.
But who knows?
Has anyone seen "Kill VBA" on Bill Gates's whiteboard?

--
Mar 9 '07 #6

P: n/a

"Stinky Pete" <Pe**********@symbionhealth.comwrote in message
news:11**********************@n33g2000cwc.googlegr oups.com...
>- Show quoted text -

Deano,

I did copy and paste it, but did not actually proof read what I copied
into the function. Just shows how careful you need to be when
copying. I tried the txtbox bit as that was the most logical thing I
could first think of as I wanted to populate a text box. After
reading your first post, I changed it to string. Despite still not
having all this clear in my mind, at least I am still learning.

Stinky Pete ;-)

Well I hope you get it sorted. I've been struggling with my U.S vs UK dates
problem and think I have maybe got it licked....
Mar 9 '07 #7

P: n/a
"lyle fairfield" <ly******@yahoo.cawrote
Has anyone seen "Kill VBA" on Bill Gates's whiteboard?
If turnabout is, indeed, fair play, wouldn't that be appropriate? I saw
"Kill Bill" on a movie poster.

Larry
Mar 9 '07 #8

P: n/a
"Stinky Pete" <Pe**********@symbionhealth.comwrote in
news:11**********************@64g2000cwx.googlegro ups.com:
OK, I am thoroughly confused. All I am trying to do is fill
in a Status field on my form with the condtions below, with
all my theory being based on what I have read in various
newsgropups. The IIf statement works in the forms Status
field when I use the IIf wording as the control source.
Obviously this is not saving into the field with the text I
want but I found it useful as a check. On the form in the
Status field, I've popped in =Status() into the OnClick field
with the theory that once the user clicks into this field, the
data is updated by referencing the Status module. What simple
step(s) am I missing??

Do I need to call the function from the OnOpen property of the
whole form and then somehow pop and result into the Status
field?
You are close to what you want to do. Call the function status
(somedate) from the OnCURRENT event of the form, and from the
[Calibration Required] field textbox.AfterUpdate event. That way
when you select a record, or change the [calibration required]
date on the form, you'll get the correct result.

You say you want to bind this [Status] to a field in the table.
Don't do it, just call the Status() function in every query,
form or report where you want to see it. There are several
reasons why, one being that if you print a report tomorrow, but
have not updated the field, your status will be one day out of
sync with the requirement. Better to use the function to
validate the status dynamically.

You can put the status function in a code module instead of the
form, and by declaring it Public, use it anywhere in the
application. pass the date of next calibration as an argument to
the function so you are not tied to a single name.

Public Function status(calduedate as date) as string
If Date() < calduedate then
status = "OK to use"
else
status = " Out of CAL!!!!!!! DO NOT USE"
end if
end function

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Mar 9 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.