Criteria for Access 97 - Microsoft Access / VBA

jillandgordon

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

Subscribe Reply

1919

Sherwood Wang

"jillandgor don" <ji***********@ sbcglobal.net> wrote in message
news:bZ******** *******@newssvr 30.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...Els e

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

pietlinden

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

pietlinden

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

Sherwood Wang

"jillandgor don" <ji***********@ sbcglobal.net> wrote in message
news:bZ******** *******@newssvr 30.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

David W. Fenton

"jillandgor don" <ji***********@ sbcglobal.net> wrote in
news:bZ******** *******@newssvr 30.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(DateFiel d) 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

Similar topics

19327

Function In Query Criteria

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 MyCriteria() Select Case Forms!MyForm!MyCbx Case 1 My Criteria = "<0" Case 2 MyCriteria = "0"

Microsoft Access / VBA

5274

Criteria testing fields that may contain null values

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 all querying is done locally. One of the reports we run allows the user to list all invoices within a period. They are also allowed to select a customer code and a product set on

Microsoft Access / VBA

4239

Query Criteria Help

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 left alone(all records shown). I have an if statement in the criteria: iif(Limit_Date()=true,Between 1/1/02 and 1/1/03,"*") This does not work since the if statement returns strings. So if Limit_Date is true, then the criteria is "Between...

Microsoft Access / VBA

11076

Using a form (with a combo box) to enter report criteria

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 as dates) in a text box. This procedure works fine. However, I also need to enter data from a combo box and I cannot get this to work. On my form I've created an unbound combo box named "Name". In the query bound to the report I've entered the...

Microsoft Access / VBA

2969

UI Metaphors for complex criteria selection

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. Now, let's say the user wants to be able to send mailings to people who have various combinations of membership and non-membership in those groups. Here's a medium-complex example: (Knitting Group or Macrame Group) and Active Contact and Mailing...

Microsoft Access / VBA

4234

QBF search criteria to report

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 find how to do this except to show the "hard for users to understand" SQL WHERE statement. Anyone have a better way?

Microsoft Access / VBA

3515

Access 2003 form criteria for inline list box query

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 form has an unbound combo box listing all the meetings in the database allowing the user to navigate between meeting records. The meetings form also has a list box that displays a list of members associated with a meeting.

Microsoft Access / VBA

1915

Access 2003 KEEPS changing my criteria in a list box

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 boxes on a form. In the row source I use the primary key of the form as a criteria. For example, I have company details on the form. The list box shows

Microsoft Access / VBA

2338

setting Access query criteria

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 'select * from crosstab where project =1' would retun 6 columns, but if I change the query to project =2 then the result would be 20 columns. In my VB2008.net application I create the sql select statement. The problem

Visual Basic .NET

3666

Criteria works for FindFirst but not for Dlookup when text containsdouble quotes

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 record with double or single quotes in the location description and click a button to see if Access can tell me the ID number and find its way back to the original record. When the text contains double quotes the FindFirst works but the Dloookup...

Microsoft Access / VBA

9703

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10555

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10317

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10069

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

9127

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7607

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5503

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5636

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4277

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp