Parameter not being passed to Query Def

[gkadell]

Hi! I have an Access 2000 db running on a Windows 95 machine on an 2000 server/Novell environment. I have several working functions that build SQL statements from forms and pass the sql to query defs. However, on
Wednesday Sept 25 all new function I wrote are not passing the SQL on (I get an error message "3129 invalid sql statement). Here's the code for the simplest query without error trapping:

BuildBiasGreaterOne2 (this is the function that builds the SQL string for one of the queries):

Function BuildBiasGreaterOne2(strSQL9 As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "SELECT qryBiasGreaterOne1.PRODUCT_CODE, "
strSelect = strSelect & "qryBiasGreaterOne1.PRODUCT_DESC, "
strSelect = strSelect & "qryBiasGreaterOne1.GrandU, "
strSelect = strSelect & "qryBiasGreaterOne1.GrandDesc, "
strSelect = strSelect & "qryBiasGreaterOne1.SALES_DATE, "
strSelect = strSelect & "qryBiasGreaterOne1.ABS_FCST_VAR, "
strSelect = strSelect & "qryBiasGreaterOne1.ABS_FCST_VAR_PCT, "
strSelect = strSelect & "qryBiasGreaterOne1.GLOBAL, "
strSelect = strSelect & "qryBiasGreaterOne1.BIAS, "
strSelect = strSelect & "qryBiasGreaterOne1.Diff"

strFrom = " FROM qryBiasGreaterOne1"

strWhere = &quot; WHERE ((qryBiasGreaterOne1.BIAS)>1) AND ((qryBiasGreaterOne1.Diff)<4)&quot;


strSQL9 = strSelect & vbCr & strFrom & vbCr & strWhere

MsgBox strSQL9
Debug.Print strSQL9


BuildBiasGreaterOne2 = True

End Function

Note that when I debug.print the SQL and paste it into a new query, the query runs, so the problem is not whether or not the SQL is being built, or if it is invalid.

Here's the code for the query def that strSQL9 is being passed to:

Function MakeBiasGreaterOne2(strSQL9 As String) As Boolean

Dim qdf As QueryDef

Set qdf = CurrentDb.CreateQueryDef(&quot;qryBiasGreaterOne2&quot
qdf.SQL = strSQL9
qdf.Close
RefreshDatabaseWindow


MakeBiasGreaterOne2 = True

The query def runs - it just makes an empty query. Any ideas about what's going on? Do you see any code problems? The 10 query defs I made before Sept 25 still work fine, it's just all the ones I made since then that don't work.

Gladys

 

[PaulBricker]

I haven't tested this out completely, but I suspect the problem is in the line
strSQL9 = strSelect & vbCr & strFrom & vbCr & strWhere

Try this.
strSQL9 = strSelect & strFrom & strWhere

The string you are passing to the qdf is broken because there is no line continuation between each str (strSelect, strFrom and strWhere). It will work in the query because you are writing SQL. For the QueryDef, you need a continuous string value and that's not what you are producing.

Paul

 

[gkadell]

Paul,

Thanks for the input, but I tried taking out the vbcr and it makes no difference. The reason I put them in in the first place was because I was getting error messages from DAO.QueryDef in the other (still working) query defs I created. When I put the vbcr in the query def could read the sql fine (I don't remember what the error message said exactly, but it pointed to the part of the sql it had problems with).

 

[PaulBricker]

Well, I'm not sure Gladys. I looked up the SQL property of the QueryDef Object and it seems like the string isn't an issue. Sorry about that. It's a little different than the approach I use with CreateQueryDef, but I modified your Functions into one Sub and it created the QueryDef and populated it with the correct records so the bulk of the code seems OK . How do you call these Functions?
Paul