Parse space in Access field

[ItNerd]

I have a table with one field that contains 2 spaces at the front and I need
to remove the spaces and then put a 0 as the first character. For instance
the field would look like ( 33805767038) and I need it to look like
(033805767038). I need to run a query between two tables and create a report.
Can anyone help. Thanks.

 

[John Spencer]

Use an expression like
"0" & MID([SOMEFIELD],3)

If all the fields start with two spaces.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ItNerd via AccessMonster.com]

Thanks can you tell me how to use this code, I have to convert multiple
records for the same field. I am a beginner to Access and so far only have
limited knowledge.
ItNerd

Use an expression like
"0" & MID([SOMEFIELD],3)

If all the fields start with two spaces.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table with one field that contains 2 spaces at the front and I need
to remove the spaces and then put a 0 as the first character. For instance
the field would look like ( 33805767038) and I need it to look like
(033805767038). I need to run a query between two tables and create a report.
Can anyone help. Thanks.

 

[PieterLinden via AccessMonster.com]

Thanks can you tell me how to use this code, I have to convert multiple
records for the same field. I am a beginner to Access and so far only have
limited knowledge.
ItNerd

Use an expression like
"0" & MID([SOMEFIELD],3)

[quoted text clipped - 11 lines]

An update query might be good place to put it...

 

[PieterLinden via AccessMonster.com]

Thanks can you tell me how to use this code, I have to convert multiple
records for the same field. I am a beginner to Access and so far only have

[quoted text clipped - 6 lines]
An update query might be good place to put it...

or you could just create the query and base the report on it... probably
safer that way...

 

[John Spencer]

If you want to do this permanently, you use an update query.

== Create a new query
== Add your table
== Put the field you want to change into the fields list
== set the criteria for under the field to
Like " *"
== Select Query: Update from the menu
== Set UPDATE to under the field to
"0" & MID([NameOfYourField],3)
== Select Query: Run from the menu

If you just want to display the value and not permanently change the data then
in the query enter in a field "cell" the expression
"0" & MID([NameOfYourField],3)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks can you tell me how to use this code, I have to convert multiple
records for the same field. I am a beginner to Access and so far only have
limited knowledge.
ItNerd

Use an expression like
"0" & MID([SOMEFIELD],3)

If all the fields start with two spaces.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table with one field that contains 2 spaces at the front and I need
to remove the spaces and then put a 0 as the first character. For instance
the field would look like ( 33805767038) and I need it to look like
(033805767038). I need to run a query between two tables and create a report.
Can anyone help. Thanks.

 

[ItNerd via AccessMonster.com]

Thanks I used the update query and it worked like a charm. I will have a
dozen or more tables every year to use this on so it will save me a lot of
time.
Learning more all the time.
ItNerd

If you want to do this permanently, you use an update query.

== Create a new query
== Add your table
== Put the field you want to change into the fields list
== set the criteria for under the field to
Like " *"
== Select Query: Update from the menu
== Set UPDATE to under the field to
"0" & MID([NameOfYourField],3)
== Select Query: Run from the menu

If you just want to display the value and not permanently change the data then
in the query enter in a field "cell" the expression
"0" & MID([NameOfYourField],3)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks can you tell me how to use this code, I have to convert multiple
records for the same field. I am a beginner to Access and so far only have

[quoted text clipped - 16 lines]