Access Update Query returns -1

[Chris Morag]

Hi, having difficulty with the following:



IIf([ARTIST]<>" UB40",StrConv([ARTIST],3),[ARTIST]) And IIf([ARTIST]<>" UFO",StrConv([ARTIST],3),[ARTIST])

I am trying to proper case names providing they are not one of a small number of exceptions ( 2 examples above ), which I would like to leave as is. I would be grateful for any workable solution.

[orange]

I think your statement will give a syntax error, or you should assign it to something.
I think because you did not assign the result to anything, it is giving -1 just indicating it resolved to TRUE

I did a test like this

Sub may26()
Dim artist As String
artist = " ub40" ' "billly"
Debug.Print IIf([artist] = " UB40" Or [artist] = " UFO", [artist], StrConv([artist], 3)) 'And IIf([artist] <> " UFO", StrConv([artist], 3), [artist])
End Sub

and it worked as expected giving

Billly
ub40

Debug.print IIf([artist] = " UB40" Or [artist] = " UFO", [artist], StrConv([artist], 3))

If you have more exceptions, you should do additional Or [artist] = "exception value"
for each.

[Chris Morag]

Used the OR option and dropped the AND statement as advised and worked a treat - many thanks.

I think your statement will give a syntax error, or you should assign it to something.
I think because you did not assign the result to anything, it is giving -1 just indicating it resolved to TRUE

I did a test like this


and it worked as expected giving



Debug.print IIf([artist] = " UB40" Or [artist] = " UFO", [artist], StrConv([artist], 3))

If you have more exceptions, you should do additional Or [artist] = "exception value"
for each.