Solved

Selecting records to process

Posted on 1999-01-19
5
137 Views
Last Modified: 2010-05-03
I have a file with a date or null in a field. I want to
select all records with null in that field only for the
program to process. The file may have 5,000 records but
only 50 are null. I don't want to read all 5,000. I think
this can be done with SQL, but my program won't accept the
SQL file. I know I am doing something wrong.
0
Comment
Question by:WPentecost
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:mark2150
ID: 1469443
What do your database access statements look like. Give us some code to critique.

A SQL query should work just fine.

The SQL query will have to go thru all of the records to get the ones that match your search criteria, but this is transparent to you.

M

0
 

Expert Comment

by:CS011999
ID: 1469444
mark2150 is right. A Sql query should work fine & it will go thru all records to retrieve your specific ones anyway. Are you afraid of performance suffering if you read thru all 5000 rows?  Try : Select * From Table Where FieldName Is Null

why doesn't your program accept your Sql file? If you can be specific I can try to help.
0
 
LVL 1

Expert Comment

by:ZenMaster
ID: 1469445
Your SQL statement should look something like this: (example for Access)

SELECT [table].*, [table].[criteriafield]
FROM [table]
WHERE ([table].[criteriafield] IS NULL);

If this helps, let me know; I'll repost as an answer.
0
 
LVL 1

Expert Comment

by:ZenMaster
ID: 1469446
(Hit submit too soon)

PS:  I am assuming you are writing a VB app with an MDB file.  This assumption may be dangerous because there are lots of ways to write an app; What do you mean by "my program won't accept the SQL file."?
0
 
LVL 12

Accepted Solution

by:
mark2150 earned 50 total points
ID: 1469447
This was extracted from operational code where an existing Access database file called DOCS.MDB is opened and the RESULTS table is queried for null client records, CLIENT being a field in the RESULTS table. A check is made if there were no records found

RefPath = "C:\clients\docs.mdb"
Set db = DBEngine.Workspaces(0).OpenDatabase(RefPath)
SortKey = "select * from results where client is null'"
Set rs = db.OpenRecordset(SortKey)
If rs.BOF And rs.EOF Then GoTo isempty

Let me know if you need more than this.

M

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question