Hi Guys,

I'm working on a Form in Access 2007. In the vba code window I have the following:

Please note the query: SELECT CC_NUMBER FROM COST_CENTER WHERE COST_CENTER.SUBSECTION_ID=" & SubsectionID & ";"

As you can see, it is quite a simple statement.
When I create an actual query in Access and use the value one "1" for the SubsectionID, I get the appropriate return of two records from the COST_CENTER table. However, when I run the code above containing the same SQL statement, I get only a return of one record.

Am I missing something? Any ideas as to why the code above is returning only one record when it should be returning two?

Alan

Hi Ashier

Looking at your query, it appears that it is 'loosing' the string in subsectionID. try putting ' after the = and before the ; for example ='" & subsectionid & "';".

This should help identify the variable as a string

I have added to the code below.

please advise if works ok

Martin

<CODE>
Private Sub Display_Cost_Centers(SubsectionID As String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim query As String
Dim recordCount As Integer

Debug.Print "SubsectionID: " & SubsectionID

query = "SELECT CC_NUMBER FROM COST_CENTER WHERE COST_CENTER.SUBSECTION_ID='" & SubsectionID & "';"
Debug.Print query
'On Error GoTo ErrHandler

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(query, dbOpenSnapshot)

recordCount = rst.recordCount
Debug.Print "RecordCount: " & recordCount

...

END SUB
</CODE>