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.

 

[Douglas J. Steele]

Or, a little shorter,

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

 

[Douglas J. Steele]

Or, a little shorter,

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

 

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

 

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

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])

 

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

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]))

 

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

 

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

 

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

 

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