DMax To Create Unique ID
Dec 22, 2012
I have a form with a combobox where I select the Carrier witch then populates a multiselect listbox with all the record from that carrier in the specified date. When I select them and click save I want all the record ID(ConID) saved under the same ID(manifest ID) ie,
1 223
1 225
2 344
2 4444
Ect
It saves it in a table called Manifest With the fields Manifest ID and CONID which is a lookup field to a different table. Here is my current code for the on click command
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Manifest", dbOpenDynaset)
[Code] ....
Now I've done some searching and I thin dmax is what I want to use.. But I am not sure how to make it work...
View Replies
ADVERTISEMENT
Jan 17, 2007
Hello i want to create a tracking number based on information entered into a form.
So i have my main table called "MainData" and this is in the backend and linked through to the front end where my form is.
I then have a query which is called "calllog" which has a field called "Call Number" inwhich i placed the following
=Year([Date]) & Left([CustomerName],3) & Right([Date Created],2)
This then gives me a unique number. Once i have entered a job in it will generate a number. Now this part works fine but it does not update the Maindata. How can i get the this to transfer the data to the maindata table???
View 6 Replies
View Related
Aug 22, 2006
Hi All.
Is there a way to index and create a unique identifier using a combination of fields (eg. User# + Provider + Date)?
Thanks.
View 1 Replies
View Related
Nov 12, 2012
I have a table as followed;
EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
Department:FK
What I want to do is create a new field that automatically populates based on what is entered into the above fields.
Essentially creating a single field with unique data generated by multiple fields.
For Example if I entered the following informaiton:
EmployeeID: 1 (AutoNumber)
FirstName: John
MInitial: P
LastName: Doe
Company: FederalGov
Department:Test
The new field can be generated as followed: JohnPDoeFederalgovTest
What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.
View 5 Replies
View Related
Apr 4, 2013
So I have this field in a form/table called "CustomerID". This is to be an ID number for our customers that includes the first four letters of their last name (LastName field) followed by four digits.
This ID is created when a button is pressed in the form. After that, code needs to grab the first four letters of the last name (LastName field in the form and table) and then go to the Contacts table and see if there is already a CustomerID that starts with those four letters. If there is not, then the CustomerID will be those four letters followed by "0001". If there is, it needs to be those four letter followed by the next number sequence that still does not exist for those four letters.
For example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns negative then
CustomerID assigned = zabo0001
Another example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns zabo0001 and zabo002 then
CustomerID assigned = zabo0003
View 7 Replies
View Related
Nov 14, 2013
How i would best combine values in a table to produce a 'primary key id number.'
For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233
I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.
View 14 Replies
View Related
Apr 12, 2013
I am trying to create an automatic unique 'number' (actually text) in a form. Here is an example of the format...
1456.R1
1456 is the project number
R stands for revision
1 is the first revision
So, in this database there could be 1456.R2 etc. but there could also be other project numbers, say 2323.R1, 2323. Looking for expression I need to enter to have Access look up the last revision for a specific project and then add 1 to it?
View 10 Replies
View Related
Oct 6, 2012
if I got a 4 x 4 matrix table - 4 rows and 4 columns - MS Access 2007/2010 the values should exist as below with no repetition of any number in any of the cells.
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
a number should not repeat in any of the cells.I set a primary key on cloumn 1 and defined unique on all the fileds but that doesn't work since 1 columns does not know what the other cell contains and no relationship exists.
View 6 Replies
View Related
Mar 6, 2014
I am trying to take a table that looks like this:
Customer Name
Fruit
Customer A
Apples
Customer A
Bananas
Customer B
Pears
Customer B
Apples
Customer C
Pears
And create a table that looks like this:
Customer Name
Apples
Bananas
Pears
Customer A
Apples
Bananas
Customer B
Apples
Pears
Customer C
Pears
I think this is relatively simple but don't know the syntax to create this table.
View 2 Replies
View Related
Mar 22, 2015
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
View 3 Replies
View Related
Sep 20, 2013
I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.
Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c
View 5 Replies
View Related
Jul 23, 2015
Is there a way of merging 2 fields together to create an additional field
my database consists of 4 main tables (in order of relationships)
*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016
For example;
Account Reference: TEST
Site Number: 001
and the field i would like to have;
Site Reference: TEST/001
I would also like that when i add a new site to that account i will have TEST/002....
View 4 Replies
View Related
Jun 18, 2015
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
View 2 Replies
View Related
Sep 15, 2006
Thanks for all the help to date.
Problem trying to get a dmax field for an invoice no in a query - am doing something wrong current attempt below won't work
Expr1 :dmax([Invoice no],[Table name]![Invoice no])
what am I missing - do I need something on the end when I get this to work I will use it to add 1 for the final query prior to print
View 4 Replies
View Related
Dec 21, 2006
Okay...I know the basic syntax for Dmax (or at least I thought I did) but I cannot get this statement to do what I want!
In the <before update> event I tyoed this:
Dim RNum As Long
Me.txtRNumber = DMax("[Request_Num]", "test_request") + 1
Me.txtRnumber = RNum
txtRNumber is the field on the form
Request_Num is the field on the table
test_request is the table
yes...I know the name syntax is bad (it's an older db I'm using as a test for a newer one).
Obviously, I want to automatically increment the field value by 1 on every new record (an if newrecord clause is included). But I just can't get the stinkin thing to work!!!
View 12 Replies
View Related
Oct 3, 2006
I am having difficulty understanding under which circumstances I should be using Max() as opposed to DMax() functions. Both seem to do the same thing (find maximum value in a field), but DMax has additional arguments.
What I currently want sounds like neither of these, as I want to query the maximum of several values in various fields for a specific record, rather than the maximum of several records in a specific field. Is there a way of doing this otherwise than by nesting IIF functions several deep?
I keep running into "Microsoft Office Access has encountered an error and needs to close" error messages, and am coming around to the view that I may need a UDF. I have never done one in Access before (but did some time ago in Excel).
Currently I have fields in various tables:
T_Clients.Commencement
T_Clients.Cessation
T_Tasks.StartDate
T_Tasks.EndDate
There is a one (T_Clients.ID_Clients) to many (T_Tasks.ID_Clients) relationship
T_Clients.ID_Clients and T_Tasks_ID_Tasks are primay keys.
In "pseudocode" my query, probably via a UDF, needs to return:
Min(Max(StartDate,Commencement)+12months,Min(EndDa te, Max(Cessation,StartDate))+9months
Commencement and Cessation may contain Null values, which are then to be treated as #1900-01-01# for the purposes of any comparisons.
My first attempt at this was to create several queries, each based on the previous query. Inefficient, perhaps, but I could trace the logic. I started by creating queries that substituted #1900-01-01# for null values in the Commencement and Cessation fields.
However when I got about 3 queries deep I ran into the "Access has encountered an error and needs to close" error, and no amount of playing around with it stopped that.
I don't know if relevant but I don't *really* have a T_Tasks.StartDate field. I have a query that calculates the start date from the previous EndDate, but I did not want to complicate the problem specs. That query seems to work ok. If I get a solution to the above I hope to jigger it to fit.
View 3 Replies
View Related
Jul 1, 2006
I've searched the forum and can't quite find what I need so I'm asking for further guidance form all you Access masters out there!
I have a field 'Time' in the format of 'dd/mm/yyyy hh:mm' which has a default value of =DMax("Time ","[Monitoring]")+1.
This increments 1 on the 'dd' bit when each new record is created, however what I want is for the hour (hh) bit to increment 1 on each new record.
Not sure if I'm being niave but I thought it was some sort of formatting issue, so I've dabbled with a few but to little effect unfortunately.
View 6 Replies
View Related
Oct 13, 2004
I want to create fields that are in the form IC0000001, IC0000002, etc. I can do the increment in the numbers by using the dmax function but how do I add the alphabets before it? How can I start with 0000001 instead of 1? Any help would be greatly appreciated, thanks so much!
Swee
View 3 Replies
View Related
Jan 18, 2006
Hello,
I am using DMax() to return the highest date in a table, but really need it to return the second-highest...so, if I had:
14th Feb
10th Dec
1st Jan
I don't want it to return 10th Dec, I want it to return 14th Feb
Any help much appreciated. I am sure there was something like dmax()-1, but that just returns (in my example above) 9th Dec!
Thanks!
View 4 Replies
View Related
Dec 4, 2006
Hello peeps!
Just someone advice if I can!
I have a table that contains student details:
StudentNo
Surname
Forename
I have created a form to add students to this table but I need a button that will read the highest StudentNo and then add one, putting the new stundent number in the form in a new record?
How can I go about doing this? Can it be done using a macro or do I need to use VBA??? I have been told that I would need to use the DMax function but I dont know VBA.
Thanks in advance for all your help
View 4 Replies
View Related
Mar 24, 2008
Hi
I have a table with three key fields
table name is tblSampleDetails
Field names are
ClientSampleRef
BatchNumber
Visual
A Batch may have many samples of which some may be visuals
Currently I store a variety of values (some number, some text and some a combination of both) in ClientSampleRef but I would like to start on some samples creating the value for the ClientSampleRef
If the sample is a visual (this is indicated by a check box on the form giving a value of -1) I would like to make the ClientSampleRef = Visual 1 this would then increment within the same batch by one each time.
I have taken some code from Dev Ashish and Arvin Meyer and changed the field and table names but I am getting an error saying there is a missing operator
If Check42 = -1 Then
Me.ClientsSampleRef = "REC-" & Right(DMax("ClientSampleRef", "tblSampleDetails"), Len(DMax("ClientSampleRef", "tblSampleDetails")) - InStr(1, DMax("ClientSampleRef", "tblSampleDetails"), "-")) + 1
End If
I am just testing this code so I have created a record with a value of REC-1 in the field ClientSampleref
I have also created a test database and the code works within that upto a value of REC-10 then I get problems
any help or pointers greatly appreciated
Richard
View 1 Replies
View Related
Jul 13, 2005
HI All,
I'm using
Between DMax("DateFieldName","TableName")-10 And DMax("DateFieldName","TableName") expression
in query to find time interval 10 days prior the last record puting this into Date field in my query Criteria. It's working fine.
But I want to find time interval a Month and an Year prior the last record.
I'm thinking to use DateDiff func to calculate properly intervals.
Between DMax("DateFieladName","TableName")-DateDiff ("m",1-2-100,1-1-100) And DMax("DateFieldName","TableName") --> for a month
That function also work but I'm not sure if it calculate correct result
Any better ideas? Thanks!
:cool:
View 3 Replies
View Related
Jul 23, 2005
Hello Access friends,
Trying to have a sequential autonumber for the ScreenID with the DMax () function.
Please advise on what is wrong with the following :
=Nz(DMax("[ScreenID]","[Screenprep]","[ScreenID] = '" & [CarModel] & "-" & Left$([Category],1) & "'")+1,0)
Neither putting this code in control source or beforeupdate event of the form is not working.
I have looked around and from previous posts in the forum come up with this module. But it is not working either:
Public Function NewScreenID() As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ScreenID As String
Dim CarModel As String
Dim Category As String
On Error GoTo Err_Execute
Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("SELECT Max([Screenprep].[ScreenID]) AS MaxScreenID from [Screenprep];", dbOpenSnapshot)
If IsNull(rst!MaxScreenID) Then
NewScreenID = [CarModel] & "-" & Left$([Category], 1) & Format(1, "0000")
Else
NewScreenID = rst!MaxScreenID + 1
End If
rst.Close
Set rst = Nothing
Set db = Nothing
NewScreenID = ScreenID
Exit Function
Err_Execute:
'An error occurred, return blank string
NewScreenID = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."
End Function
In advance thank you for your time.Can someone please guide me on how to sort this out?
View 1 Replies
View Related
Jun 4, 2006
Hello gentlemen,
My main form contains a Entry_No (Text – Not duplicate) field. I used following code in the same form in a command button’s OnClick event to increment integer value of Entry_No to “Issue-1”, Issue-2”, “Issue-3” & so on.
Dim strmax As String
strmax = DMax("[Entry_No]", "T_Drug_Receiv_Head")
Me!Entry_No = "Issue-" & Right(strmax, _
Len(strmax) - _
InStr(1, strmax, "-")) + 1
No problem with above code. It works fine.
The record source of the main form initially was based on query which I removed later. I placed following code to display only the last record while form opens (on Open event). This is a try due to very large table and I don’t want my form / query to load all 90,000 records into the memory at one time that takes time.
I placed a unbound text box (TxtMaxt) to disiplay Entry_No field of last record of the table which is ok.
Txtmax = DMax("[Entry_No]", "T_Drug_Receiv_Head") ‘ This is OK.
Dim NSSQL As String
NSSQL = "Select * from T_Drug_Receiv_Head where [Entry_No] = Txtmax"
Me.Form.RecordSource = NSSQL
Me.Refresh
When I open the form, it asks me ‘Enter parameter value’ for ‘Issue’. When enter something then dialog closes and form appears with blank record except showing displaying Entry_No field of last record of the table in the Txtmax unbound text box.
When I removes code from on open event and selects query that was set before as record set, it works.
Where might have gone wrong?
With kind regards,
Ashfaque
View 12 Replies
View Related
Jul 24, 2013
I just wondering if there is any way to Dmax more than one field? I found something and tried it but it does not seem to work.
Text13 = 10
Text15 = 5
I expected to get a result of just 10 but didn't
Code:
Dim Junk As Variant
Junk = DMax("[Text13] & ', ' & [Text15]", "Test")
MsgBox Junk
View 5 Replies
View Related
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
