Format numeric result as text???

[FerryMary]

I'd like the following statement to display "Due" is value is "-1".
-1 is natural result if statment is true. Currently I get "30ue" in the
results field.

Any thoughts?

ServiceAlert: Format([Current Engine Hours]>=[Service]*0.85 And [Current
Engine Hours]<[Service],"d""ue""")

Thanks so much
Mary

 

[Douglas J. Steele]

The format function uses d to represent days. Dates in Access are stored as
8 byte floating point numbers (where the integer part represents the date as
the number of days relative to 30 Dec, 1899, and the decimal part represents
the time as a fraction of a day)

Access uses -1 for True and 0 for False. Because of the d in the Function
statement, you should get 29ue for True (-1 is 29 Dec, 1899) and 30ue for
False.

Format is actually inappropriate for what you're trying to do. Instead, use
the IIf function:

ServiceAlert: IIf([Current Engine Hours]>=[Service]*0.85 And [Current Engine
Hours]<[Service],"due", Null)

 

['69 Camaro]

Hi, Mary.

Under your current logic, no special value will be displayed if the engine
is overdue for maintenance. If you would like the value "Due" to be
displayed whether the engine is due or overdue, try the following syntax
(watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), "Due", NULL)

If you would also like the value "Overdue" to be displayed when the engine
is overdue for maintenance, try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

If you would like the value "Due" to be displayed when maintenance is due,
but no value displayed when the engine is overdue for maintenance (as is in
your current logic), then try the following syntax (watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85) AND ([Current
Engine Hours] < Service), "Due", NULL)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

 

[FerryMary]

Thanks so much to both replies:

I actually had separate columns for service intervals (1-due,1-past due)
and rebuild intervals(1-due,1-past due). {4 recordset columns total} At the
very least I can cull this down to 2 recordset columns. Thanks again.

Mary

Hi, Mary.

Under your current logic, no special value will be displayed if the engine
is overdue for maintenance. If you would like the value "Due" to be
displayed whether the engine is due or overdue, try the following syntax
(watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), "Due", NULL)

If you would also like the value "Overdue" to be displayed when the engine
is overdue for maintenance, try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

If you would like the value "Due" to be displayed when maintenance is due,
but no value displayed when the engine is overdue for maintenance (as is in
your current logic), then try the following syntax (watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85) AND ([Current
Engine Hours] < Service), "Due", NULL)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'd like the following statement to display "Due" is value is "-1".
-1 is natural result if statment is true. Currently I get "30ue" in the
results field.

Any thoughts?

ServiceAlert: Format([Current Engine Hours]>=[Service]*0.85 And [Current
Engine Hours]<[Service],"d""ue""")

Thanks so much
Mary

 

[FerryMary]

I tried to incorporate 'due' and 'overdue' but have missed something,,
something very simple I'm sure. If anyone can crack me in the head and set
me straight it would be much appreciated. I end up with 'overdue' everytime.

ServiceAlert: IIf(([Current Engine Hours] Between [Service]*0.85 And
[Service]),IIf(([Current Engine Hours]>[Service]),"Due","Overdue"),Null)

Thanks Again,
Mary-mumble,mumble,mumble

Hi, Mary.

Under your current logic, no special value will be displayed if the engine
is overdue for maintenance. If you would like the value "Due" to be
displayed whether the engine is due or overdue, try the following syntax
(watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), "Due", NULL)

If you would also like the value "Overdue" to be displayed when the engine
is overdue for maintenance, try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

If you would like the value "Due" to be displayed when maintenance is due,
but no value displayed when the engine is overdue for maintenance (as is in
your current logic), then try the following syntax (watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85) AND ([Current
Engine Hours] < Service), "Due", NULL)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'd like the following statement to display "Due" is value is "-1".
-1 is natural result if statment is true. Currently I get "30ue" in the
results field.

Any thoughts?

ServiceAlert: Format([Current Engine Hours]>=[Service]*0.85 And [Current
Engine Hours]<[Service],"d""ue""")

Thanks so much
Mary

 

[Douglas J. Steele]

I don't believe you can use Between in an IIf statement.

Try:

ServiceAlert: IIf(([Current Engine Hours] >= [Service]*0.85 And
[Current Engine Hours] <= [Service]),IIf(([Current Engine
Hours]>[Service]),"Due","Overdue"),Null)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried to incorporate 'due' and 'overdue' but have missed something,,
something very simple I'm sure. If anyone can crack me in the head and
set
me straight it would be much appreciated. I end up with 'overdue'
everytime.

ServiceAlert: IIf(([Current Engine Hours] Between [Service]*0.85 And
[Service]),IIf(([Current Engine Hours]>[Service]),"Due","Overdue"),Null)

Thanks Again,
Mary-mumble,mumble,mumble

Hi, Mary.

Under your current logic, no special value will be displayed if the
engine
is overdue for maintenance. If you would like the value "Due" to be
displayed whether the engine is due or overdue, try the following syntax
(watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), "Due",
NULL)

If you would also like the value "Overdue" to be displayed when the
engine
is overdue for maintenance, try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85),
IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

If you would like the value "Due" to be displayed when maintenance is
due,
but no value displayed when the engine is overdue for maintenance (as is
in
your current logic), then try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85) AND
([Current
Engine Hours] < Service), "Due", NULL)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first
and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'd like the following statement to display "Due" is value is "-1".
-1 is natural result if statment is true. Currently I get "30ue" in
the
results field.

Any thoughts?

ServiceAlert: Format([Current Engine Hours]>=[Service]*0.85 And
[Current
Engine Hours]<[Service],"d""ue""")

Thanks so much
Mary

 

['69 Camaro]

Hi, Mary.

To incorporate both "Due" and "Overdue" in the same column, try the second
example syntax I gave you earlier (watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

And thanks for marking our replies as answers. It's much appreciated!

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

I tried to incorporate 'due' and 'overdue' but have missed something,,
something very simple I'm sure. If anyone can crack me in the head and
set
me straight it would be much appreciated. I end up with 'overdue'
everytime.

ServiceAlert: IIf(([Current Engine Hours] Between [Service]*0.85 And
[Service]),IIf(([Current Engine Hours]>[Service]),"Due","Overdue"),Null)

Thanks Again,
Mary-mumble,mumble,mumble

Hi, Mary.

Under your current logic, no special value will be displayed if the
engine
is overdue for maintenance. If you would like the value "Due" to be
displayed whether the engine is due or overdue, try the following syntax
(watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), "Due",
NULL)

If you would also like the value "Overdue" to be displayed when the
engine
is overdue for maintenance, try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85),
IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

If you would like the value "Due" to be displayed when maintenance is
due,
but no value displayed when the engine is overdue for maintenance (as is
in
your current logic), then try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85) AND
([Current
Engine Hours] < Service), "Due", NULL)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first
and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'd like the following statement to display "Due" is value is "-1".
-1 is natural result if statment is true. Currently I get "30ue" in
the
results field.

Any thoughts?

ServiceAlert: Format([Current Engine Hours]>=[Service]*0.85 And
[Current
Engine Hours]<[Service],"d""ue""")

Thanks so much
Mary

 

[FerryMary]

It worked perfectly thanks so much guys. But if someone could explain why
that works. (I know I should just be happy and move on and the whole gift
horse in mouth thing,,,,but)

If my "Due" status is 85%-100% of "Service" (which is a 'service at'
recordset) and "Overdue" is"Service" @ 101% + how does the <[Service]
produce my Overdue result desired.

Is it the sequence of the IIFs? and do they only apply to first result.

Just curious
Mary--Thanks again

Hi, Mary.

To incorporate both "Due" and "Overdue" in the same column, try the second
example syntax I gave you earlier (watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

And thanks for marking our replies as answers. It's much appreciated!

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

I tried to incorporate 'due' and 'overdue' but have missed something,,
something very simple I'm sure. If anyone can crack me in the head and
set
me straight it would be much appreciated. I end up with 'overdue'
everytime.

ServiceAlert: IIf(([Current Engine Hours] Between [Service]*0.85 And
[Service]),IIf(([Current Engine Hours]>[Service]),"Due","Overdue"),Null)

Thanks Again,
Mary-mumble,mumble,mumble

Hi, Mary.

Under your current logic, no special value will be displayed if the
engine
is overdue for maintenance. If you would like the value "Due" to be
displayed whether the engine is due or overdue, try the following syntax
(watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), "Due",
NULL)

