Counting first 3 numbers of a field "###A#####"

[rodvaN]

Hello there.. I have a field with lots of data with the format:
001A020001
001A020002 -001A150345
002B010001 - 002B230225

etc..
So I want to count just the first 3 numbers and see the total of 001 = 345
002 = 225
So in one field display the variable im counting and in another field the result.. all this from the same field.. is this possible?

 

[wazz]

left("001A020002 -001A150345",3) = 001
right("001A020002 -001A150345",3) = 345

 

[rodvaN]

Sorry I did not explian myself very good..
I got a field called "lots"
It stores this data:
005A010038 005A050247 001A010011 077A010012 255A080029 076A080165 234A040003 001A010018 005A050252 047A010006 004A070368 252A010001 260A010001 255A080034 017A020016 017A040101 017A070224 005A080416 017A090330 246A090506 260A010002 017A090337 001A010005 248A010062 255B060023 248B060522 248C080097 005A090502 077A010013 021A180001 248C080098 249A100098 249B050132 249B050133 249B080009 001A010008 001A010021 002A010019 001A010022 001A010023 249A060179
So I want to count how many repeated FIRST 3 NUMBERS ARE.. for example in the few data I just pasted are 7 "001" and 5 "249"
So how can I do this count on the first 3 numbers and display the variable in another field?

 

[Access Hero]

Are these numbers in a field on a table or in a file? Where do they come from?

If in a table:

select y.x, count(*) from
(
select left(columnname,3) as x
from mytablename
) as y
group by y.x

is one of many ways to accomplish this if the data is in a table.

Otherwise, you'll need to iterate through each entry and keep a count in an array or some other device.

 

[rodvaN]

Excuse me, where I use that code?
Its SQL, so how can I introduce it on a Query, or on a Listbox?
In the data properties of the listbox?
if the table name is "BASE" and the fieldname is "Lote"
How it would be?
thanks

 

[Access Hero]

Excuse me, where I use that code?
Its SQL, so how can I introduce it on a Query, or on a Listbox?
In the data properties of the listbox?
if the table name is "BASE" and the fieldname is "Lote"
How it would be?
thanks

select y.Lote, count(*)
from
(select left(Lote,3) as Lote
from BASE) as y
group by y.Lote

Put it into the Row Source property (after careful testing & debugging). You might want to test it out as a query first (just paste it into the SQL window of a new query) and then use that query in the List Box Source property.