Solved

Selecting records to process

Posted on 1999-01-19
5
136 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now