Compile error Type mismatch

[detrie]

I am trying to count the occurrences of the LEFT 6 characters on my form field.

This code throws a "Compile Error: Type mismatch" on intDailyCount =rs I cant seem to figure it out

Private Sub Command79_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intDailyCount As Integer
Dim sqlRoot As String

Set db = CurrentDb

sqlRoot = "SELECT tbl_SampleGroups.GroupNumber, Count(tbl_Samples.GroupID) AS CountOfGroupID " & _
        "FROM tbl_Samples INNER JOIN tbl_SampleGroups ON tbl_Samples.GroupID = tbl_SampleGroups.SampleGroupID " & _
        "GROUP BY tbl_SampleGroups.GroupNumber " & _
        "HAVING (((tbl_SampleGroups.GroupNumber)=Left([Forms]![frm_Login]![GroupNumber],6)));"

Set rs = db.OpenRecordset("sqlRoot")


intDailyCount = rs
Me.txtSampleCount = intDailyCount

      
Set rs = Nothing

End Sub

 

[MarkK]

A couple of changes you need to make...

[COLOR="Green"]'remove the quotes around sqlRoot[/COLOR]
Set rs = db.OpenRecordset(sqlRoot)

[COLOR="green"]'rs is a recordset, and has a fields collection, you need to use a field, maybe...[/COLOR]
intDailyCount = rs![COLOR="Blue"]CountOfGroupID[/COLOR]

See how the field name in blue is a field in your SQL?

 

[detrie]

Ok.. that makes sense. CountofGoupID doesn't need to exist on form does it? (easy to add if it does)
Also I think my syntax is still off. I am getting a Run-Time error '3061': Too few parameters. Expected 1 on set rs

Set rs = db.OpenRecordset(sqlRoot)

 

[MarkK]

There's an unrecognized identifier in your SQL. Debug.print the value of the sqlRoot variable to the immediate pane, copy that SQL text to a new test query, and test run it in the designer.

debug.print sqlRoot

 

[pbaldy]

OpenRecordset won't be able to resolve the form reference, you'll need to concatenate it, like:

"HAVING (((tbl_SampleGroups.GroupNumber)=Left(" & [Forms]![frm_Login]![GroupNumber] & ",6)));"

 

[MarkK]

Oh, that makes sense. Thanks Paul.

 

[detrie]

Ok I would have never found that. Thanks Paul

Things seem to run on one record but I get a Run-Time error: '3021': no current record here

intDailyCount = rs!CountOfGroupID

If I comment out

'Set rs = Nothing

it will run on all records. Is it required?

I need to revisit the SQL. It only works when the value is exactly 6 characters instead

 

[pbaldy]

Mark appears to be offline. You'll get that error when the recordset doesn't return any records. You can avoid it with:

If Not rs.EOF Then
  intDailyCount = rs!CountOfGroupID
Else
  MsgBox "No records found"
End If

 

[detrie]

Thanks Paul,
Do you see anything in my now concatenated form reference that is preventing the LEFT 6 function from working?

 

[MarkK]

Thanks Paul!

 

[pbaldy]

Not offhand, but the way to debug is what Mark mentioned in post 4. Here's more on that:

http://www.baldyweb.com/ImmediateWindow.htm

 

[detrie]

Paul and Mark. Thank you for your help with this. Because of the Debug.Print statement, I was able to see that I was trying to match the LEFT 6 of the form value against the entire 10 character of the table value instead of the LEFT 6.

Thank you again for your help and suggestions

Here is the revised working code

Private Sub Command79_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim intDailyCount As Integer
Dim sqlRoot As String

Set db = CurrentDb

Me.txtSampleCount = ""

sqlRoot = "SELECT Left([tbl_SampleGroups].[GroupNumber],6) AS GroupNo, Count(tbl_Samples.GroupID) AS CountOfGroupID " & _
        "FROM tbl_Samples LEFT JOIN tbl_SampleGroups ON tbl_Samples.GroupID = tbl_SampleGroups.SampleGroupID " & _
        "GROUP BY Left([tbl_SampleGroups].[GroupNumber],6) " & _
        "HAVING (((Left([tbl_SampleGroups].[GroupNumber],6))=Left(" & [Forms]![frm_Login]![GroupNumber] & ",6)));"

'Debug.Print sqlRoot

Set rs = db.OpenRecordset(sqlRoot)

If Not rs.EOF Then
  intDailyCount = rs!CountOfGroupID
Else
  MsgBox "No records found"
End If

Me.txtSampleCount = intDailyCount

Set rs = Nothing

End Sub

 

[pbaldy]

Mark was happy to help and I was happy to butt in.

 

[MarkK]

Teamwork makes the dream work!

 

[NadiaVKhan]

than kyo!!! ive needed this answer too!