Can I use an alternative field if value is NUL

[Marshall Barton]

OK - Its trying to do it, but when the "Alt date" is required I get " = error".

Is it possible, perhaps, to convert the "Short Date" format to text for the
report? I need it to remain as date in the table, as I quite often sort by
date, but when in the report sorting is not required for that field.

You should not have to convert the date to text for the
expression to work. Maybe there is some other issue?
Please post a Copy/Paste of the expression as you currently
have it.

I have never seen Access generate " = error" because of
something wrong in a text box expression. Are you sure that
is exactly what the text box displayed?

Try an experiment. Add two text boxes, one bound to the
date field and the other bound to the alt date field. Then
check to see if both text boxes display the expected values
from the report's record source query.

 

[David M.]

HI AGAIN.

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

=Nz([Alt date],[When seen]) (Changed from =Nz([When seen],[Alt date]) )

Format is empty.

After further testing:

"When seen" or "Alt date" alone - both work OK.

"=Nz([When seen],[Alt date])" OK with "When seen" but gives "#Error" when
"Alt date" required
"=Nz([Alt date],[When seen])" OK with "Alt date" but gives "#Error" when
"When seen" required.

 

[David M.]

HI AGAIN.

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

=Nz([Alt date],[When seen]) (Changed from =Nz([When seen],[Alt date]) )

Format is empty.

After further testing:

"When seen" or "Alt date" alone - both work OK.

"=Nz([When seen],[Alt date])" OK with "When seen" but gives "#Error" when
"Alt date" required
"=Nz([Alt date],[When seen])" OK with "Alt date" but gives "#Error" when
"When seen" required.

 

[Marshall Barton]

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

"=Nz([When seen],[Alt date])"
OK with "When seen" but gives "#Error" when "Alt date" required

This is really weird!?

I set up a simple form in A2003 to duplicate your situation
and I ran into the same problem. I have never seen this
before so I was trying to track it down when all of a sudden
it started behaving correctly?!

I have no idea what was going on, but the only thing I think
I did was add a line of code to the form's Current event
txtTest = TypeName(DateField)
which displays "Access Field" in a test text box. My form
continues to work behave correctly even after I removed the
test text box and the code I had added.

 

[Marshall Barton]

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

"=Nz([When seen],[Alt date])"
OK with "When seen" but gives "#Error" when "Alt date" required

This is really weird!?

I set up a simple form in A2003 to duplicate your situation
and I ran into the same problem. I have never seen this
before so I was trying to track it down when all of a sudden
it started behaving correctly?!

I have no idea what was going on, but the only thing I think
I did was add a line of code to the form's Current event
txtTest = TypeName(DateField)
which displays "Access Field" in a test text box. My form
continues to work behave correctly even after I removed the
test text box and the code I had added.

 

[David M.]

Thanks for the update. Glad to know that even the experts can be baffled at
times!

If you can find the specific magic incantation to work the fix in my case ...

Thanks again
--
DPM

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

"=Nz([When seen],[Alt date])"
OK with "When seen" but gives "#Error" when "Alt date" required

This is really weird!?

I set up a simple form in A2003 to duplicate your situation
and I ran into the same problem. I have never seen this
before so I was trying to track it down when all of a sudden
it started behaving correctly?!

I have no idea what was going on, but the only thing I think
I did was add a line of code to the form's Current event
txtTest = TypeName(DateField)
which displays "Access Field" in a test text box. My form
continues to work behave correctly even after I removed the
test text box and the code I had added.

 

[David M.]

Thanks for the update. Glad to know that even the experts can be baffled at
times!

If you can find the specific magic incantation to work the fix in my case ...

Thanks again
--
DPM

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

"=Nz([When seen],[Alt date])"
OK with "When seen" but gives "#Error" when "Alt date" required

This is really weird!?

I set up a simple form in A2003 to duplicate your situation
and I ran into the same problem. I have never seen this
before so I was trying to track it down when all of a sudden
it started behaving correctly?!

I have no idea what was going on, but the only thing I think
I did was add a line of code to the form's Current event
txtTest = TypeName(DateField)
which displays "Access Field" in a test text box. My form
continues to work behave correctly even after I removed the
test text box and the code I had added.

 

[Mark Han[MSFT]]

Hi David,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when you run the Nz function in
the form, the exception "#Error" occurs. If I have misunderstood, please
let me know.

in order to address your concern, I would like to explain the following
1 I have reproduced the issue, when the data is null, the value returned by
the function NZ will be #Error;but when the data is not bull, the value
returned by th NZ is correct. So I think the issue might be a product
issue. if you would like to send the feedback to Microsoft, please refer to
http://www.microsoft.com/office/community/en-us/wizard.mspx?type=suggestion
Please understand that Microsoft strives to engineer our products to
satisfy the needs of as many people as possible. Unfortunately, some
problems inevitably arise. We do try to resolve these problems to the best
of our ability. Having said that, we do appreciate the feedback we receive
from our customers such as you. I will forward your comments to the
appropriate development and usability experts for the purpose of improving
user experience in the future.

2 For the issue, we can take the following to workaround the issue.
--in the "control source"
--type the expression "=IIf(IsNull([Data]),[tt],[Data])" (I have tested it.
it works)

Besides, for more information about "IIF", there is an article to share
with you:http://office.microsoft.com/en-us/access/HA012288671033.aspx

If there is anything unclear, please do not hesitate to let me know. Have a
nice day.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Han[MSFT]]

Hi David,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when you run the Nz function in
the form, the exception "#Error" occurs. If I have misunderstood, please
let me know.

in order to address your concern, I would like to explain the following
1 I have reproduced the issue, when the data is null, the value returned by
the function NZ will be #Error;but when the data is not bull, the value
returned by th NZ is correct. So I think the issue might be a product
issue. if you would like to send the feedback to Microsoft, please refer to
http://www.microsoft.com/office/community/en-us/wizard.mspx?type=suggestion
Please understand that Microsoft strives to engineer our products to
satisfy the needs of as many people as possible. Unfortunately, some
problems inevitably arise. We do try to resolve these problems to the best
of our ability. Having said that, we do appreciate the feedback we receive
from our customers such as you. I will forward your comments to the
appropriate development and usability experts for the purpose of improving
user experience in the future.

2 For the issue, we can take the following to workaround the issue.
--in the "control source"
--type the expression "=IIf(IsNull([Data]),[tt],[Data])" (I have tested it.
it works)

Besides, for more information about "IIF", there is an article to share
with you:http://office.microsoft.com/en-us/access/HA012288671033.aspx

If there is anything unclear, please do not hesitate to let me know. Have a
nice day.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[David M.]

Thank you, that HAS solved the problem this time, although it did not seem to
work when I first tried it after the initial response.
--
DPM

Hi David,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when you run the Nz function in
the form, the exception "#Error" occurs. If I have misunderstood, please
let me know.

in order to address your concern, I would like to explain the following
1 I have reproduced the issue, when the data is null, the value returned by
the function NZ will be #Error;but when the data is not bull, the value
returned by th NZ is correct. So I think the issue might be a product
issue. if you would like to send the feedback to Microsoft, please refer to
http://www.microsoft.com/office/community/en-us/wizard.mspx?type=suggestion
Please understand that Microsoft strives to engineer our products to
satisfy the needs of as many people as possible. Unfortunately, some
problems inevitably arise. We do try to resolve these problems to the best
of our ability. Having said that, we do appreciate the feedback we receive
from our customers such as you. I will forward your comments to the
appropriate development and usability experts for the purpose of improving
user experience in the future.

2 For the issue, we can take the following to workaround the issue.
--in the "control source"
--type the expression "=IIf(IsNull([Data]),[tt],[Data])" (I have tested it.
it works)

Besides, for more information about "IIF", there is an article to share
with you:http://office.microsoft.com/en-us/access/HA012288671033.aspx

If there is anything unclear, please do not hesitate to let me know. Have a
nice day.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[David M.]

Thank you, that HAS solved the problem this time, although it did not seem to
work when I first tried it after the initial response.
--
DPM

Hi David,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when you run the Nz function in
the form, the exception "#Error" occurs. If I have misunderstood, please
let me know.

in order to address your concern, I would like to explain the following
1 I have reproduced the issue, when the data is null, the value returned by
the function NZ will be #Error;but when the data is not bull, the value
returned by th NZ is correct. So I think the issue might be a product
issue. if you would like to send the feedback to Microsoft, please refer to
http://www.microsoft.com/office/community/en-us/wizard.mspx?type=suggestion
Please understand that Microsoft strives to engineer our products to
satisfy the needs of as many people as possible. Unfortunately, some
problems inevitably arise. We do try to resolve these problems to the best
of our ability. Having said that, we do appreciate the feedback we receive
from our customers such as you. I will forward your comments to the
appropriate development and usability experts for the purpose of improving
user experience in the future.

2 For the issue, we can take the following to workaround the issue.
--in the "control source"
--type the expression "=IIf(IsNull([Data]),[tt],[Data])" (I have tested it.
it works)

Besides, for more information about "IIF", there is an article to share
with you:http://office.microsoft.com/en-us/access/HA012288671033.aspx

If there is anything unclear, please do not hesitate to let me know. Have a
nice day.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[David M.]

Success!

I don't know if you have seen the message from Mark Han, but going back to
the 'long' IIf version seems to have worked this time.

I think it's now a case of leave well enough alone!

Thanks again for your efforts.
--
DPM

Sorry if this poosts twice

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

"=Nz([When seen],[Alt date])"
OK with "When seen" but gives "#Error" when "Alt date" required

This is really weird!?

I set up a simple form in A2003 to duplicate your situation
and I ran into the same problem. I have never seen this
before so I was trying to track it down when all of a sudden
it started behaving correctly?!

I have no idea what was going on, but the only thing I think
I did was add a line of code to the form's Current event
txtTest = TypeName(DateField)
which displays "Access Field" in a test text box. My form
continues to work behave correctly even after I removed the
test text box and the code I had added.

 

[David M.]

Success!

I don't know if you have seen the message from Mark Han, but going back to
the 'long' IIf version seems to have worked this time.

I think it's now a case of leave well enough alone!

Thanks again for your efforts.
--
DPM

Sorry if this poosts twice

Sorry, my eyesight is not the greatest. The error is, of course, "#Error".

Copied from "Control Source"

"=Nz([When seen],[Alt date])"
OK with "When seen" but gives "#Error" when "Alt date" required

This is really weird!?

I set up a simple form in A2003 to duplicate your situation
and I ran into the same problem. I have never seen this
before so I was trying to track it down when all of a sudden
it started behaving correctly?!

I have no idea what was going on, but the only thing I think
I did was add a line of code to the form's Current event
txtTest = TypeName(DateField)
which displays "Access Field" in a test text box. My form
continues to work behave correctly even after I removed the
test text box and the code I had added.

 

[Marshall Barton]

in order to address your concern, I would like to explain the following
1 I have reproduced the issue, when the data is null, the value returned by
the function NZ will be #Error;but when the data is not bull, the value
returned by th NZ is correct. So I think the issue might be a product
issue. if you would like to send the feedback to Microsoft, please refer to
http://www.microsoft.com/office/community/en-us/wizard.mspx?type=suggestion

Mark,

Thanks for confirming this problem. I do think that since
you have reproduced the problem, it would be way easier for
everyone if you could feed your repro scenario to the "bug
list" than for us to throw it into the big MS "product
feedback" hopper.

 

[Marshall Barton]

in order to address your concern, I would like to explain the following
1 I have reproduced the issue, when the data is null, the value returned by
the function NZ will be #Error;but when the data is not bull, the value
returned by th NZ is correct. So I think the issue might be a product
issue. if you would like to send the feedback to Microsoft, please refer to
http://www.microsoft.com/office/community/en-us/wizard.mspx?type=suggestion

Mark,

Thanks for confirming this problem. I do think that since
you have reproduced the problem, it would be way easier for
everyone if you could feed your repro scenario to the "bug
list" than for us to throw it into the big MS "product
feedback" hopper.

 

[Mark Han[MSFT]]

Hi DPM,

Thank you for the reply and confirmation. I'm glad that my suggestion help
to resolve the issue.

if you have any other questions related to the issue, we welcome you post
them here.

Have a nice day.


Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Han[MSFT]]

Hi DPM,

Thank you for the reply and confirmation. I'm glad that my suggestion help
to resolve the issue.

if you have any other questions related to the issue, we welcome you post
them here.

Have a nice day.


Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================