Help with update query in Access

sunny76 · January 13th, 2006, 01:18 AM

Hi,

I need help with update query function. I have a table with a field, "user_no", containing information which I need to manipulate. The info contains 5 characters, beginning with G. The last 3 characters are numbers which I wish to update to "***". As for the second character, it ranges from A-Z and I do not wish to make any changes to it.

For eg.
I have the following:

GA111
GS123
GK234

How do I fill in the "Update To" field in order to change the information to:

GA***
GS***
GK***

Pls help.

Thanks.

Bob Bedell · January 13th, 2006, 01:49 AM

Hello,

Paste the following function in a standard module:

Code:

Public Function f_ModifyData(strInput As String) As String
   Dim strCharacters As String
   Dim strOutput As String

   strCharacters = Left$(strInput, 2)
   strOutput = strCharacters & "***"

   f_ModifyData = strOutput
End Function

Paste the following SQL statement in your query designer in SQL View:

Code:

UPDATE tblData SET tblData.DataItem = f_ModifyData(DataItem);

Or in query design view:

Field: DataItem
Table: tblData
UpdateTo: f_ModifyData(DataItem)

HTH,

Bob

Field

SerranoG · January 13th, 2006, 08:51 AM

Just a clarification, Sunny. Do you actually want to turn the numbers LITERALLY into asterisks, e.g. 123 to ***, or do you really want to MASK the numbers so that they remain 123 underneath but only APPEAR *** to the eye?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

sunny76 · January 16th, 2006, 05:23 AM

Hi,

I want to literally turn them into ***.

Regards