Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

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.
0
WPentecost
Asked:
WPentecost
  • 2
  • 2
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now