"jillandgordon" <ji***********@sbcglobal.net> wrote in
news:bZ***************@newssvr30.news.prodigy.com:
I have a problem. I am trying to create a query from two
different tables. One of the tables has a column with either a
date or an empty field in it. What I am trying to do is create a
column which either has a Y if there is a date in the field and
leaves the field blank if there is not. I have tried all of the
language I can think of for an "IF" function and failed.
I hope you are trying to create this column in the query, and not in
one of the tables, as it's derived data and shouldn't be stored.
Creating a column in the query that uses the formula
IsNull(DateField) will return a Boolean. You can then go to the
column's properties and set the format to display Yes/No or
True/False, whichever you like.
Keep in mind, though, that the value of that derived column will not
be "Y" but True, the numeric value of which is -1 in Access/VBA.
However, that's not really relevant, as you'll never want to use
that derived field for anything but display purposes. For that
reason, you may want to not even have the derived field in your
query at all, but instaed have a calculated control on a report or
form to display the value. The formula would be the same, except
preceded with an = (as with all calculated control sources).
Why do you not need to know the value returned by your calculated
field in your query? Because you'd never test that derived value.
That is, if you want just the records that have no data, you
wouldn't test if your derived field is true, but instead if the date
field in question Is Null. Putting criteria on a calculated field is
always substantially slower than doing it against the actual data,
so in cases like this where it's quite easy, you wouldn't want to do
that.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc