Hi there,
I'm an advanced beginner, so please bear with me...
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...
Any advice would be appreciated!
Thank you,
Jo
3 2736
Hi there,
I'm an advanced beginner, so please bear with me...
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...
Any advice would be appreciated!
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 -
if nz(dmax("number","yourtable","code ='" & me.code & "' and "year" = me.year), 0)=0 then
-
me.number = 1
-
else
-
me.number = dmax("number","yourtable","code ='" & me.code & "' and "year" = me.year) + 1
-
end if
-
Wow! Smokin' fast response there...
With a few minor tweaks, that worked! Here's the code that worked for me: -
If Nz(DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'"), 0) = 0 Then
-
Me.Number = 1
-
Else
-
Me.Number = DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'") + 1
-
End If
-
Thank you for the help!
Cheers,
Jo
Wow! Smokin' fast response there...
With a few minor tweaks, that worked! Here's the code that worked for me: -
If Nz(DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'"), 0) = 0 Then
-
Me.Number = 1
-
Else
-
Me.Number = DMax("Number", "map", "left(Label,7)='" & Me.Zone & Me.Year & "'") + 1
-
End If
-
Thank you for the help!
Cheers,
Jo
Anytime,
J
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tom |
last post by:
I am trying to store information into a table that has an auto
increment field. There is currently no data in the table. Using the
code below I cannot insert data into the table. I get an error...
|
by: B Love |
last post by:
Hello Group,
I have 2 text fields that I would like to concatenate for use in a table.
One field is an ordinary text box. The other is a simple combo box which I
use to select one of about ten...
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what...
|
by: astro |
last post by:
Anyone have suggestions on where to troubleshoot this error?
Background:
-Access 2k v. 9.0.6926 sp3
- front and backend on production server (wiindows 2k)
-accessed via Citrix
-front-end is...
|
by: George Ter-Saakov |
last post by:
What is the purpose of having Interlocked.Increment if it does not work with
variable declared as volatile.
Here is my problem,
Interlocked.Increment increments the variable in thread safe...
|
by: theaybaras |
last post by:
Hi,
I'm still getting a feel for some things in Access. What I have is a database of scientific articles. I have an unbound memo field in which I want to have the APA style citation concatenated...
|
by: ewingate |
last post by:
I seem to be having an issue with version incrementation. When
building my app, it will not consistently increment the version
number. I currently have the attribute set at "1.2.*" and the default...
|
by: 3acks |
last post by:
I've read a lot of articles and forums trying to figure this one and have yet to come up with a solution I can get to work. I need to create a unique id. I can do this using Dmax to find the...
|
by: badvoc |
last post by:
Hi,
I have had some good fortune on this site so I am back and I must iterate I am a beginer.
I am having some problems getting to grips with the right technique to manage variables and...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |