Can I use an alternative field if value is NUL

[David M.]

In one of my Access 2003 databases I have a date field formatted to date, BUT
some dates I cannot recall exactly and so I have an 'Alternate date' text
field. How do I get a report to print the 'Alternate date' instead when Date
is NUL, i.e. in the same place in the report. Just to confirm, these two
fields should not both have data.
Thanks for any thoughts.

 

[bhicks11 via AccessMonster.com]

Use an iif() expression for the value. Iif(not isnull(this date),this date,
that date)

Bonnie
http://www.dataplus-svc.com

 

[Douglas J. Steele]

Or, a little shorter,

Nz([This Date], [That Date])

 

[bhicks11 via AccessMonster.com]

No kidding - I did not know that. I like it.

Bonnie
http://www.dataplus-svc.com

Or, a little shorter,

Nz([This Date], [That Date])

Use an iif() expression for the value. Iif(not isnull(this date),this
date,

[quoted text clipped - 11 lines]

 

[Marshall Barton]

In one of my Access 2003 databases I have a date field formatted to date, BUT
some dates I cannot recall exactly and so I have an 'Alternate date' text
field. How do I get a report to print the 'Alternate date' instead when Date
is NUL, i.e. in the same place in the report. Just to confirm, these two
fields should not both have data.

Try using an expression in the text box:
=Nz([date field], [alternate date field])

 

[David M.]

Thanks to all of you. I've tried both versions, BUT I now find that the "Alt
date" field is not in the report field list (It was added to the table in an
early revision). SO, next question, How do I update the field list? I've
tried the 'official' method, but cannot find a toolbar in the fieldlist
window.--
DPM

In one of my Access 2003 databases I have a date field formatted to date, BUT
some dates I cannot recall exactly and so I have an 'Alternate date' text
field. How do I get a report to print the 'Alternate date' instead when Date
is NUL, i.e. in the same place in the report. Just to confirm, these two
fields should not both have data.

Try using an expression in the text box:
=Nz([date field], [alternate date field])

 

[David M.]

UPDATE - I've got the field added OK - NEXT problem:
I now get a ' Circular reference' message.

Relevant details are:
'When seen' field is in 'short date' but 'Alt date' is text. Will this
prevent success?

'=Nz([When seen],[Alt date])' is what I have got so far

Should I give up? :-((
--
DPM

Thanks to all of you. I've tried both versions, BUT I now find that the "Alt
date" field is not in the report field list (It was added to the table in an
early revision). SO, next question, How do I update the field list? I've
tried the 'official' method, but cannot find a toolbar in the fieldlist
window.--
DPM

In one of my Access 2003 databases I have a date field formatted to date, BUT
some dates I cannot recall exactly and so I have an 'Alternate date' text
field. How do I get a report to print the 'Alternate date' instead when Date
is NUL, i.e. in the same place in the report. Just to confirm, these two
fields should not both have data.

Try using an expression in the text box:
=Nz([date field], [alternate date field])

 

[Marshall Barton]

Thanks to all of you. I've tried both versions, BUT I now find that the "Alt
date" field is not in the report field list (It was added to the table in an
early revision). SO, next question, How do I update the field list? I've
tried the 'official' method, but cannot find a toolbar in the fieldlist
window.--

If the report's record source is the table, then you should
not have to do anything.

If the report's record source is a query, then you have to
add the field to the query's field list. (If the query uses
table.* for it's field list, compacting the database might
be sufficient.)

 

[Marshall Barton]

UPDATE - I've got the field added OK - NEXT problem:
I now get a ' Circular reference' message.

Relevant details are:
'When seen' field is in 'short date' but 'Alt date' is text. Will this
prevent success?

'=Nz([When seen],[Alt date])' is what I have got so far

Dates should be stored in Date type fields, not text fields.

The circular reference is probably because the text box has
the same name, [When seen] as a field used in the
expression. Change the text box's name to something else
such as txtWhenSeen.

The expression might actually work the way it is, but, if
you can not chage the AltDate field to a Date field, it's
better to explicitly convert it:
=Nz([When seen],CDate([Alt date]))

 

[David M.]

Ah - sorry - I didn't make it clear.
"When seen" field is a known date, but when the date is unknown it goes into
"Alt date" as something like "1986" or "July 1974" or, in many cases
"Various" when I have seen the same one several times at the same location
(Steam locos, actually).
Hence the problem and the mixed format.
--
DPM

UPDATE - I've got the field added OK - NEXT problem:
I now get a ' Circular reference' message.

Relevant details are:
'When seen' field is in 'short date' but 'Alt date' is text. Will this
prevent success?

'=Nz([When seen],[Alt date])' is what I have got so far

Dates should be stored in Date type fields, not text fields.

The circular reference is probably because the text box has
the same name, [When seen] as a field used in the
expression. Change the text box's name to something else
such as txtWhenSeen.

The expression might actually work the way it is, but, if
you can not chage the AltDate field to a Date field, it's
better to explicitly convert it:
=Nz([When seen],CDate([Alt date]))

 

[Marshall Barton]

Ah - sorry - I didn't make it clear.
"When seen" field is a known date, but when the date is unknown it goes into
"Alt date" as something like "1986" or "July 1974" or, in many cases
"Various" when I have seen the same one several times at the same location
(Steam locos, actually).
Hence the problem and the mixed format.

Did changing the name of the text box get rid of the
circular reference? If it did, did my original suggestion
work? If not what problem are you having with it now?

 

[David M.]

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.

 

[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.

 

[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.

 

[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.]

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.