conditionally parse a numeric field

[Takeadoe]

I've got a favor to ask -

Consider the following numeric field:

511
6805
3205
403

I need to make 2 new numeric fields from this variable, call it CS
(short for Check Station)

The first 2 numbers (even when there are only 3 digits - zeros are
missing) represent the County and the last 2 the location within the
county. County ranges from 1-88, location from say 1 to 20.

How would I create 2 new numeric fields with the following conditions:

if CS < 1000 then county=the first digit and location=the next 2

else

county=first 2 and location = next 2

Any help on this would really be appreciated.

Mike

[AP]

Re: conditionally parse a numeric field

Hello
Try

First field

iif([fieldname]<1000,left([fieldname],1),left(fieldn ame],2))

Second Field

Right([fieldname],2)


AP

https://www.megacrosstab.com

Takeadoe wrote:

I've got a favor to ask -
>
Consider the following numeric field:
>
511
6805
3205
403
>
I need to make 2 new numeric fields from this variable, call it CS
(short for Check Station)
>
The first 2 numbers (even when there are only 3 digits - zeros are
missing) represent the County and the last 2 the location within the
county. County ranges from 1-88, location from say 1 to 20.
>
How would I create 2 new numeric fields with the following conditions:
>
if CS < 1000 then county=the first digit and location=the next 2
>
else
>
county=first 2 and location = next 2
>
Any help on this would really be appreciated.
>
Mike

[Takeadoe]

Re: conditionally parse a numeric field

Like a charm! Thank you very much!
AP wrote:

Hello
Try
>
First field
>
iif([fieldname]<1000,left([fieldname],1),left(fieldn ame],2))
>
Second Field
>
Right([fieldname],2)
>
>
AP

https://www.megacrosstab.com

>
Takeadoe wrote:

I've got a favor to ask -

Consider the following numeric field:

511
6805
3205
403

I need to make 2 new numeric fields from this variable, call it CS
(short for Check Station)

The first 2 numbers (even when there are only 3 digits - zeros are
missing) represent the County and the last 2 the location within the
county. County ranges from 1-88, location from say 1 to 20.

How would I create 2 new numeric fields with the following conditions:

if CS < 1000 then county=the first digit and location=the next 2

else

county=first 2 and location = next 2

Any help on this would really be appreciated.

Mike

[Takeadoe]

Re: conditionally parse a numeric field

Please forgive me for being such a bother, but I barely know Access -
the code works great and I tried it in a select query. But, if I want
these 2 variables to be permanent, my thought was I needed to use an
Update Query. When I try and run it, I keep getting the message, "must
select at least one destination field." Can you please advise?

Mike
AP wrote:

Hello
Try
>
First field
>
iif([fieldname]<1000,left([fieldname],1),left(fieldn ame],2))
>
Second Field
>
Right([fieldname],2)
>
>
AP

https://www.megacrosstab.com

>
Takeadoe wrote:

I've got a favor to ask -

Consider the following numeric field:

511
6805
3205
403

I need to make 2 new numeric fields from this variable, call it CS
(short for Check Station)

The first 2 numbers (even when there are only 3 digits - zeros are
missing) represent the County and the last 2 the location within the
county. County ranges from 1-88, location from say 1 to 20.

How would I create 2 new numeric fields with the following conditions:

if CS < 1000 then county=the first digit and location=the next 2

else

county=first 2 and location = next 2

Any help on this would really be appreciated.

Mike

[AP]

Re: conditionally parse a numeric field

Try setting the following

The table added to the query should be the table you want to update

bring the two fields down (Im assuming you added these two fields to
the table) to the design grid
for field one, in the update to box type the first expression

for field two in the 'update to' box type the second expression

Hope this helps
AP

https://www.megacrosstab.com

Takeadoe wrote:

Please forgive me for being such a bother, but I barely know Access -
the code works great and I tried it in a select query. But, if I want
these 2 variables to be permanent, my thought was I needed to use an
Update Query. When I try and run it, I keep getting the message, "must
select at least one destination field." Can you please advise?
>
Mike
AP wrote:

Hello
Try

First field

iif([fieldname]<1000,left([fieldname],1),left(fieldn ame],2))

Second Field

Right([fieldname],2)


AP

https://www.megacrosstab.com

Takeadoe wrote:

I've got a favor to ask -
>
Consider the following numeric field:
>
511
6805
3205
403
>
I need to make 2 new numeric fields from this variable, call it CS
(short for Check Station)
>
The first 2 numbers (even when there are only 3 digits - zeros are
missing) represent the County and the last 2 the location within the
county. County ranges from 1-88, location from say 1 to 20.
>
How would I create 2 new numeric fields with the following conditions:
>
if CS < 1000 then county=the first digit and location=the next 2
>
else
>
county=first 2 and location = next 2
>
Any help on this would really be appreciated.
>
Mike

[Takeadoe]

Re: conditionally parse a numeric field

Perfect! Thank you very much!
AP wrote:

Try setting the following
>
The table added to the query should be the table you want to update
>
bring the two fields down (Im assuming you added these two fields to
the table) to the design grid
for field one, in the update to box type the first expression
>
for field two in the 'update to' box type the second expression
>
Hope this helps
AP

https://www.megacrosstab.com

>
>
>
Takeadoe wrote:

Please forgive me for being such a bother, but I barely know Access -
the code works great and I tried it in a select query. But, if I want
these 2 variables to be permanent, my thought was I needed to use an
Update Query. When I try and run it, I keep getting the message, "must
select at least one destination field." Can you please advise?

Mike
AP wrote:

Hello
Try
>
First field
>
iif([fieldname]<1000,left([fieldname],1),left(fieldn ame],2))
>
Second Field
>
Right([fieldname],2)
>
>
AP

https://www.megacrosstab.com

>
Takeadoe wrote:
I've got a favor to ask -

Consider the following numeric field:

511
6805
3205
403

I need to make 2 new numeric fields from this variable, call it CS
(short for Check Station)

The first 2 numbers (even when there are only 3 digits - zeros are
missing) represent the County and the last 2 the location within the
county. County ranges from 1-88, location from say 1 to 20.

How would I create 2 new numeric fields with the following conditions:

if CS < 1000 then county=the first digit and location=the next 2

else

county=first 2 and location = next 2

Any help on this would really be appreciated.

Mike