473,287 Members | 1,834 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Criteria for Access 97

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
5 1881

"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
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
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

"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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Steve | last post by:
I need help with a function to be used as the criteria for a query. The field in the query is InventoryStatus: - . The function is built around a Select Case set of about twelve cases. Function...
6
by: AAVF | last post by:
Hi We have a problem with a query. An Access database links via ODBC to a UNIX server. To speed things, we use the ODBC to load the relevant tables to the local PC that runs Access so that...
2
by: Enterprise | last post by:
I'm stuck. I use Access 2000. I have a date field in a query. What I need done is if a Limit_Date function returns a true, I want the date field to be Between 1/1/02 and 1/1/03, otherwise I want it...
3
by: pelcovits | last post by:
I am trying to set up an unbound form to enter report criteria. I've followed the MS Office Assistance document: "Create a form to enter report criteria" which describes how to enter data (such...
8
by: Steve Jorgensen | last post by:
Mailing List management is a good example of a case where my conundrum arises. Say there is a m-m relationship between parties and groups - anyone can be a member of any combintation of groups. ...
2
by: rinmanb70 | last post by:
I have a QBF form/query and a report from the QBF that shows the results of the QBF. I would like to show the criteria on the report that was used in the QBF to get the info on report. I can't...
3
by: rhobson2 | last post by:
Hello, I wrote a database applicaiton using Access XP (2002) and everything has been working good for the client until they purchased a couple of new computers with Access 2003. The meetings...
1
by: DrJarmin | last post by:
Hello The problem is this: in the criteria for a list box I reference the parent form - and Access KEEPS changing the criteria for one that won't work. Details below: I have a couple of list...
2
by: Aussie Rules | last post by:
Hi, I have a access 2007 database with a cross tab query. Based on the selection critieria the cross tab may contain a different number columns in the result. For example if the query is...
2
by: Denise | last post by:
Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.