By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Criteria for Access 97

P: n/a
I hope this group does not mind the most basic questions. I am trying
to teach myself Access in two different versions. My company now uses
Access 97 but, in the next few months, will be switching to Access
2003, so I am taking self-paced tutorials in both.

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.
Thanks for help with this very basic question.

Gordon
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"jillandgordon" <ji***********@sbcglobal.net> wrote in message
news:bZ***************@newssvr30.news.prodigy.com. ..
I hope this group does not mind the most basic questions. I am trying
to teach myself Access in two different versions. My company now uses
Access 97 but, in the next few months, will be switching to Access
2003, so I am taking self-paced tutorials in both.

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.
Thanks for help with this very basic question.

Gordon



in VBA use structure

If...Then...Else

that mean u must have recordset open & you walk recordset etc. so probably
2 complicated for u yes?

in query use

IIF()

this very happy function & can do many things. (u see help file ok?) so in
new query column u test date column value with IIF() & if date column have
date then new column = Y else new column = null yes?

make alias in empy query field name ok? then u go:

MyNewQueryTest: IIf(Not IsNull([MyDateField]),"Y")
u run query. this make Y in all records where date not is null but leaf all
other records blank.

Sherwood Wang
***MVP***


Nov 13 '05 #2

P: n/a
IIF(IsNull([MyField],"","Y")

if the field is formatted as a date at table level, then you can use
IsNull([MyField])

otherwise, if it's a text field, you'd use
IIF==(IsDate([MyField]),"Y","")

Nov 13 '05 #3

P: n/a
IIF(IsNull([MyField],"","Y")

if the field is formatted as a date at table level, then you can use
IsNull([MyField])

otherwise, if it's a text field, you'd use
IIF==(IsDate([MyField]),"Y","")

If you're new to Access, play with the help files... useful info in
there once you learn your way around.

Nov 13 '05 #4

P: n/a

"jillandgordon" <ji***********@sbcglobal.net> wrote in message
news:bZ***************@newssvr30.news.prodigy.com. ..
I hope this group does not mind the most basic questions. I am trying
to teach myself Access in two different versions. My company now uses
Access 97 but, in the next few months, will be switching to Access
2003, so I am taking self-paced tutorials in both.

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.
Thanks for help with this very basic question.

Gordon


also u not please use access 97 ok? very old & stupid version. only mr.
larry linson use it yes?

Sherwood Wang
***MVP***
Nov 13 '05 #5

P: n/a
"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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.