If you would also like the value "Overdue" to be displayed when the
engine
is overdue for maintenance, try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85),
IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

If you would like the value "Due" to be displayed when maintenance is
due,
but no value displayed when the engine is overdue for maintenance (as is
in
your current logic), then try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85) AND
([Current
Engine Hours] < Service), "Due", NULL)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first
and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'd like the following statement to display "Due" is value is "-1".
-1 is natural result if statment is true. Currently I get "30ue" in
the
results field.

Any thoughts?

ServiceAlert: Format([Current Engine Hours]>=[Service]*0.85 And
[Current
Engine Hours]<[Service],"d""ue""")

Thanks so much
Mary

 

['69 Camaro]

Hi, Mary.

It worked perfectly thanks so much guys.

You're welcome.

But if someone could explain why
that works. (I know I should just be happy and move on and the whole
gift
horse in mouth thing,,,,but)

There's an old saying, "Those who know how will always have a job. Those
who know why will always have a job -- and be put in charge." Besides, if
you know why the code works, you'll be able to apply the same principles
later when a similar coding problem comes up. And it will.

Is it the sequence of the IIFs?

Yes.

Here's how the logic works:

Are the current engine hours greater than or equal to 85% of the service
hours?

1. If yes, then are the current engine hours less than the service hours?
1a. If yes, then the ServiceAlert column should display "Due."
1b. If no, then the ServiceAlert column should display "Overdue."
2. If no, then the ServiceAlert column should not display anything.

and do they only apply to first result.

If I understand your question, then the ServiceAlert column will display
nothing if the current engine hours are below 85% of the service hours, or
display "Due" if the current engine hours are between 85% and nearly 100% of
the service hours, or display "Overdue" if the current engine hours are 100%
or more of the service hours.

This logic for the ServiceAlert column only applies to the service
intervals. You'll need to apply the same logic to the engine rebuild
intervals.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

It worked perfectly thanks so much guys. But if someone could explain why
that works. (I know I should just be happy and move on and the whole
gift
horse in mouth thing,,,,but)

If my "Due" status is 85%-100% of "Service" (which is a 'service at'
recordset) and "Overdue" is"Service" @ 101% + how does the <[Service]
produce my Overdue result desired.

Is it the sequence of the IIFs? and do they only apply to first result.

Just curious
Mary--Thanks again

Hi, Mary.

To incorporate both "Due" and "Overdue" in the same column, try the
second
example syntax I gave you earlier (watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85),
IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

And thanks for marking our replies as answers. It's much appreciated!

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

I tried to incorporate 'due' and 'overdue' but have missed something,,
something very simple I'm sure. If anyone can crack me in the head and
set
me straight it would be much appreciated. I end up with 'overdue'
everytime.

ServiceAlert: IIf(([Current Engine Hours] Between [Service]*0.85 And
[Service]),IIf(([Current Engine
Hours]>[Service]),"Due","Overdue"),Null)

Thanks Again,
Mary-mumble,mumble,mumble


:

Hi, Mary.

Under your current logic, no special value will be displayed if the
engine
is overdue for maintenance. If you would like the value "Due" to be
displayed whether the engine is due or overdue, try the following
syntax
(watch out for word wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85), "Due",
NULL)

If you would also like the value "Overdue" to be displayed when the
engine
is overdue for maintenance, try the following syntax (watch out for
word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85),
IIF(([Current
Engine Hours] < Service), "Due", "Overdue"), NULL)

If you would like the value "Due" to be displayed when maintenance is
due,
but no value displayed when the engine is overdue for maintenance (as
is
in
your current logic), then try the following syntax (watch out for word
wrap):

ServiceAlert: IIF (([Current Engine Hours] >= Service * 0.85) AND
([Current
Engine Hours] < Service), "Due", NULL)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers,"
so that all may benefit by filtering on "Answered questions" and
quickly
finding the right answers to similar questions. Remember that the
first
and
best answers are often given to those who have a history of rewarding
the
contributors who have taken the time to answer questions correctly.


:

I'd like the following statement to display "Due" is value is "-1".
-1 is natural result if statment is true. Currently I get "30ue" in
the
results field.

Any thoughts?

ServiceAlert: Format([Current Engine Hours]>=[Service]*0.85 And
[Current
Engine Hours]<[Service],"d""ue""")

Thanks so much
Mary