Selecting records to process

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.
WPentecostAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
mark2150Commented:
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
 
CS011999Commented:
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
 
ZenMasterCommented:
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
 
ZenMasterCommented:
(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
 
mark2150Commented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.