Access adds square brackets automatically

Question

I have a complex formula in a report footer which basically sums data in the report.  I've got it to work for one product group, and I thought I could just copy the formula into word and find and replace with the next keyword and paste this back into access and it would work perfectly.  however, access seems to want to add square brackets and it won't let me fix it.

=IIf((Sum([Previous Tonnes]*IIf([descrip] Like “*Compounds”,1,0))=0) And (Sum([Current Tonnes]*IIf([descrip] Like “*Compounds”,1,0))>0),"100%",IIf(Sum([Tonnes Chg]*IIf([descrip] Like “*Compounds”,1,0))=0,"No Tonnes",Sum([Tonnes Chg]*IIf([descrip] Like “*Compounds”,1,0))/Sum([Previous Tonnes]*IIf([descrip] Like “*Compounds”,1,0))))

This is the formula that won't work, I started with Like "Fertiliser" and replaced it with Like "*Compounds" and when I paste it into access it turns it into this:-

=IIf((Sum([Previous Tonnes]*IIf([descrip] Like [“]*[Compounds”],1,0))=0) And (Sum([Current Tonnes]*IIf([descrip] Like [“]*[Compounds”],1,0))>0),"100%",IIf(Sum([Tonnes Chg]*IIf([descrip] Like [“]*[Compounds”],1,0))=0,"No Tonnes",Sum([Tonnes Chg]*IIf([descrip] Like [“]*[Compounds”],1,0))/Sum([Previous Tonnes]*IIf([descrip] Like [“]*[Compounds”],1,0))))

WHYYYYYYY??????!

Answer 1

Word turns the quotes into 'smart quotes'.  Turn off smart quotes in Word and it should be fine.  Because I use Word to draft my replies here I have the following macro, run from a button in the quick access toolbar in Word, for toggling smart quotes on and off:

Sub ToggleQuotes()

Dim strMessage As String

With Options

If .AutoFormatAsYouTypeReplaceQuotes = True Then

.AutoFormatAsYouTypeReplaceQuotes = False

strMessage = "Smart quotes are off."

Else

.AutoFormatAsYouTypeReplaceQuotes = True

strMessage = "Smart quotes are on."

End If

End With

MsgBox strMessage, vbOKOnly, "Smart Quotes"

End Sub

Answer 2

Thank-you SOOOOO much Ken - it was the smart quotes causing the problem!

I went into File - Options - Proofing - Auto Correct - Auto Format As You Type and unchecked the straight quotes with smart quotes box and works a treat now.

Thanks so much for your help!

Very happy!

Answer 3

I always love it when Ken responds, he's sooooo smart!  Thanks Ken!!!

Answer 4

Thanks for your prompt reply, but this does not work!

Answer 5

Open Notepad and try it... Word adds it's own twist which isn't translated correctly by Access.