How to call function 2

[jon92]

How do I call a function in an update query?
I am trying to capitalise names in a name field

I have found a function that will do this named:
Function proper(var As Variant) As Variant

what do I enter in the QBE grid
update to ?
criteria ?

Help needed please

 

[mpastore]

Paste in SQL like this:


update [mytable] set [mytable].[myfield] = proper([mytable].[myfield])


Your function should be in a module.

Sometimes its better to test a function via SQL before doing an update. Test doing something like this:


select [mytable].[myfield], proper([mytable].[myfield]) as name_proper from [mytable]



This way you can manually validate what changes would look like before actuall changing data.



Mike Pastore

Hats off to (Roy) Harper

 

[jon92]

Thanks for replying Mike

I have tried what you suggest and I receive the following error:
Ambiguous name in query expression proper([mytable].[myfield])'.

Jon

 

[mpastore]

John,

Can you send me the SQL from your query?

Mike Pastore

Hats off to (Roy) Harper

 

[jon92]

SQL:

update [tblbarvisit] set [tblbarvisit].[barid] = proper([tblbarvisit].[barid])

 

[mpastore]

I replicated your logic in a db as:

UPDATE tblBarVisit SET tblBarVisit.barID = proper(tblBarVisit.barID);

Are you sure your function is available? To check this:

CTL+G, this brings up code window.

Type in:

?proper("mike pastore&quot

You should see "Mike Pastore" (assuming your function is working.


Mike Pastore

Hats off to (Roy) Harper

 

[jon92]

It looks like my function isn't working

I received: Ambiguous name detected: proper

 

[mpastore]

You most likely have the function defined twice in your modules. Do a search for "function proper" in your whole project. Get rid of the duplicate function and try again.

Mike Pastore

Hats off to (Roy) Harper

 

[jon92]

Thanks Mike

You were spot on - query now working as I need.

Have a star !

Regards Jon