replace function show stopper

[Kal]

Hi everyone,

I am having a very strange problem with the replace() function. I am
trying to run the following select statement from within a vba module
and am getting a 'data type mismatch error in criteria expression'

SELECT ThreeGProcessChangeRequest.[Issue ID],
ThreeGProcessChangeRequest.[Title] " & _
"FROM ThreeGProcessChangeRequest WHERE ThreeGProcessChangeRequest.
[Status]<>'Closed' And ThreeGProcessChangeRequest.[Status]<>'Pending
Implementation' AND Replace(ThreeGProcessChangeRequest.[Assigned
To],',','') ='" & UserName & "' ORDER BY [Issue ID];"


UserName is irrelevant because even when I hardcode a name in this
area I get the error. But if I remove the replace function the error
goes away...I can do no comparison right now if I can't get this to
work.

Thank you,

SR

 

[Douglas J. Steele]

Replace needs double-quotes, not single quotes.

Try using

Replace(ThreeGProcessChangeRequest.[Assigned To],"","","""")

(that's two double quotes on either side of the comma as the 2nd parameter,
then four double quotes as the 3rd parameter)