SQL-String vs number

[tina]

try this

strSQL = "SELECT * FROM QDelaySum WHERE ServiceID = '" &
strSVCID & "'"

if you don't enclose your value in quotes (in this case
single quotes), Access assumes it's a number.

hth

 

[Junior]

Thanks Tina
That helped some - but now i get a runtime error 3061 - too few parameters

 

[tina]

i'm not so great at visualizing, Junior, sorry. to figure
that out, i'd have to see your db. if nobody else helps
you out and you're still stuck, you're welcome to email it
to me if you want, and i'll try to help. compacted,
please, and proprietary data removed, and no more that 1.5
mb zipped.

 

[Dirk Goldgar]

Thanks Tina
That helped some - but now i get a runtime error 3061 - too few
parameters

If QDelaySum is itself a query, probably that query has parameters --
for example, references to controls on forms, such as are often used in
criteria -- which are not resolved automatically by the DAO
OpenRecordset method. To get a recordset from a parameter query, try
doing it like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strSVCID As String
Dim strSQL As String

' ... get strSVCID from somewhere ...

strSQL = "SELECT * FROM QDelaySum " & _
"WHERE ServiceID = '" & strSVCID & "'"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", strSQL)

With qdf

For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = .OpenRecordset

End With

With rs

' ... do stuff with rs ...

.Close
End With

Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

 

[tina]

i was hoping somebody would come up with a solution. i
don't know if that helped junior, but i copied it in a
flash and saved it for my future reference - thanks
dirk!