Modifying field contents in Access field 
Author Message
 Modifying field contents in Access field

One way to do it is to create an append query and an update query.  Assuming
the field with the problem is field1:

Create the append query with all of your fields in it, except for Field1,
and with an additional calculated field:  Expr1: isnumeric(Field1).  Set the
filter criteria on this field to false.  Leave the "append to" line blank
for this field.  Add a second calculated field:  Expr2:
mid(field1,instr(1,field1,"and")+4).  Set the "append to" line to "field1"..

Running this append query should create the new records that you need, but
with field1 in the new records corrected to contain the second number in the
field.

Create an update query with all your fields in it, and with an additional
calculated field:  Expr1: isnumeric(Field1).  Set the filter criteria on
this field to false, and leave the "update to" line blank.  For Field1,
change the "update to" line to the following:  Val(Field1)

This update query will overwrite field1 in the records that have two numbers
in field1 with the left most number.

The filter field Expr1 in both of these queries should only display records
that are not numeric.  The field Expr2 in the first query uses the instr
function to determine where "and" is in the field, and uses the mid function
to get the characters to the right of "and".

In the update query, the function val is used to get the leftmost characters
that consitute a valid number.

The append query must be run first, or you will lose the criteria that
allows you to detemine which records need to be duplicated.

To complete the job, you need to add a new numeric field to hold your field1
data.  Run an update query to make your new field contain the field1 data.
Then you can delete your old field1.

If you like, you can test the expr1 field on a select query on your table to
verify that only the records that you want to process are displayed.  You
can also show that the proper calculations are done by making appropriate
modifications to the select query.

Regards,

John


Quote:
> Hi,

> Not sure if this is the correct list for this, apologies if not.

> I've imported an Excel sheet of server log data into Access, and one of
> the fields I need to be numerical, but it currently contains some text.
> The field contains study numbers, but some of the entries have the
> following format;

> "195 and 196"

> What I'd like is to eliminate the "and" and create two records for each
> of the two study numbers (with all the other fields the same). When I
> have nothing but numbers in that field I would change it to numerical.

> Can somebody give me some guidance on how I could loop through the field
> amending the entries where necessary using VBA?. TIA.

> Colin Goodier



Tue, 11 Mar 2003 03:00:00 GMT  
 Modifying field contents in Access field

One way to do it is to create an append query and an update query.  Assuming
the field with the problem is field1:

Create the append query with all of your fields in it, except for Field1,
and with an additional calculated field:  Expr1: isnumeric(Field1).  Set the
filter criteria on this field to false.  Leave the "append to" line blank
for this field.  Add a second calculated field:  Expr2:
mid(field1,instr(1,field1,"and")+4).  Set the "append to" line to "field1"..

Running this append query should create the new records that you need, but
with field1 in the new records corrected to contain the second number in the
field.

Create an update query with all your fields in it, and with an additional
calculated field:  Expr1: isnumeric(Field1).  Set the filter criteria on
this field to false, and leave the "update to" line blank.  For Field1,
change the "update to" line to the following:  Val(Field1)

This update query will overwrite field1 in the records that have two numbers
in field1 with the left most number.

The filter field Expr1 in both of these queries should only display records
that are not numeric.  The field Expr2 in the first query uses the instr
function to determine where "and" is in the field, and uses the mid function
to get the characters to the right of "and".

In the update query, the function val is used to get the leftmost characters
that consitute a valid number.

The append query must be run first, or you will lose the criteria that
allows you to detemine which records need to be duplicated.

To complete the job, you need to add a new numeric field to hold your field1
data.  Run an update query to make your new field contain the field1 data.
Then you can delete your old field1.

If you like, you can test the expr1 field on a select query on your table to
verify that only the records that you want to process are displayed.  You
can also show that the proper calculations are done by making appropriate
modifications to the select query.

Regards,

John


Quote:
> Hi,

> Not sure if this is the correct list for this, apologies if not.

> I've imported an Excel sheet of server log data into Access, and one of
> the fields I need to be numerical, but it currently contains some text.
> The field contains study numbers, but some of the entries have the
> following format;

> "195 and 196"

> What I'd like is to eliminate the "and" and create two records for each
> of the two study numbers (with all the other fields the same). When I
> have nothing but numbers in that field I would change it to numerical.

> Can somebody give me some guidance on how I could loop through the field
> amending the entries where necessary using VBA?. TIA.

> Colin Goodier



Tue, 11 Mar 2003 03:00:00 GMT  
 Modifying field contents in Access field

John,

Thanks for this. I eventually got round the problem in a very clumsy way, by
just manually editing the fields and appending a new record for each study
number, but that's not very satisfactory, so I'll certainly look at your
suggestion. Thanks again.

Colin

Quote:

> One way to do it is to create an append query and an update query.  Assuming
> the field with the problem is field1:

> Create the append query with all of your fields in it, except for Field1,
> and with an additional calculated field:  Expr1: isnumeric(Field1).  Set the
> filter criteria on this field to false.  Leave the "append to" line blank
> for this field.  Add a second calculated field:  Expr2:
> mid(field1,instr(1,field1,"and")+4).  Set the "append to" line to "field1"..

> Running this append query should create the new records that you need, but
> with field1 in the new records corrected to contain the second number in the
> field.

> Create an update query with all your fields in it, and with an additional
> calculated field:  Expr1: isnumeric(Field1).  Set the filter criteria on
> this field to false, and leave the "update to" line blank.  For Field1,
> change the "update to" line to the following:  Val(Field1)

> This update query will overwrite field1 in the records that have two numbers
> in field1 with the left most number.

> The filter field Expr1 in both of these queries should only display records
> that are not numeric.  The field Expr2 in the first query uses the instr
> function to determine where "and" is in the field, and uses the mid function
> to get the characters to the right of "and".

> In the update query, the function val is used to get the leftmost characters
> that consitute a valid number.

> The append query must be run first, or you will lose the criteria that
> allows you to detemine which records need to be duplicated.

> To complete the job, you need to add a new numeric field to hold your field1
> data.  Run an update query to make your new field contain the field1 data.
> Then you can delete your old field1.

> If you like, you can test the expr1 field on a select query on your table to
> verify that only the records that you want to process are displayed.  You
> can also show that the proper calculations are done by making appropriate
> modifications to the select query.

> Regards,

> John



> > Hi,

> > Not sure if this is the correct list for this, apologies if not.

> > I've imported an Excel sheet of server log data into Access, and one of
> > the fields I need to be numerical, but it currently contains some text.
> > The field contains study numbers, but some of the entries have the
> > following format;

> > "195 and 196"

> > What I'd like is to eliminate the "and" and create two records for each
> > of the two study numbers (with all the other fields the same). When I
> > have nothing but numbers in that field I would change it to numerical.

> > Can somebody give me some guidance on how I could loop through the field
> > amending the entries where necessary using VBA?. TIA.

> > Colin Goodier



Mon, 21 Apr 2003 19:44:32 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. How to modify the field length from an access-field in Ms-VB

2. How to modified existing field and add new field in existing table for Access database

3. The contents of fields on the reports grows outside limits of field width

4. Modify table field properties and record values in Access macro

5. Modify table field properties and record values in Access macro

6. Accessing dBase field contents through VB 6.0

7. Problem with reading Access memo field contents from adOpenForwardOnly recordset

8. Add Access Field contents to Collection

9. Save Picture Box contents to Pocket Access database field

10. Need help reading Outlook keyword fields into Access field

11. Newbie - Access 97 - Full Name Field into 2 or three different fields

12. HowTo get max field value from Access table field

 

 
Powered by phpBB® Forum Software