Slect Statement with a Where Clause
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:
VB Code:
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
Re: Slect Statement with a Where Clause
You have to wrap your variable in with quotation marks. Try this:
VB Code:
rsBatchHeader.Open "Select *
From BatchHeader
Where BatchHeader.TransactionType = '" & strTransactionType & _
"' and BatchHeader.BatchNumber = '" & strBatchNumber & "'", _
prvCNNBatchHeader, adOpenDynamic, adLockOptimistic
Re: Slect Statement with a Where Clause
Try
Code:
"SELECT * FROM BatchHeader
WHERE BatchHeader.TransactionType = '" & strTransactionType & "' "
AND BatchHeader.BatchNumber = '" & strBatchNumber & "' ", prvCNNBatchHeader, adOpenDynamic, adLockOptimistic
Re: Slect Statement with a Where Clause
Create a varable called strSQL
VB Code:
strSQL = "Select * From BatchHeader Where BatchHeader.TransactionType = '" & strTransactionType & " and BatchHeader.BatchNumber = '" & strBatchNumber & "'"
This assumes that the field types in the database are strings
Then:
VB Code:
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.
Re: Slect Statement with a Where Clause
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.
Re: Slect Statement with a Where Clause
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 !!