Can I use an alternative field if value is NUL

  • Thread starter Thread starter David M.
  • Start date Start date
D

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.
 
David said:
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.
Click to expand...


Try using an expression in the text box:
=Nz([date field], [alternate date field])
 
David said:
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.
Click to expand...


Try using an expression in the text box:
=Nz([date field], [alternate date field])
 
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


Marshall Barton said:
David said:
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.
Click to expand...


Try using an expression in the text box:
=Nz([date field], [alternate date field])
Click to expand...
 
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


Marshall Barton said:
David said:
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.
Click to expand...


Try using an expression in the text box:
=Nz([date field], [alternate date field])
Click to expand...
 
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


David M. said:
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


Marshall Barton said:
David said:
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.
Click to expand...


Try using an expression in the text box:
=Nz([date field], [alternate date field])
Click to expand...
Click to expand...
 
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


David M. said:
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


Marshall Barton said:
David said:
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.
Click to expand...


Try using an expression in the text box:
=Nz([date field], [alternate date field])
Click to expand...
Click to expand...
 
David said:
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.--
Click to expand...


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.)
 
David said:
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.--
Click to expand...


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.)
 
David said:
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
Click to expand...


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 said:
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
Click to expand...


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


Marshall Barton said:
David said:
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
Click to expand...


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]))
Click to expand...
 
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


Marshall Barton said:
David said:
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
Click to expand...


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]))
Click to expand...
 
David said:
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.
Click to expand...


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 said:
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.
Click to expand...


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?
 
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.
 
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 said:
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.
Click to expand...


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.
 
Back
Top