CODEWITHSUNDEEP
vbams-access
Shawn
Shawn

Reputation: 13

Dsum function not working with Text field

I've tried just about everything i can think of on why i would get this error, but i have had no luck. I wrote a similar code that references that same table with numerical values that works fine, but when searching for text it has problems. The error code says the missing operator lies here: [ExpendetureStore] = 'Lowe's

TotalCostTextBox = DSum("[ExpendetureCost]", "ProjectExpendetures", "[ExpendetureStore] = '" & Me.StoreNameCombo & "'")

Upvotes: 1

Views: 122

Answers (1)

June7
June7

Reputation: 21370

Lowe's has an apostrophe in its name. Access query engine is reading that apostrophe as a special character (text delimiter) in the compiled search string. If your data includes apostrophes, one way to deal with is to 'escape' the character - double it in the data with Replace() function. This forces Access to treat the character as normal text.

TotalCostTextBox = DSum("[ExpendetureCost]", "ProjectExpendetures", "[ExpendetureStore] = '" & Replace(Me.StoreNameCombo, "'", "''") & "'")

The same will happen with quote marks and are more challenging to deal with. Note the escaping of quote between quotes.
Replace("somevalue", """", """" & """")
Or may be easier to understand using Chr() function.
Replace("somevalue", Chr(34), Chr(34) & Chr(34))

Side note: Expendeture is a misspelling of Expenditure.

Upvotes: 2

Related Questions