too few parameters error '3061' 1

[timbert62]

i have been staring at & fiddling with this for hours on end & can not figure out what is wrong

i have read related tips on this forum, all of which make believe that is has something to do with how my quotes are ordered & the data types (especially in the WHERE clause), but i do not know enough about VBA to really know how it should be .. i have tried every combination i can think of, but i still get a 'too few parameters' error, sometimes expecting 3 sometimes 4 ??

any advise/guidance on getting this to work will be greatly appreciated


Function MedianProp(tName$, fldName$, Prop$) As String

Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount, i, x, y, OffSet As Integer

Dim EvalProp As String, PROP_ADD2 As String

Set MedianDB = CurrentDb()
EvalProp = Prop$
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName$ & "] FROM [" & tName$ & "] WHERE ([PROP_ADD2] = " & EvalProp & &quot ORDER BY [" & fldName$ & "]&quot

etc ..
etc ..

thank you it's a small world ...
... but i wouldn't want to paint it

 

[TerpFan2001]

Timber:

Try placing ' around the text you are searching for:

WHERE ([PROP_ADD2] = '" & EvalProp & "')

 

[cmmrfrds]

The easiest way to debug is to put your sql string in a variable and then use the variable in the recordset function. Use the debug to see that your string is being put together properly. I have found my syntax errors every time this way.

dim sqlstring as string
sqlstring = "SELECT [" & fldName$ & "] FROM [" _
& tName$ & "] WHERE ([PROP_ADD2] = " _
& EvalProp & &quot ORDER BY [" & fldName$ & "]"

debug.print "sql = " sqlstring

"Control G" to look at debug output

 

[timbert62]

thanks for the tips

i've incorporated both suggestions & end up with the debug print =

sql = SELECT [Age] FROM [rpt-qry Property Settlement Stats] WHERE ([PROP_ADD2] = 'CHAPLINWOODS') ORDER BY [Age]

it seems like i'd want " " around the CHAPLINWOODS, but i can't quite get it to do that !?!

i'll keep pluggin away at it, kinda like my 3rd grade daughter tackles her math 'problem of the week' ... "Guess & Check"

thanks

tim it's a small world ...
... but i wouldn't want to paint it

 

[timbert62]

OK, now i am really confused

i now have it to the point that the resultant sql statement =

sql = SELECT [Age] FROM [rpt-qry Property Settlement Stats] WHERE [PROP_ADD2]="CHAPLINWOODS" ORDER BY [Age]

which is what i thought i wanted. i even copied that statement & pasted it into the SQL-view of a new query & it worked

however, i STILL get a 'too few parameters' error, when i try to use the MEDIANPROP() function as part of another query

what am i missing???

thanks again

tim
it's a small world ...
... but i wouldn't want to paint it

 

[cmmrfrds]

I use ADO instead of DAO syntax, so I don't use the openrecordset, but check the parameters on the openrecordset method. It is telling you there is some conflict in how it is picking up or defaulting the parameters.

right click on OpenRecordset in your code, it will bring you to the reference library. Check all the expected parameters.

MedianDB.OpenRecordset

 

[timbert62]

hoooo-ray !

after i finally got the code right (thanks to the suggestion/advise on how to best debug sql code), i determined that the problem was no longer with the code of the function but with how i was using the function in a Access query.

originally, i was using the FUNCTION defined by my code in a QUERY that was based on a QUERY that was dependent on values from a FORM ... which seems to have introduced too many levels of complexity into things ( at least for me too weed the paramters all the way through)

to get it to work, i instead used my FUNCTION in a QUERY that was based on a QUERY that was NOT dependent on the FORM, the FUNCTION grouped results on values, so i didn't need the values determined by a FORM anyway

i'm quite sure that i don't really know WHY it works now, but i do know THAT it works now... which at this point is all that really matters

thanks for the help

tim

it's a small world ...
... but i wouldn't want to paint it