Updating a query

[Guest]

I have a database that has been populated (no format or input mask, so the
hyphen was typed manually) with a Project Number field as: ###-####. I'd
like to change the existing records to read as ####-####. I'll enter an
input mask for the new records. Please advise as to how to change the
existing data.

 

[Guest]

Use the following as your update value for modifying the project number, just
replace your field name where you see [ProjectID]. I would back up your
table before running the update query.

Left(Replace([ProjectID],"-","",1),3) & "-" &
Right(Replace([ProjectID],"-","",1),4)

 

[Guest]

Karen,

Where does the data for the new digit come from and where do you want to
place it? Are you adding a leading zero, for example, or moving the hyphen
to the right and adding a suffix? Will it be the same or variable?

For a leading zero, you could do an update query that changes the field to:

"0" & [YourField]

For a trailing constant suffix, say of "A" you could use:

Left([YourField],3) & Mid([YourField],5,1) & "-" & Right([YourField],3)

For some variable suffix or prefix (or some other arrangement) that depends
on the value of the field, you could write a custom function to return the
correct value:

Public Function NewProjectNumber(strCurrentNumber as String) as String
Select Case strCurrentNumber
Case (Some valuelist)
NewProjectNumber = SomeExpresssion
Case (Some other valuelist)
NewProjectNumber = SomeOtherExpression
End Select
End Function

Then your update query would set the value to:

=NewProjectNumber([YourField])

Hope that helps.
Sprinks

 

[Guest]

Thanks Sprinks. I wanted a leading zero so you advice worked perfectly!
--
Thanks, Karen

Karen,

Where does the data for the new digit come from and where do you want to
place it? Are you adding a leading zero, for example, or moving the hyphen
to the right and adding a suffix? Will it be the same or variable?

For a leading zero, you could do an update query that changes the field to:

"0" & [YourField]

For a trailing constant suffix, say of "A" you could use:

Left([YourField],3) & Mid([YourField],5,1) & "-" & Right([YourField],3)

For some variable suffix or prefix (or some other arrangement) that depends
on the value of the field, you could write a custom function to return the
correct value:

Public Function NewProjectNumber(strCurrentNumber as String) as String
Select Case strCurrentNumber
Case (Some valuelist)
NewProjectNumber = SomeExpresssion
Case (Some other valuelist)
NewProjectNumber = SomeOtherExpression
End Select
End Function

Then your update query would set the value to:

=NewProjectNumber([YourField])

Hope that helps.
Sprinks

I have a database that has been populated (no format or input mask, so the
hyphen was typed manually) with a Project Number field as: ###-####. I'd
like to change the existing records to read as ####-####. I'll enter an
input mask for the new records. Please advise as to how to change the
existing data.