error - Access can't find '|' field in criteria expression 1

[mgargUWBC]

I'm having a problem with the following code.

Me.Project.RowSource = "SELECT Projects.ProjectID, Projects.ProjectName FROM WorkPlan INNER JOIN Projects ON WorkPlan.ProjectID = Projects.ProjectID " & _
                            "WHERE (((WorkPlan.ConsultantID) = " & [Me]![ConsultantID] & ") AND ((WorkPlan.ProgramName) = " & [Me]![SSID] & "))"

I get an error "Microsoft Office Access can't find the field '|' referred to in your expression" when my error trapping is on. If I remove all my error trapping it tells me this is run time error '2465'.

What am I doing wrong? I have a feeling it's going to be a "duh" once I get the answer!

Thanks in advance,
Michelle

 

[chris153]

Hi,

Looks OK to me ! try putting the sql statement in as a continuous string e.g. not over two lines and see if it still happens.

 

[mgargUWBC]

Chris153 -

Tried it and same error.

It worked correctly until I added the WorkPlan.ConsultantID) = " & [Me]![ConsultantID] part if that helps.

Michelle

 

[chris153]

Michelle,

What data format are the ConsultantID and ProgramName fields ?

if either of them are text fields you need single quotes round the variable e.g. WorkPlan.ProgramName = '" & [Me]![SSID] & "'"

If its not this I'm struggling !

 

[RoyVidar]

Hi!

Try removing the brackets around the Me keyword.

[tt]...ProgramName) = " & Me![SSID] & "))"[/tt]

Roy-Vidar

 

[mgargUWBC]

RoyVidar

BINGO!

That was it. I knew it was a "duh" when I posted but I was stumped. A case of looking at it so long I couldn't see the error.

A star for you!

Michelle