I wrote this query but it will not execute in in B1 when I use the [%0] variable, but if I replace with text it works. What needs to be done to fix this?
SELECT distinct bp.[CardCode], bp.[CardName],
bp.[MailAddres], bp.[MailZipCod], bp.[Phone1], bp.[Phone2], bp.[Fax],
bp.[CntctPrsn], bp.[MailCity], bp.[MailCountr],
bp.[State2],
invoice.DocNum AS 'Last Order Num',invoice.DocTotal AS 'Last Total',
invoice.DocDate AS 'Last Order Date'
FROM (SELECT CardCode,
MAX(oinv.DocEntry) AS 'DocEntry'
FROM OINV
GROUP BY CardCode) AS latesti
LEFT JOIN OINV AS invoice ON (invoice.DocEntry = latesti.DocEntry)
INNER JOIN OCRD AS bp ON invoice.cardcode = bp.cardcode
WHERE (bp.[MailCITY] liKE '%%[%0]%%' )
and bp.[FROZENFOR] = 'N'
UNION ALL
SELECT distinct bp2.[CardCode], bp2.[CardName],
bp2.[MailAddres], bp2.[MailZipCod], bp2.[Phone1], bp2.[Phone2], bp2.[Fax],
bp2.[CntctPrsn], bp2.[MailCity], bp2.[MailCountr],
bp2.[State2],
transfer.DocNum AS 'Last Order Num',transfer.DocTotal AS 'Last Total',
transfer.DocDate AS 'Last Order Date'
FROM (SELECT CardCode,
MAX(owtr.DocEntry) AS 'DocEntry'
FROM OWTR
GROUP BY CardCode) AS latesttrans
LEFT JOIN OWTR AS transfer ON (transfer.DocEntry = latesttrans.DocEntry)
INNER JOIN OCRD AS bp2 ON transfer.cardcode = bp2.cardcode
WHERE (bp2.[MailCITY] liKE '%%[%0]%%' )
and bp2.[FROZENFOR] = 'N'
Order by CardCode