Mutiple criteria for report

[rry2k]

I am trying to run a report with the following string and I am getting a syntax type error. I believe I am missing quotes or something like that but I can't resolve this.

DoCmd.OpenReport "Time Sheet", acPreview, ,
(("[TimeCardID]=" & [TimeCardID]) _
& ("[Combowkend]=" & [WeekEnding]))

Thanks for the help
Russ

 

[MSMINNICH]

Russ- Try this

dim strCriteria as string

strCriteria = "TimeCardID = " & [TimeCardId] & " AND Combowkend = " & [WeekEnding]

DoCmd.OpenReport "Time Sheet", acPreview, ,
strCriteria


Scott

 

[rry2k]

Scott,
Good thought but that didn't work. I keep getting an error message that says: Can't find the field '|' refered to in your expression. Let me clarify a couple things: first combowkend is a combobox on the form that contains dates and weekending is a field in the table. Maybe this will make a difference.

Thanks for the help..Russ

 

[MSMINNICH]

Russ-
OK, We're close. The syntax of the WHERE clause is:
Tablefield = Formfield. So if you have the following

This is the This is the
Table Form
field Field
strCriteria = "TimeCardID = " & [TimeCardId]
strCriteria = strCriteria & " AND WeekEnding = " & ComboWkend

Notice that the database fieldnames are within the quotes and the Form fieldnames are not. Another important thing is to make sure that neither Form field can be null.
Scott

 

[rry2k]

thanks for all your help. That did the trick On the form and subform. Now I just need to get it to work on my report and subreport.

 

[MSMINNICH]

Russ-
Another way to do this is in your Report Open event write
dim frm as form
set frm = Forms!MyFormName

Me.Recordsource = "Select * from MyRecs WHERE TimeCardID = " & frm.TimeCardID & " AND WeekEnding = " & frm.ComboWkEnd

All on one line, of course. MyRecs is a table or queryname. The table or query would need to have all of the fields that you need for the report.
You can also do ranges using relational operators like:

Me.Recordsource = &quot;Select * from MyRecs WHERE TimeCardID > &quot; & frm.TimeCardID1 & &quot; AND TimeCardID < &quot; & frm.TimeCardId2 & &quot; AND WeekEnding = &quot; & frm.ComboWkEnd