Invalid use of vertical bars

[Ebes1099]

I'm having a problem with an expression in a Query that was built by someone else.

I get an Invalid use of Vertical bars error when I try and run it.

The expression is,

Line1: IIf([CPN_PROD_DN_CAG_SUBCAG_LOC_RPT].[EXT_CLINIC_NO]>"!"|"Clinic # " & [CPN_PROD_DN_CAG_SUBCAG_LOC_RPT].[EXT_CLINIC_NO]|[CPN_PROD_DN_CAG_SUBCAG_LOC_RPT].[LOCATION_P_ADDR_LINE_TXT1]

I was thinking they should be commas, since they look like they are separating the arguments, but I get a syntax error if I just replace them with commas. Any help? Thanks.

 

[dhookom]

Does your expression include the final ")" after changing the | to commas?


Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

Yes that was in there. Must have just missed it when I copied and pasted into that post.

 

[dhookom]

What is the exact expression after using commas? Have you tried replacing the "!" with some other value?

It might help if you posted the entire SQL view.

Duane
Hook'D on Access
MS Access MVP

 

[Axworthy]

I have had problems when I tried to link two queries when the linking fields had a | in them as a separator. I had something like str(FieldNumeric1) & "|" & str(FieldNumeric2) as the join value. I never figured out why it would not work. I just changed them from a | to an X and the join worked.

 

[Ebes1099]

The entire expression after I replace the vertical bars with commas is. (I haven't tried replacing the "!" yet.

Line1: IIf([CPN_PROD_DN_CAG_SUBCAG_LOC_RPT].[EXT_CLINIC_NO]>"!","Clinic # " & [CPN_PROD_DN_CAG_SUBCAG_LOC_RPT].[EXT_CLINIC_NO],[CPN_PROD_DN_CAG_SUBCAG_LOC_RPT].[LOCATION_P_ADDR_LINE_TXT1])

I'm actually attempting to help a co-worker solve this and I don't have direct access to the back end database where this query is so it makes it even more difficult for me. I appreciate your help.

 

[dhookom]

This should work. Are you located in the US? Some other languages use a semi-colon rather than comma.

Make sure you test with something other than "!"

Duane
Hook'D on Access
MS Access MVP