user defined functions in update queries

[spizotfl]

hi, i was wondering about using user-defined functions in an update query. specifically: i have a table that has a field that needs to have the 3rd character as a hyphen. most records already have that hyphen, but i need to fix the others.
anywho, i have two functions: one that checks to see if a hyphen is included and returns a boolean. the other function adds a hyphen.
i have an update query like this:

UPDATE tblDemographics SET tblDemographics.ProvID = InsertHyphen([tblDemographics.ProvID]) 
WHERE (ThirdChar([tblDemographics.ProvID])) = True;

but i keep getting "The search key was not found in any record"
if i run this as a select query, it returns the results exactly as i want.
any thoughts?
oh, i was also wondering if, since the ProvID field is text, i would need some sort of quoting around the InsertHyphen function.

Thanks

ps i know that this isn't a good problem to have, and i am well aware of the design issues that have led to this in the first place. it will be corrected as time permits, but if we can piece together a quickie patch, it will help quite a bit.

 

[Golom]

This

[tblDemographics.ProvID]

should be this

[tblDemographics].[ProvID]

in both places it occurs.

 

[spizotfl]

i just tried it, but i am still getting the search key not found message.

 

[Golom]

Just to isolate the problem, try this

UPDATE tblDemographics 
SET tblDemographics.ProvID = InsertHyphen(tblDemographics.ProvID) 

WHERE Mid(tblDemographics.ProvID,3,1) <> "-"

You should also confirm that the field name is really "ProvID".

 

[spizotfl]

i tried that and get the same message. i double checked the table data, and it hasn't changed.
if i convert what you just suggested to a select query, it pulls up the right things, but it doesn't work as an update.

 

[Golom]

I don't recall that message being one I've seen before. I'm wondering if it's coming from the InsertHyphen routine. Could you give this a try

UPDATE tblDemographics 
SET ProvID = Left(ProvID,2) & "-" & Mid (ProvID,3)

WHERE Mid(ProvID,3,1) <> "-"

 

[spizotfl]

still getting that search key not found message. maybe you can't use a function in the where clause of an update query?

 

[spizotfl]

i just changed the where clause to the hard coded value and it didn't work, either.

 

[spizotfl]

ok, for an update, i shut everything down for the weekend, came back, and it worked without a fuss. don't know what was going on, but it now seems to do the trick just right.