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:
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
I have the code working great to generate the different sections. I have a table with 3 fields (seperating the number) and 1 field that is the combined number. The fields are:
FormatID (Text field),YearID (Text field),BaseID (Number field ) (These are populated using a form called "Test")
FormatID = AR-CR- (This is set as the default format/value) YearID = 2007- (I have this obtained automatically using code in the default value of the data tab) =Right(Date(),4) & "-"
BaseID = 00001 (Sequential by +1)
I have a button on the form called "Save" which I have placed this code in the "On-Click" event. Private Sub Save_Click() If IsNull(Me![BaseID]) Then Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]"), 0) + 1, "00000") End If Me![CustID] = [FormatID] & [DateID] & Format([BaseID], "00000") End Sub
What I can't figure out is how to get the Base ID to restart with 00001 at the beginning of each year? What am I doing wrong?
I have also attached an image showing the increment change if I was to change the year to 2008 how it just incriments the last numbers instead of resetting to 00001.
In plain english: Select the highest value in the field named reqNumb from FlightLog where the Month of txtDate is equal to the variable frmMonth and the year of txtDate is equal to the variable frmYear.
txtDate is a Date/Time field in the table FlightLog
frmMonth and frmYear are both integer variables that take the system time (sysTime) and determine the month and year: I.E.
I have a report that runs off a query that is sorted in descending order the price of something. This price column is in the middle of the report. Every time I try to add a function (sum or count of a column for example) in the report footer or header however, my report is then immediately resorted in ascending values of the first column.
Hi there, I have created a gym database, within it you can push a button each time a member attends and the date they attended is stored in a table "Attendance", multiple values are stored in this table because i want to see each time the member attends.
The problem comes when I went to create a query to pull out members who have not attended for 2 weeks or more. I am not quite sure how to do this, I think it is by putting a DMax criteria on the "AttendDate" field in the attendance table when i create a query but I am quite lost!
I'm trying to make an Attendance Report for my students. I want the report to show each student and how many times they've been "Present", "Late" or "Tardy" in a month and in one year.
I've set up my db with two tables.
Table 1 - contains Student ID and Student Names Table 2 - contains Attendance ID, course id, student id and the Status ("Present", "Late" or "Absent")
I used the sum queries and it doesn't do the count function properly. This is a copy of my query.
I have a single table that has 20 columns. 19 columns (1 for each question) have cells with 1 of 6 distinct values, "SA", "A", "N", "D", "SD", or "DK". The 20th column has a value that identifies that records source. I am trying to create a report that would prompt an individual for the records source, and then once entered, calculate the number of individuals that answered that particular question with one of the six distinct values. It should also calculate what percentage of individuals specificied that distinct answer for that question.
I thought I would just create a query that prompted the user for the record source id and perform the necessary count functions for my particular arguments. However, I get an error saying the query is too complex.
I also tried using DCount directly in the report with the table as the source, but I got an error and none of the totals added up right.
Any and all help is greatly appreciated. If I am unclear in what is going on, I would be happy to post clarification. Thank you for your time.
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!!!
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:
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.
I am trying to see if the following is possible. I have created a form in my database that tracks how many thank you letters I have sent out with a "check for yes" box.
On records where there is no check I want to create a button beside that says "Print Thank You Letter". It would then generate a form based on that certain customer's information.
Is there a way for me to do this? Also, will the report automatically generate that person's information found in the record?
I am building a faux Electronic Medical Records database for the purpose of training med students. I need a command button to pull up a report , but i want to delay the opening of the report (as if waiting for "tests" to come back or be uploaded) is there a way to do this with VBA? i read about the sleep api but i dont know how to get it to work or where to put the code
so what would i add and where would i add it to delay opening the report that is called EKG W/ Subreport ...
im working on an access report and would like a little vba script to run on when the page prints out the next record (mostly just need to hide/unhide a few objects and change the value of a variable on the report when it prints the next record)how i can trigger this (as there seems to be no onNextRecord event handler)
edit: upon a better inspection of the reports record source i realized that on next record doesnt actually work (i could force it to by adding a few new fields to queries and forcing endless loops), what i needs actually a lil more complicated
my report has a header, in the header is a field called productionNo, the record source has multiple instances of prodno's with countless duplicates, what i need is a function to trigger when the productionno changes, there will probably be a few pages with the same production number (theyd be grouped together though), so it should only trigger when the value changes, not when it stays the same
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!
I have an access report that there is One column among 9 of them, refuse to give answer.. Its suppose to Sum all the total of each line in the column. See Attachment. This report is group by ID and sort by Shift. The formula is: Sum([Rates]), and the Rate is list in Details Report where the Sum ([Rates]) is located in Id Footer section.
FYI, when I ran this report, it does not know what the Rate is. However, the rate is the formula which listed accordingly in the report details.
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.
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
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
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?
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.
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.
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...
