SELECT statement

I have the following code

If conn.Execute("SELECT * FROM [sheet1$] WHERE EmployeeNo = " & parsed("EmpNum") & " And DepartmentNo = " & parsed("Dept") & "").RecordCount > 0 Then

...code

basically what I want is, if the recordcount from the select statement is greater than 0 then that means the record exists then peform some other operation. But I get the error "Data type mismatch in criteria expression"

Any ideas to why?

Looking into HAVING COUNT > 0...if you need to make sure you data set returns records, you want to use the HAVING COUNT method within your SQL Statement.

Can you give me an example using HAVING COUNT.

Looking into HAVING COUNT > 0...if you need to make sure you data set returns records, you want to use the HAVING COUNT method within your SQL Statement.

Well, first off lets understand the point of this. Typically having count is used to return a recordset that has no duplicates or some other condition. You should use a unique field, and do a group by. So, on your table, do you have a unique field? It would look something like this:



"SELECT my_ID, count(*) FROM myTable GROUP BY my_ID HAVING COUNT(*) > 0"

You are trying to return a set of records (RecordSet) to a Connection object, which you cannot do. Dim an ADODB.Recordset object and return the query results to that:


Dim RS As ADODB.RecordSet
Dim SQL As String

Set RS = New ADODB.RecordSet
SQL = "<your SQL here>"
RS.Open SQL, conn, adOpenStatic, adLockReadOnly

Debug.Print RS.RecordCount

If RS.RecordCount > 0 then
' blah blah blah
End If

That is correct, but you CAN use the HAVING COUNT method within your SQL statement for certain conditions. The recordcount method is using to return the current recordcount of the query or command string opened. HAVING COUNT will restrict the query results.

I was tying the reply before all this Having business LOL :p

Ok, let me explain what i'm trying to do.

If conn.Execute("SELECT * FROM [sheet1$] WHERE EmployeeNo = " & parsed("EmpNum") & " And DepartmentNo = " & parsed("Dept") & "").RecordCount > 0 Then
....code

I want to check if the record exist, if it does then i will insert an row. there might be a better way to check if the record exist in the conn connection.



You are trying to return a set of records (RecordSet) to a Connection object, which you cannot do. Dim an ADODB.Recordset object and return the query results to that:


Dim RS As ADODB.RecordSet
Dim SQL As String

Set RS = New ADODB.RecordSet
SQL = "<your SQL here>"
RS.Open SQL, conn, adOpenStatic, adLockReadOnly

Debug.Print RS.RecordCount

If RS.RecordCount > 0 then
' blah blah blah
End If

OK, a simplier method:



Dim sSQL as string

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Exit sub
Else
'do something
End If



....code

I tried your code. I have the following


ssql = "SELECT COUNT(*) as total_count FROM [sheet1$] WHERE EmployeeNo = " & parsed("EmpNum") & " And DepartmentNo = " & parsed("Dept") & ""
rec.Open ssql, conn, adOpenKeyset, adLockOptimistic, adCmdText



If rec.Fields("total_count").Value > 0 Then
conn.Execute "INSERT INTO [sheet1$] (EmployeeNo, DepartmentNo,JobNo, Method, TaxFreq, CheckCount, REGULAR) VALUES('" & parsed("EmpNum") & "', '" & parsed("Dept") & "', '" & "9079" & "', '" & "DIR DEP" & "', '" & "WEEKLY" & "', '" & "2" & "', '" & FormatNumber(parsed("Amount") / 100, 2, vbFalse, vbFalse, vbFalse) & "')", affected
End If

but i get a "Object variable or with block varialbe not set"

OK, a simplier method:



Dim sSQL as string

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Exit sub
Else
'do something
End If



....code

Are all of your objects declared properly?



Dim rs as ADODB.recordset
Dim cn as ADODB.connection

Set cn = New ADODB.connection
Set rs = New ADODB.recordset



Are your "parsed" values being returned correctly? If you run this code in debug mode, do these variables have values?

yep everything is declared properly

Are all of your objects declared properly?



Dim rs as ADODB.recordset
Dim cn as ADODB.connection

Set cn = New ADODB.connection
Set rs = New ADODB.recordset



Are your "parsed" values being returned correctly? If you run this code in debug mode, do these variables have values?

I believe the error is in your conn.execute command. But first:

1) I believe (though not sure) you want to run this execute command if the value IS zero. If this record already exists, you don't fire your execute command (> 0). If total_Count is = 0, then run it.

One step at a time...does this work:



Dim sSQL As String

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Else
msgbox "no values present"
End If

'just back out for now

Exit Sub



Does this piece work?

Well, if the value > 0 the that means the record exist so I want to run the execute method. I tried your code, but it stills gives me the same error


I believe the error is in your conn.execute command. But first:

1) I believe (though not sure) you want to run this execute command if the value IS zero. If this record already exists, you don't fire your execute command (> 0). If total_Count is = 0, then run it.

One step at a time...does this work:



Dim sSQL As String

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Else
msgbox "no values present"
End If

'just back out for now

Exit Sub



Does this piece work?

If you run the code in debug mode, where does it die? Does it die on the recordset open line? Or after? I assume you can do a SELECT COUNT() on an Excel spreadsheet.

it dies at that line

rec.Open ssql, conn, adOpenKeyset, adLockOptimistic

Ok, I just ran a test. This produced a "total_Count" of 8, which is correct. See the code below: connection to Excel and returning the recordcount of the query:



Public Sub excelTest()
Dim cn As ADODB.Connection
Dim strQuery As String
Dim rs As ADODB.Recordset


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

strQuery = "SELECT COUNT(*) as total_Count FROM [Sheet1$]"

rs.Open strQuery, cn, adOpenStatic, adLockOptimistic, adCmdText

MsgBox rs.Fields("total_Count").Value

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub