number problem?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in my database which contains 6 numbers ex: 123456. I want to
break this number down, add another field and have one field 123 and the
other field 456. Please help.
 
Hi
I've just done it like this. The table is called Table1 and the field is no
In your query, the first field should be:
Expr1: Int([Table1]![no]/1000)
which will return 123, and the second field is:
Expr2: [Table1]![no]-([Expr1]*1000)
which will return 456
 
rob said:
I have a field in my database which contains 6 numbers ex: 123456. I want
to
break this number down, add another field and have one field 123 and the
other field 456. Please help.
Click to expand...


Add two fields to your table. Use the Left and Right functions in an update
query, to update the values in your table.

Left(YourField,3) and Right(YourField,3)
 
Hi Joan, I misrepresented my question to you. My field name is retpro which
contains 6 digit numbers which i want broken down. when I used your
suggestion it populated the fields with ret for my 164 records. I need to
breakdown the records in 123 and 456. These numbers vary in every record.
Sorry for the mishap.
 
Hi Joan, I misrepresented my question to you. My field name is retpro which
contains 6 digit numbers which i want broken down. when I used your
suggestion it populated the fields with ret for my 164 records. I need to
breakdown the records in 123 and 456. These numbers vary in every record.
Sorry for the mishap.
Click to expand...

It would have been helpful had you included the actual code of your
update query. You did add the new fields to your table and write an
Update query as Joan suggested, didn't you?
 
rob said:
Hi Joan, I misrepresented my question to you. My field name is retpro
which
contains 6 digit numbers which i want broken down. when I used your
suggestion it populated the fields with ret for my 164 records. I need to
breakdown the records in 123 and 456. These numbers vary in every record.
Sorry for the mishap.
Click to expand...


No I think I have it right. Post the SQL of the query you used (View, SQL
View), as Fred suggests
 
ok here is want I tried ex: field retpro includes records such as 123456,
345678, 234567 and so on. I want them broken down in 2 fields such as 123 and
345 etc. I created a blank field called two and used an update query to
update such as in the field section I choose two and then update to
left(two,3) and i get two in all fields. Please tell me what i'm doing wrong.
 
ok here is want I tried ex: field retpro includes records such as 123456,
345678, 234567 and so on. I want them broken down in 2 fields such as 123 and
345 etc. I created a blank field called two and used an update query to
update such as in the field section I choose two and then update to
left(two,3) and i get two in all fields. Please tell me what i'm doing wrong.

:
** snipped**
Click to expand...

I don't know what you did wrong.
You still haven't posted the Update query SQL.
Don't tell us what you did... show us --- by Copying the query SQL and
Pasting it into a reply message.

Further, in your original post you wanted the field [retpro] (123456)
broken down to 123 and 456, in this message you want 123456 broken
down into 123 and 345. You also wrote that you added a new field named
[two] to place the new data in (123), but you are using "left(two,3)"
to do what? [Two] is a new field. It has no data.
And where do you want to have the second 3 characters (456) placed?
Or should the second 3 characters somehow be 345?

Perhaps you should re-write your question. Write it as though you are
trying to explain it to someone who has no idea what you are trying to
do. Show us with an example, exactly what the finished data will look
like. Use actual table and field names.
And don't forget my first paragraph. Copy and Paste and post your SQL.
 
UPDATE Table1 SET Table1.[second] = Left([retpro],3);
It works now. Thank you very much, left out brackets
 
here is the sql UPDATE Table1 SET Table1.second = Left("retpro",3);
Click to expand...

That will indeed store the text string "ret" in every record: you're
telling it that you want the leftmost three characters of the literal
text string "retpro".

To tell it you want the left three characters of the *value in the
field named retpro*, use square brackets to tell Access it's a
Fieldname:

UPDATE Table1
SET Table1.Second = Left([retpro], 3), Table1.First = Right([retpro],
3)

If [retpro] is a Number field rather than a Text field, this will
cause problems if you have any five-digit numbers (or, equivalently,
leading zeros in the number). Andy's solution will avoid this problem.


John W. Vinson[MVP]
 
rob said:
here is the sql UPDATE Table1 SET Table1.second = Left("retpro",3);
Click to expand...

That will return ret because you are asking it to return the left 3 of a
string. Put square brackets around the field name.
Left([retpro],3)
 
Back
Top