Searching a table for a value using Query in VBA

[Challseus]

Hi all:

I want to be able to search a database for a specific value, and if it isn't there output an error message. I want to be able to do all this in VBA. Is there some sort of Query function or something like this?

Any help would be much appreciated.

 

[Russell Hauf]

Search Help for the Database functions (DLookup, in particular).

Let us know if you are still having trouble after you have tried this...

-Russell

 

[Gates Is Antichrist]

I'm not a query expert but I play one on TV but I can do this kind of thing "IN" Access; at any rate, you have a better chance asking this on the Excel side. Try the Excel help item "About importing data" too.

If you're brave you can try analyzing the code produced by this in Excel (turn on "record macro"):
file/open/select filetype Microsoft Access/pick a Select query

This will link up to an existing query in an Access database. It would produce a column of database query results in e.g. A1 through {however many rows are needed, to show matches to your "specific value"}. You could then have your VBA compare A1 to "" or Empty or whatever. Or just use a cell directly, a la
if(A1="","","Error msg")

I hope I got your drift with this question.

At any rate, this may be more trouble than for what you want. As the prior post indicates, use dlookup, which specifically tells you whether an item exists in a database or not -- as a Boolean answer.

DCOUNT may also right your flight.

 

[Russell Hauf]

I disagree about asking on the Excel side. This is not at all an Excel question. DCount or DLookup will do the trick:

If IsNull(DLookup("tblMine","[SomeField]","[Somefield] = 'Bill'") Then
    'Error Message
End If

'or

If DCount("tblMine","[Somefield]","[Somefield = 'Bill'") = 0 Then
    'Error Message
End If

HTH,

Russell

 

[Gates Is Antichrist]

Well I believed it to be a stretch for this thin forum to give VBA-in-Excel answers. There are tons of Access users in the Excel forum - and some of them quite expert on that front.

 

[Challseus]

Sorrry I did not get back to you guys last night, but my home PC's keyboard is screwed up. In fact, only half the letters on it work. But that's besides the point...I appreciate the fast answer and I'm looking forward to trying it out at work this afternoon. I'll keep you guys posted as to if it works or not. Again, your help is much appreciated.

 

[Challseus]

Okay, I'm still having probelms. Here's my code:

If IsNull(DLookup("Students", "[ssn]", "[ssn] = 'Bill'")) Then
    MsgBox "Not Found"
End If

Now am I doing this right? "Students" is the name of the table I want to Query. "ssn" is the field I want to look at. Finally, "Bill" is the name I'm looking for.

I'm getting an error along the lines of cannot find table or query 'ssn'. Is the table private and I have to use an accessor function?

 

[Russell Hauf]

Okay, I'm still having probelms. Here's my code:

If IsNull(DLookup("Students", "[ssn]", "[ssn] = 'Bill'")) Then
    MsgBox "Not Found"
End If

Now am I doing this right? "Students" is the name of the table I want to Query. "ssn" is the field I want to look at. Finally, "Bill" is the name I'm looking for.

I'm getting an error along the lines of cannot find table or query 'ssn'. Is the table private and I have to use an accessor function?

Probably should be something like:

If IsNull(DLookup("Students", "[ssn]", "[name] = 'Bill'")) Then
    MsgBox "Not Found"
End If

Where "name" is the name of the field where "Bill" will be found.

HTH,

Russell

 

[Challseus]

Thx, that helped out, except I changed this:

If IsNull(DLookup("Students", "[ssn]", "[ssn] = 'Bill'")) Then 
    MsgBox "Not Found" 
End If

To this:

If IsNull(DLookup("ssn", "Students", "[ssn] = 'Bill'")) Then 
    MsgBox "Not Found" 
End If

I truly appreciate your help.

 

[Russell Hauf]

Good! Like I said, consult the help files (not the Russell files!).