ACCESS FEED: RE: [MS_AccessPros] Re: Opening filtered or code with where condition in code

Selasa, 27 September 2011

RE: [MS_AccessPros] Re: Opening filtered or code with where condition in code

Lee is really close.

DoCmd.OpenReport "rContactSimilar", acViewPreview, _
WhereCondition:="[SoundsLike] = '" & strSoundEx & "'"

Note that I didn't bother to count commas to correctly place the WhereCondition
parameter. I used a "named reference" to the parameter so the command knows
what to do with it. You can do that with any parameter in a call to a sub or
function.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
Sent: Wednesday, September 28, 2011 1:45 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Opening filtered or code with where condition in
code

I THINK the issue is here:
DoCmd.OpenReport "rContactSimilar", acViewPreview, , "[SoundsLike] = " &
strSoundEx

I THINK strSoundEx needs to be surrounded by single quotes, but I'm not sure if
the WhereCondition argument is read like a query or not.

(I put a space around the single quotes on the first line so you can see it
easier, but it should be in code like the 2nd line)

"[SoundsLike] = ' " & strSoundEx & " ' "
"[SoundsLike] = '" & strSoundEx & "'"

Hope that works
-Lee

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> After updating the LastName field on the Contacts form I would like to open a
report with similar last names. I am getting an "Enter Parameter Value" msgbox
and S530 (the SoundExBrowne value of the name that I am trying to do a where
condition on) is the message. If I enter S530 into the msgbox txtbox the report
is correct. If I don't enter any parameter of course the report is blank.
>
> The code trying to pull up the report is:
> On Error GoTo Proc_Err
> strSoundEx = SoundexBrowne(LastName)
> DoCmd.OpenReport "rContactSimilar", acViewPreview, , "[SoundsLike] = " &
strSoundEx
>
> The query that is working correctly for the report is:
> SELECT Contacts.LastName, Contacts.FirstName, Contacts.SpouseFN,
Contacts.SpouseLN, SoundexBrowne([LastName]) AS SoundsLike
> FROM Contacts
> ORDER BY Contacts.LastName, Contacts.FirstName;
>
> Thanks! I know this probably very simple. Have been working and working and
working ... to get this to work and my head won't get around it.
>
> Connie
>

------------------------------------

Yahoo! Groups Links