470,596 Members | 1,574 Online

# Increment number based on concatenated fields... 4
Hi there,

I have a simple table that has four fields in it: Zone (which will store a three letter code such as ABC), Year (a four digit number field), Number (which is the problem field) and Label (which will be the concatenation of the previous three fields).

There will be a variety of three letter codes stored in the Zone field (11 to be precise) and the Year field will have a very wide variety of 4-digit codes. What I'd like to do (if possible) is to populate the number field based on the concatenation of the Zone and Year fields. For example, if the Zone is ABC and the Year 1987 and this is the first occurrence in the table of this combination, then the Number field should read 1. If there's another occurrence of ABC + 1987 down the road, then the Number field should increment to 2, and so on.

The Zone and Year fields will be entered via a form and I'd like the Number field to populate itself automatically.

Is there a VBA solution to this? I'm assuming it's too complicated for an expression...

Thank you,
Jo
May 9 '07 #1
3 2277 JConsulting
603 Expert 512MB
Hi there,

I have a simple table that has four fields in it: Zone (which will store a three letter code such as ABC), Year (a four digit number field), Number (which is the problem field) and Label (which will be the concatenation of the previous three fields).

There will be a variety of three letter codes stored in the Zone field (11 to be precise) and the Year field will have a very wide variety of 4-digit codes. What I'd like to do (if possible) is to populate the number field based on the concatenation of the Zone and Year fields. For example, if the Zone is ABC and the Year 1987 and this is the first occurrence in the table of this combination, then the Number field should read 1. If there's another occurrence of ABC + 1987 down the road, then the Number field should increment to 2, and so on.

The Zone and Year fields will be entered via a form and I'd like the Number field to populate itself automatically.

Is there a VBA solution to this? I'm assuming it's too complicated for an expression...

Thank you,
Jo
How about something like this in the after update of one of your fields...Sorry if I got the field names wrong..you get the idea

Expand|Select|Wrap|Line Numbers
1. if nz(dmax("number","yourtable","code ='" & me.code & "' and "year" = me.year), 0)=0 then
2. me.number = 1
3. else
4. me.number = dmax("number","yourtable","code ='" & me.code & "' and "year" = me.year) + 1
5. end if
6.
May 9 '07 #2
nfrjob
4 Wow! Smokin' fast response there...

With a few minor tweaks, that worked! Here's the code that worked for me:

Expand|Select|Wrap|Line Numbers
1.     If Nz(DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'"), 0) = 0 Then
2.         Me.Number = 1
3.     Else
4.         Me.Number = DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'") + 1
5.     End If
6.
Thank you for the help!
Cheers,
Jo
May 9 '07 #3
JConsulting
603 Expert 512MB
Wow! Smokin' fast response there...

With a few minor tweaks, that worked! Here's the code that worked for me:

Expand|Select|Wrap|Line Numbers
1.     If Nz(DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'"), 0) = 0 Then
2.         Me.Number = 1
3.     Else
4.         Me.Number = DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'") + 1
5.     End If
6.
Thank you for the help!
Cheers,
Jo
Anytime,
J
May 9 '07 #4

 2 posts views Thread by Tom | last post: by 2 posts views Thread by B Love | last post: by 3 posts views Thread by CSDunn | last post: by 7 posts views Thread by astro | last post: by 3 posts views Thread by George Ter-Saakov | last post: by 5 posts views Thread by theaybaras | last post: by reply views Thread by ewingate | last post: by 27 posts views Thread by 3acks | last post: by 12 posts views Thread by badvoc | last post: by 2 posts views Thread by davidbuttler | last post: by reply views Thread by CoderNitai | last post: by reply views Thread by lanliddd | last post: by 3 posts views Thread by kjhyder | last post: by reply views Thread by Domgrar | last post: by 1 post views Thread by markleo | last post: by 1 post views Thread by EverettMiller | last post: by 3 posts views Thread by Eliasendashaw | last post: by reply views Thread by DarlinRey | last post: by