Adding Report Filter By VBA From Function A2K

  • Thread starter Thread starter Jon P via AccessMonster.com
  • Start date Start date
J

Jon P via AccessMonster.com

Hi,
I am trying to launch a report from a module in my db using the below text.


DoCmd.OpenReport "rptM16Report", acViewPreview, "teamleaderemail = Bloggs,
Fred"

Unfortunately on the report because Bloggs, Fred contains a comma it would
have to appear as "Bloggs, Fred" however when I do this it cause a "Expected
End Of Statement" error.

I have tried using ' instead of " and also putting the name into a string and
using that to filter the report.

Unfortunately I am working with a database that cannot be modified and am
unable to remove the comma.

Any help would be greatly appreciated.

Cheers
JP
 
Jon,

Try:

DoCmd.OpenReport "rptM16Report", acViewPreview, _
"teamleaderemail = 'Bloggs,Fred'"

(single quotes to denote a text string within another string).

HTH,
Nikos
 
Cheers for the reply Nikos...

Apologies, I didnt explain myself correctly. Where teamleaderemail="Bloggs,
Fred" Bloggs, Fred is actually a variable passed through to the function.

I am also running into the problem with setting the SQL for a recordset.
Somehow I need to pass Bloggs, Fred into the string with " encapsulated
around however I try setting a variable to

x = chr$(34) & TeamLeaderEmail & chr$(34)

then use for my sql

strSQL = "SELECT * From tblWorking WHERE TeamLeaderEmail=" & x

Not too sure if the above makes sense....
 
Jon,

Yes, it does make sense. The "encapsulation" is done in the string, not
on the variable. Try this:

strSQL = "SELECT * From tblWorking WHERE TeamLeaderEmail = '" & _
TeamLeaderEmail & "'"

HTH,
Nikos
 
Thanks for your help, worked a treat.

Nikos said:
Jon,

Yes, it does make sense. The "encapsulation" is done in the string, not
on the variable. Try this:

strSQL = "SELECT * From tblWorking WHERE TeamLeaderEmail = '" & _
TeamLeaderEmail & "'"

HTH,
Nikos
Click to expand...
 
Back
Top