Another DMax Question (yes I've Used Your Search Facility)
May 14, 2006
Hello,
I have inherited a database that already has heaps of records in it. One of the forms which is used constantly, has a "BookNumber field" (taken from an underlying table called "Participants". At the moment, this number is entered by hand, but it really could be done automatically increasing by 1 each time a new record is created.
My questions are these:
1. If the BookNumber field in the Participants table is a text field, can I use DMax on it, or does it need to be a number field?
2. If it does need to be a number field, will it cause problems to the existing records if I change the text field to a number field in the table design?
3. Where do I put the DMax statement - in the table or in the form? I am assuming that I put it in the form, but point it to the table is that right?
I know you have answered heaps of questions about DMax before (I think I have read just about every one), but I'm still having trouble figuring this out. I tried putting a text box in the form that had an expression that read: =DMax("BookNumber", "Participants")+1 as it's data, but it just came back with an error message.
Please help...
Thanks,
QuietAchiever
View Replies
ADVERTISEMENT
Jun 21, 2006
please excuse the ignorance, im am i novice in the process on learning this.
i have a project that requires to create a customer service log of all the queries that come in.
i would like to create a form that would enable me to search for the following
query number
date
account name
account reference
then i would like it to list all the return so you can select the entry to open it or either go to that record.
any help is greatly greatly appriciated.
thank you for your time
View 5 Replies
View Related
Feb 28, 2008
I’m trying to provide the users of my database with a text search facility.
The database deals with documents, and I’d like the user to be able to search by title of document, with partial text matches. E.g. a title might be ‘Health and safety at work act’ - so searching by Health and/or safety gives this document as a result.
The table is called ‘tblDocTitle’ with the field ‘Title’ containing just that.
Ideally the search facility would be some sort of text box in which the text, and then a button to initiate that search.
Hope you can help
View 4 Replies
View Related
Mar 20, 2013
I have a simple query like below.
Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;
It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.
The error I get is a syntax error the SELECT statements.
View 3 Replies
View Related
Apr 18, 2015
I have been writing a database for a medical facility. Currently, I have a box to check if a patient in the database is admitted. What I would like to do is create two radio buttons: one to admit and one to discharge.
When the patient is discharged, I want the data in some of the fields on the form(s) to clear, but not all the fields.
I realize the I will need some scripting here.
As an example: the fields for Unit and Room number will clear upon discharge. As well as fields for diagnosis and informed consent signature located on another form within the same table.
View 3 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
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 4 Replies
View Related
Mar 7, 2005
I'm reading lots of threads about various problems with autonumber, and I encountered a corruption in a very beginning database myself, using the autonumbered field as a sequential file number, my primary key. So, my question is.....how is the very most simple, NOVICE, way to use DMax (step by step on what to write and where to put it) to create a sequential file number, starting from 00001 and ad infinitum until I go out of business :)
I simply want a field called "file number" that will effectively auto-number, without the dangers of autonumbering crashes etc.
Thanks for your suggestions!
View 3 Replies
View Related
Jun 27, 2007
I have data in a table
I am extracting using an append Query, I wish to assign a number from the original table +1 to the records pulled by the append query.
I am very new to this and have the expression below, however the result is #error in created field CertifNumber
The data I am looking for is in table Sample Details and the Field is CertNo
CertifNumber: DMax([Sample Details]![CertNo],"Sample Details","[Sample Details]![CertNo]+1")
If somebody could help me with this expression I would be very grateful.
Thanks
Richard
View 3 Replies
View Related
Aug 8, 2013
after I insert a new record using INSERT INTO and then use DMax() (in a private sub) to look up the new record. About half the time DMax() pulls the new record based on the primary key field (AutoNumber) just fine. However, half the time it pulls the max record prior to the new record being inserted. I.e. record 1001 was added and DMax() pulls record 1000. I'm assuming that my issue has something to do with the timing of when the record is writen/saved in the table. Is there a simple method of refreshing the table that I can use prior to using DMax()?
View 2 Replies
View Related
Jun 28, 2014
My code is supposed to advance the number of times the current user who is logging in and advance by one. It is NOT doing this by the Current User? It is advancing the count from the greatest number by one?
Need to modify SQL to allow for txtlogincnt ? ? ?
txtUserID is the Users Login Name.
LoginCnt is the number of times the User has Logged in.
1 Other pieces of code that get's the Log Count per User logging in. This part works!
'Get Login Count.
Quote:
Me.txtlogincnt = DLookup("[LoginCnt]", "tblUserSecurity_Sec", "[userID]='" & Me.txtUserID.Value & "'")
Quote:
'Update Login Count
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblUserSecurity_Sec SET tblUserSecurity_Sec.LoginCnt = '" & Nz(DMax("LoginCnt", "tblUserSecurity_Sec") + 1) & "' " & _
"WHERE (((tblUserSecurity_Sec.userID)='" & Me.txtUserID.Value & "'));"
DoCmd.SetWarnings True
View 3 Replies
View Related
Apr 15, 2015
How do i use DMax() function instead of Autonumber, since the table is empty and it wont know what to do with Dmax() + 1 for next record?
E.g I have a new table Customer_details which contains
customerID
customerName
Age
now if i enter data through forms, i want the CustomerID to be incremental for each new record, so i can use Dmax() function. But since the table is empty how can i tell the form, through VBA that if it is null store 1 else store Dmax("CustomerID", "Customer_Details") + 1
View 2 Replies
View Related
May 9, 2013
DMax multi user.I don't get a PO number and when I click on my subform I can't change the values because it says I need to add multi user.Also code that Chris O and RainLover had references additional fields which I don't have. I only have the one field that needs updating.I have PONumber textbox and tbl.Purchase with the field PO..Here's the code I used.
Private Sub PONumber_AfterUpdate()
If (conHandleErrors) Then On Error GoTo ErrorHandler
' If we have some data
If Len(Me!PONumber) Then
[code]....
View 5 Replies
View Related
Mar 1, 2012
I am using DMAX to try to link a table to a from in access.
View 2 Replies
View Related
Jan 11, 2012
This qry pulls from multiple tables to create a job ticket for the technician/installer. I will not list all the tables because I have narrowed down my problem to two specific ones. These two tables:
tblBusinessCustomer
tblBillingInfo
Have a one to many relationship, due to a single customer could have multiple billing notes, this is how the tables are structured:
tblBusinessCustomer:
CustomerID (Primary Key)CustomerNameAccount StatusInstallationDate
tblBillingInfo:
BillingID (Primary Key)CustomerIDBillingNotesDateBillingNotes
I have a one to many relationship using the CustomerID field from tblBusinessCustomer to tblBillingInfo. When I run the report I have a field for billing notes. So what happens is that when I run the report (using the qryJobTicket as a filter) the report will have as many iterations as there are Notes. So for example:
CustomerID: 1
BillingNotesDate: 1/1/12
BillingNotes: (some form of gibberish)
BillingNotesDate: 1/2/12
billingNotes: (different gibberish)
so the report will run twice, one for each "line" of notes. I have tried using a form of the Dmax function as a filter in the qry so that I would only grab the most recent date from the BillingNotesDate field, thus only running a report and grabbing the most recent info.The problem is that I have inherited trying to clean up this database, and I added the BillingNotesDate field, since before they were running a constant "log" in the notes field:
1/1/12 Customer changed Name to ABC company, 1/2/12 Customer was notifited of late payment, 1/3/12....and on and on.SO there are NULL values in the BillingNotesDate field for all of the OLD data. And instead of trying to clean that up right away, I was trying to use the Dmax function to give me the most recent date, but the ones that had no date at all (NULL) it would not pull that record. I am trying to use the DMax function on a Date Field with Null values
View 6 Replies
View Related
