Help with Recordset Problem - Syntax? 4

[pdldavis]

Hi, This is part of a rather long inherited code.

This Sql statement works. It produces Weekend dates that are used for graphing:

stSqlWkEnd = "SELECT DISTINCT [date]+7-Weekday([date]) AS WkEnd, tblDailyRpts.Mhrs, " & _
"tblDailyRpts.SuSysID, tblDailyRpts.ItemType " & _
"From tblDailyRpts " & _
"WHERE (((tblDailyRpts.SuSysID) = [Forms]![frmPQSelDld]![txtSuSiD])) " & _
"ORDER BY [date]+7-Weekday([date]); "

Further down, rstWkEnd is defined as a Dynaset and I gather that this is to pick out the specific date fields from the sql statement above.

Set dbs = CurrentDb
Set rstWkEnd = dbs.OpenRecordset(stSqlWkEnd, dbOpenDynaset)

mWkEnd = rstWkEnd("WkEnd&quot
Do While Not rstWkEnd.EOF


At 'Set rstWkEnd', I get an error - Too Few Parameters. Expected - 1.

If I mouse over stSqlWkEnd, I see the sql statement - the data appears to be there.

rstWkEnd shows a blank date - 12:00 am

I am guessing that the Set rstWkend statement has a problem.

Can Someone help me out?

Thanks, Dan

 

[faeryfyrre]

You might try changing the SQL statement slightly.


stSqlWkEnd = "SELECT DISTINCT [date]+7-Weekday([date]) AS WkEnd, tblDailyRpts.Mhrs, " & _
"tblDailyRpts.SuSysID, tblDailyRpts.ItemType " & _
"From tblDailyRpts " & _
"WHERE (((tblDailyRpts.SuSysID) = " & _
[Forms]![frmPQSelDld]![txtSuSiD] & _
&quot) " & _
"ORDER BY [date]+7-Weekday([date]); "



This should fix the Set error you are getting when trying to create the recordset

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[pdldavis]

Thank you, that did the trick - finally!

Dan

 

[Quintios]

I see what changed, but I don't understand why it was necessary to move [Forms]![frmPQSelDld]![txtSuSiD] outside of the quotes.

Can someone fill me in here?

thx!

Q-

 

[RoyVidar]

When you build a sql string, you would want to assure that the values of variables or form references is concatinated into the vba string, not their names (there are some criteria strings wher you might ommit this, but which I do not remember - I find it easier and more consistent to concatinate everytime).

Consider MyVariable having the value 100:

[tt]sSQL1 = "Select * from table1 Where id = MyVariable"
sSQL2 = "Select * from table1 Where id = " & MyVariable

? sSql: Select * from table1 Where id = MyVariable
? sSq2: Select * from table1 Where id = 100[/tt]

(? = Debug.Print performed in the immediate pane - CTRL+G)

Which string is more likely to give the result you like;-)

Do a search on where condition, SQL, criteria here on TT and you'll find lot's for threads dealing with these issues, also what qualifier to use for different datatypes (' - single quote for string literals, # - hash for dates)

Roy-Vidar

 

[Quintios]

I guess my confusion is where Access will accept references to collection items, and where it needs hard coded values.

I just build all my queries in the query builder and then copy them straight over to VBA code when I need to.

thx!

Q-

 

[faeryfyrre]

It's been my experience that when building queries in the Query Builder you can reference controls on forms directly, but as soon as you copy that SQL into a module as a string (or indeed just build the SQL string within the module) you have to include the value of the referenced control rather than a reference to the control itself.

I guess one way to explain it would be to consider SQL in a Saved Query to be native and SQL in a module as requiring translation. Thus in a Saved Query Access/Jet knows implicitly that [Forms]![Form1]![TextBox1] is a reference to a control on a form, whereas through code Access/Jet cannot work out what [Forms]![Form1]![TextBox1] is so you have to reference the value rather than the control itself.

Therefore
In a Saved Query this is valid.
SELECT * FROM TABLE1 WHERE FIELD1 = [Forms]![Form1]![TextBox1];

wheras in a module you would have to do this
"SELECT * FROM TABLE1 WHERE FIELD1 = " & [Forms]![Form1]![TextBox1] & ";"


I've just read this back to myself and its as clear as mud. Hope people can follow my twisted logic here.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Quintios]

Plain as day, bro.

thx!

Q-

 

[RoyVidar]

Just to add to the confusion or amusement. When opening a report using a criteria string to filter it, one might say that "both types of assignement" are valid. So using faeryfyrre's example:

[tt]docmd.openreport "tst", acviewpreview, , _
"FIELD1 = " & Me![TextBox1][/tt]

[tt]docmd.openreport "tst", acviewpreview, , _
"FIELD1 = [Forms]![Form1]![TextBox1]"[/tt]

This is what I referred to above, in a little cryptic way, but as I said, I prefer to concatinate, it's more consistent ;-)

Roy-Vidar

 

[Quintios]

Speaking of opening a report, is there a way to put the query for the report source into the module of the report?

thx!

Q-

 

[pdldavis]

Here's some code from a report that uses a query where the Where Clause is obmitted depending on the text value of txtPrintType on the referenced form:


Private Sub Report_Open(Cancel As Integer)
Dim StSql As String
StSql = "SELECT tblLL.ID, tblLL.File, " & _
"tblLL.Area, tblLL.Project, tblLL.Date, " & _
"[ln] & " & " '" & "," & "'" & " & [fn] AS [Submitted By], tblLL.Problem, " & _
"tblLL.Solution, tblLL.Photo, " & _
"LLtblEquipInv.EquipInv, " & _
"tblLL.Approved FROM LLtblSubmittedBy " & _
"RIGHT JOIN ((LLtblEquipInv RIGHT JOIN LLtblLessonsLearned " & _
"ON LLtblEquipInv.EquipID = tblLL.EquipInv) " & _
"LEFT JOIN LLtblProjects ON tblLL.Project = LLtblProjects.ProjID) " & _
"ON LLtblSubmittedBy.NameID = tblLL.[Submitted By] "

Select Case Forms!llfrmLessons.txtPrintType
Case Is = 1

StSql = StSql & "WHERE (((tblLL.ID)=[Forms]![LLfrmLessons]![txtRecID])) " & _
"ORDER BY tblLL.ID; "

Case Is = 2
StSql = StSql & "ORDER BY tblLL.ID; "

End Select

Me.RecordSource = StSql

End Sub

 

[Quintios]

Ahh, it's in the Report Open section. Thanks...

thx!

Q-