Solved

Selecting records to process

Posted on 1999-01-19
5
134 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
(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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 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…

772 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

10 Experts available now in Live!

Get 1:1 Help Now