Slect Statement with a Where Clause

[The_Game]

I'm trying to create a function that will search an Access Database to see if a record already exists or not.

I am having problems with my select statement with regards to the where part of it.


Here's what i have so far:

Option Explicit
Private prvCNNBatchHeader As New ADODB.Connection
 
 
Private Function FindExistingRecord(strTransactionType As String, strBatchNumber As String) As Boolean
 
Dim rsBatchHeader As New ADODB.Recordset
 
 
rsBatchHeader.Open "Select * From BatchHeader Where BatchHeader.TransactionType = strTransactionType and BatchHeader.BatchNumber = strBatchNumber", prvCNNBatchHeader, adOpenDynamic, adLockOptimistic
 
end Function

I am getting an error stating: [Microsoft][ODBC Microsoft Access Driver] Too Few Parameters. Expected 2.

Where i run the select part without the where clause, it seems to work fine, but not with the Where.

Hope someone can help
thanks

[Mark Gambo]

You have to wrap your variable in with quotation marks. Try this:

rsBatchHeader.Open "Select * 
From BatchHeader 
Where BatchHeader.TransactionType = '" & strTransactionType & _
"' and BatchHeader.BatchNumber = '" & strBatchNumber & "'", _
prvCNNBatchHeader, adOpenDynamic, adLockOptimistic

[Hack]

Try

"SELECT * FROM BatchHeader 
WHERE BatchHeader.TransactionType = '" & strTransactionType & "' "
AND BatchHeader.BatchNumber = '" & strBatchNumber & "' ", prvCNNBatchHeader, adOpenDynamic, adLockOptimistic

[GaryMazzone]

Create a varable called strSQL

strSQL = "Select * From BatchHeader Where BatchHeader.TransactionType =  '" & strTransactionType & " and BatchHeader.BatchNumber = '" & strBatchNumber & "'"

This assumes that the field types in the database are strings

Then:

rsBatchHeader.Open strSQL, prvCNNBatchHeader, adOpenDynamic, adLockOptimistic

The varables you are passing need to attached to the statement as varables and not included as part of the statement string.

[The_Game]

OK, thanks guys i have the select statement working now, but i'm still having problems with my overall search.

I'm trying to take two user inputs (strTransactionType, strBatchNumber) and search through an Access Database table (BatchHeader) to see if they exist.

If they exist, i want to load the information to the form for editing and if they don't exist i want to create a new record.

[litlewiki]

make sure both the fields exist in your table ,one of them is a primary key and then just use the and keyword in the where clause !!