Multiple 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

 

[Taff07]

Have you tried putting it all on one line to see if it runs without forcing a new line within the code structure?

It may help, I`ve had the same problem myself before.

Ian

 

[rry2k]

I'll give it a shot.
thanks

 

[Taff07]

sorry were some other bits within the code that looked wrong on closer inspection.

Try this revised bit below:

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

Hope this works

Ian

 

[Taff07]

Sorry for these bitty responses but getting distracted myself at this end . The above may not work depending on what data types the 2 fields are that you are using. If they are number fields then this will be ok. Otherwise you will need to add '' to either side of your cariables but within the string. Below would be the amendment if they are both text.

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

 

[rry2k]

Ian,
Good thought but that didn't get it. I still get the message: can't find the field '|' refered to in your expression.

 

[Taff07]

Is Combowkend the name of a field or the name of a Combo box control on a form?

Ian

 

[rry2k]

Combowkend is a combobox on the form and contains a date.

 

[Taff07]

The name you are decalring needs to be the name of the field that it is displaying. What is the name of the field?

Looking above i`d assume that you need to switch around the 2 names 'combowkend' and 'Weekending'. Also if your using a certain value of a form you will need to full quote the control using the formname etc.

ie. Forms!FormName!combowkend

Ian

 

[rry2k]

What I am trying to do is: open the report where timecardID on the form is equal to timecardID in the table and where the value selected in combowkend on the form is equal to the weekending field in the table.

Thanks for hanging in there with me on this.

 

[hchen]

Try following:

If [TimeCardID] is numeric, it should be:

stLinkCriteria = "[TimeCardID]=" & Me![TimeCardID]

If [Combowkend] is NON-numeric, it should be:

stLinkCriteria = "[Combowkend]=" & "'" & Me![Combowkend] & "'"

so, try this

DoCmd.OpenReport "Time Sheet",acPreview,,(("[TimeCardID]=" _
& ME![TimeCardID] & " AND [Combowkend]=" & "'" & Me! [WeekEnding] & "'&quot)

 

[rry2k]

Well that was a little better there was no error this time.
I switched combowkend and weekending in your example to me!combowkend and got prompted for weekending like it couldn't find the field. I know it's there and spelled correctly. I typed in a date and got all fields which obviously isn't right.
The field weekending is a data type date/time, is that an issue here?
??
Thanks..Russ

 

[Taff07]

Hi again,

you will need the # sign either side of your date instead of the ' signs.

 

[rry2k]

Thanks for the help and Time I appreciate it.

 

[Taff07]

Did that work in the end? If it did I`m guessing you switched the 2 names for your date value around in hchen's example so that the combo box is outside the string.

Hope I helped

Ian