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  2822  
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 telling
me that "Number of query values and destination fields are not the
same."
If I add a value for the auto increment field to the SQL String the
data is entered into the table with no problems but obviously the auto
increment field now...
|
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 items.
The ordinary text field is: NewLongDescription
(This is a memo field that contains descriptions of dolls sold in an e-comm
site)
|
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 a spreadsheet might show, the
column names will actually be dynamic, based on data from a SQL Server 2000
database. The row data will also come from the same database. So in this
case, I will have a main report and a subreport. I've already tried...
|
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 mde, backend is mdb
-only occurs on the production server
-only occurs with mde verson - i've tested the problem with the mdb version
of the front-end and it loads/generates the document fine on the production
|
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 manner.
But at the same time if i want to use variable that could be changed in
another thread i must use volatile (to prevent optimization).
But then i can not use Interlocked.Increment.
So i do not see any benefits of having ...
| | |
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 from other fields. I have 12 fields for author names. lets say author1, author2, author3, ... author11, author12. I need a different text in the citation if 7 or more authors are entered into the above fields. If 1-6 authors are listed citation...
|
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
versioning behavior would be fine if only it would increment every
time I build the project. Is there a minimum number of lines of code
that must change for a build to increment it's version? I understood
that the default build and version...
|
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 largest value and add 1. The problem is that I need the unique ID to be based on the value of two other fields. So, in table of hundreds of records I want to look at only certain values to obtain the next available number. For example, if I have a...
|
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 adding a unique order number to a customer order form.
I have 4 stages to my order form.
Stage 1 involves the user selecting a link based on what they want. The link then sets 2 variables. $type and $fault based on the link they have chosen.
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
