querydef as a number

[sha7jpm]

Hi all,

I have been struggling with a querydef problem.. it works fine for one part of my database where the query is based on a organisation name (string) but for this part I need it as a number, I have tried changing the var declaration but it does not help, is there a way to solve this? at present the query has written to it, "240" or "250", obviously I need to somehow remove the speechmarks, but clearly it is more complicated than that as I am going around in circles!

ta muchly

john


Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim Q As QueryDef
Dim db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
Dim stDocName As String

' Build a list of the selections.
Set ctl = Me![PGRList]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Modify the Query.
Set db = CurrentDb()
Set Q = db.QueryDefs("individual blue Form Report")
'Q.sql = "SELECT [new final table].ProjectsCode, [new final table].[Project description], [new final table].[Instruments & Quantity], [new final table].[x:\ File Location], [new final table].[Final Record Total], [new final table].[DPG Researcher], [new final table].[RDS Researcher], [new final table].Completed, [new final table].Statistician, [new final table].[Research Team], [new final table].[Date of Stage 4 Completed] FROM [new final table]WHERE [new final table].[Instruments & Quantity] In(" & Criteria & ");"
'Q.Close

'Q.sql = "SELECT [PGR lea].LEANo, First([PGR lea].Title) AS FirstOfTitle,
'First([PGR lea].Fname) AS FirstOfFname, First([PGR lea].Sname) AS FirstOfSname,
'First([PGR lea].[Job Title]) AS [FirstOfJob Title], First([PGR lea].Description)
'aS FirstOfDescription, [lea and region].LEAname, [lea and region].Region
'FROM [PGR lea] INNER JOIN [lea and region] ON [PGR lea].LEANo = [lea and region].LEANo
'GROUP BY [PGR lea].LEANo, [lea and region].LEAname, [lea and region].Region
'HAVING [lea and region].LEAname In(" & Criteria & ");"
'Q.Close

Q.sql = "SELECT [PGR main].PGRID, [PGR main].LEANo, [PGR main].Title, [PGR main].Fname, [PGR main].Sname, [PGR main].Gender, [lea and region].LEAname, [lea and region].Region , [PGR Blue form].BlueformDescription, [PGR Blue form].BlueformReminder FROM ([PGR main] INNER JOIN [lea and region] ON [PGR main].LEANo = [lea and region].LEANo) LEFT JOIN [PGR Blue form] ON [PGR main].PGRID = [PGR Blue form].pgrid GROUP BY [PGR main].PGRID, [PGR main].LEANo , [PGR main].Title, [PGR main].Fname, [PGR main].Sname, [PGR main].Gender, [lea and region].LEAname, [lea and region].Region, [PGR Blue form].BlueformDescription, [PGR Blue form].BlueformReminder HAVING [PGR main].PGRID In(" & Criteria & ");"
Q.Close

' Run the report from the listbox fed query
stDocName = "individual blue Form Report"
DoCmd.OpenReport stDocName, acPreview

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

 

[Mile-O]

where the query is based on a organisation name (string) but for this part I need it as a number, I have tried changing the var declaration but it does not help, is there a way to solve this? at present the query has written to it, "240" or "250", obviously I need to somehow remove the speechmarks, but clearly it is more complicated than that as I am going around in circles!

Have you considered the CLng() function?