I need to create a query that will return for the latest document, the greatest document number. If I do select max(doc_date), max(doc_nbr), I end up getting the max doc_nbr, but it has an earlier doc date. How would I structure the query to do this?
An example is for these values:
1/30 1
1/30 3
1/30 5
2/28 2
2/28 4
The result should be:
2/28 4
I will then need to see if this returned value is different than the value that was returned the day before, but I am pretty sure I know how to do that.
I need a query that will produce the records that have maximum date for each author (distinct authors), ordered by date descending. For example, output should be in this case:
hi all, I've got one question regarding dates. I've already make the form to show the count of records by using query according to the start and end date selected by the user. What I want is when I open the form, it should show the results of count between the minimum and maximum dates inside the data table. It means I want to show the count result without date specification that is the total count from all records of the whole table .
Can anyone help me how can I make it? thanks a lot in advance.
I would like to know if it is possible to pull the max and min start date and end date. Basically i have an option where by the user selects the desired date range that they would like.
Now I would like to know how to get the earliest and latest date range should they not select a date range. My date range is being displayed at the top of my report.
I was wondering if there was a function in access which allows the user to type in a date, and instead of showing just the date, it shows the actual day of the week as well, in a seperate field. Is this possible?
I am tring to add a number of years to a dob. I'm doing this by adding my date+years*365.26 I get a string of numbers. I then convert the number in the next column to actual date again. I'm getting the correct date. I've tried unsuccesfully to use the dateadd function.Now I want my criteria on that column to allow me to return only date in a given to from period of my choosing. I want to determine the date range each time I run the query.
An example would be:
DOB age date I need to follow up 5/15/1935 + 75 = 5/15/2005
Then I request dates between 4/1/05 and 5/30/05 and this date is returned.
I've tried the between_and functions but nothing is returned. I'm guessing it's because the column is still a calculation and not a true date.
I've tried: Adding a column to my query to convert the number string back to date format (criteria failed to return a result). Adding a second column that ='s my converted number to date column (criteria failed to return a result) And I've tried the make table query and then run another query from the new table. I can't seemto make this happen. I think it's not recognizing my data as a date because it isn't returning any data. Is ther an easier way to: Add a number of years to a date and have it return a date and not the number string? If not is there a better way to convert the string to a date? Is there a way to get my criteria to recognize the date and return date between my begin and end dates?
I have a query which pulls up results based on the value of a text box. All works well except one field has a DOB (date) and if I search say for " /1976" I get no results, however search /76 and all the people with that year of birth show then show up. The date is stored in the dd/mm/yyyy format.
I'm using the like [forms]![clients]![searchfield] in the criteria of the query to bring up results.
I have two class variables (both arrays) among about 10 other class variables, that are not returning any values but "" for the string or #12:00:00AM" for a date. Here are my class variables:
Code: 'UPSData Class Module Private p_LetterArray() As String Private p_date() As Date Private p_LetterArraySize As Integer
My Properties
Code: 'Properties Public Property Get LetterArray(index As Integer) As String LetterArray(index) = p_LetterArray(index) End Property Public Property Let LetterArray(index As Integer, NewValue As String)
[Code] ....
As I said, tLet and tDate result in "" and #12:00:00AM# respectively. When I step through the code, the values for tmp.LetterArray(0) is assigned "src" and tmp.UPSDate(0) stores "12/25/2013" correctly.
When I assign tLet and TDate, the same thing happens when stepping through the code. I'll use the LetterArray property to describe what happens:
Get LetterArray is called. p_LetterArray(0) does equal "src" Let LetterArray is called. NewValue is "src" and p_LetterArray is "src" when End Property is highlighted in the debugger Scope returns to Get LetterArray with End Property highlighted. In checking the values, LetterArray(0) = ""
Same steps happen with the same results ("12:00:00AM" vice "")
I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:
Example 1:
Dim ActualManHours as Long ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400 txtActualManHours.Value = ActualManHours
Example 2:
Dim ActualManHours as Long ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")
Example 3:
Dim StartTime as Double Dim EndTime as Double Dim ActualManHours as Long StartTime = CDbl(txtStartTime.Value) EndTime = CDbl(txtEndTime.Value) ActualManHours = EndTime - StartTime txtActualManHours.Value = ActualManHours
I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.
When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?
Could there be some sort of join preventing all records from being returned?
Hi to all; I am not familiar with access function, in Excel its very easy to find the max value at row level, what's the formula in access query to get the max of this example Co1co2 co3….co60max(co1:co60) 200150300 500 ? 212542321 222 ? thanks in advance for respond Majed
I have an expression in a query which is checking a list box on a form. If the list box is empty (is null), then I would like the query to search on the highest (maximum) value in the specified field (CourseFK), otherwise it will look for the value in the list box.
Expr1: IIf([Forms]![frmEditDeleteStudentDetails]![lstEditCourseFK] Is Null,[CourseFK]=Max([CourseFK]),[CourseFK]=[Forms]![frmEditDeleteStudentDetails]![lstEditCourseFK])
However it seems Access will not allow me to do this - I get an error about an aggregate function in a where clause.
Can someone please suggest to me how to get around this? Eg, is there an alternative way to search for the maximum (still needs to be determined dependant on the list box).
Hi, Can some1 tell me how to create a query that shows me the ProcessID for max time and date (date and time are in separate fields) for every PersonID. I need to run a query once in 5 minutes to check the job that every person is doing. They are entering the processID every time they get a new work to do.
How do you find the maximum of four fields in each record of a query. Say (for example) you have daily records of the rainfall across four cities, where the cities are the fields in the query. how do you write an extra calculated field to the query that shows the max. rainfall across the fields on a paticular day.
I am a beginner and have a very basic query. I would like to create a field which returns the maximum value across a number of fields for each record. Is there a function that would do this?
To explain in more detail, each record is a customer and the fields are their purchases by month. I would like to get the maximum monthly purchase amount for each customer over the past year.
Hi, we use access for localisation in our application. The business object which accessess the access uses DAO connectivity and we often land to "Too many client tasks" problem.
is it wise to change the DAO to ADO? if so the above problem will go off? what are the chances that the problem still persists?
is that access has any limits on the number of connections? like 64, 255? or is that ADO/DAO has any limitaions on the number of concurrent connections to access DB?
How many users can an access database support? I'm setting up an app accross 3 workstations but each has multiple users. There may be as many as 1000 records a day entered. Can an access database handle this or do I need to switch to sql server?
I understand that the maximum file size for an Access db is 2GB but can you essentially have as many fields as you like as long as you do not exceed the 2GB threshold?
I am doing some planning for creating a database with ~90 fields and want to know before I start it whether it will be able to handle that many.
My issue: Does anyone know the maximum number of (one to one) relationships with cascading deletes a table can have?
Background: I have a split database peer-to-peer application with ten simultaneous users, 250+ linked tables running on A2K, AXP and A03. MSDE and SQL Server are presently not viable options.
I'm unable to manually or programmatically relate one table to 73 others with referential cascade deletes; some of the 73 are related to still other tables. The one table is used as a recordsource on a subform on each of 73 different main forms, each of the latter having a different recordsource.
In other words, I want to delete a record in any of 73 different main forms, each with a unique table recordsource, and then cascade delete a subform record (which has the same table recordsource, regardless of the main form).
If I combined the 73 into a single table, I'd have a monster record with 500+ fields, assuming the latter is even possible, and in which event perfomance would suffer from excessive network traffic.
What I've tried: I haven't worked this issue for some time and have forgotten the number of relationships for a single table I've been able to create. As I recall, it's on the order of 20 relationships maximum for one table.
Of course, I can do it programmatically delete the subform record on the OnDelete event of the main form, but, for elegance, I'd like to use a cascade delete.
Peer-to-peer network performance is not presently a problem.
I would like to know if there is a maximum nb of columns to use in a table or at least a prefered max nb. For example i have a table "customers" and inside i have "name" "last name" "age" "gender"..and so on is there a limit? By the way i'm using access 2007
My quiery is supposed to search through a table of names, and find the latest timesheet entry date for each name, as follows:
ID: 1 Name: John Timesheetdate: 1/1/2005
ID is specific to each name.
I want the result to be a single row for each ID/name, and to return ONLY the latest timesheetdate (this could either be the maximum value or the latest entry).
I have a feeling that this is a simple problem, but i just dont have the knowledge to do it, any help would be greatly appreciated!
