Finding Max Date

Jul 21, 2006

ok ihopefully i wont make this too complicated to under stand.

i ahev a form which will populate a form from a query. the query is pulling info out a table included int hti sinfo is the date when it was entered. in the criterai of the query i hve added this code
Between [Forms]![Boxes Reviewed]![txtFromDate1] And [Forms]![Boxes Reviewed]![txtToDate1]
whcih means that the date must be between the two differnt dates that must be entered on the form.

the problem is, is that a value can be entered more that once with different dates. i woul dwant the report just to shouw the most recent date. i considered doing a query which requested all max dates from the table and then having a query calling forma query. but i do not know enough on how to get just the most recent date.
so if i wrote a query which held

box num, shelf num, resp_person, and date. what would i have to put int he criteria to only bring back the entery witht he most recent date.

sorry if i have rambled and look forward to your help

View Replies


ADVERTISEMENT

Finding The Date Of An Enquiry

Jan 8, 2007

Hi There,

I am an Access novice and have managed to create a DB to record all incoming enquiries to my business. The enquiries are entered via a form onto a table and one of the fields is the date of the enquiry, which is entered as 01-Jan-07.
What I want to do is build a query that will find all enquiries in a given month.
Has anyone got a simple soultion, please?

Best regards
Keith:(

View 5 Replies View Related

Finding 'before' On A Date Of Birth

Feb 4, 2008

If I have a Date Of Birth in the form XX/XX/XXXX, what code do i need to use in the Design View CRITERIA Box. Thanks for all your help!

Richard J

View 1 Replies View Related

Finding Unique Data Using Date Field

Mar 27, 2008

Hi

I have a table called SWAPS which contains information on each user and the different equipment he has been issued. Query at the moment contains 5 fields

Payroll, Surname, Firstname, Mobile No and Date sent.

SQL for this query is as follows

Select Swaps.PR, Swaps.surname,swaps.firstname,swaps.[mobile no],Max(Swaps,[Date Sent] as Lastdate

From Swaps

Group by swaps.pr, swaps.surname,swaps.firstname,swaps.[mobile no]

Order by swaps.pr;



Now when I run this I get the results I want by date so I get



123456 Smith Fred 0788411025 12/3/8

456789 West Bob 012555447854 13/1/8

So it correctly ids all the last dates for me as I want to see the latest issue date for that user. Now I want to add in another column to this query which contains the serial number of the equipment issued and heres where the problem lies. If a user has been issued more than 1 piece of kit, I get all the serial numbers irrespective of date. How do I get it to display the serial number for the latest date?

Thanks

View 2 Replies View Related

Queries :: Calls Table - Finding Last Contact Date

Aug 7, 2014

There are three tables. An [Action Register] table, a [Calls] table and a [tblContacts] table. The Contacts are common to both.

The Calls table records calls to customer by date

The Action Register table records issues that Customers send in by Open date.

I am trying to make a query where we see the latest date the customer was contacted regardless of which table.

I created two queries.

qryLastCallDate finds the max date from the Call table:

SELECT Max(Calls.CallDate) AS MaxOfCallDate, tblContacts.ContactName
FROM Calls LEFT JOIN tblContacts ON Calls.ContactID = tblContacts.ContactID
GROUP BY tblContacts.ContactName
ORDER BY Max(Calls.CallDate);

qryLastIssueDate finds the max date from the Action Register table:

SELECT Max([Action Register].Open) AS MaxOfOpen, tblContacts.ContactName
FROM tblContacts RIGHT JOIN [Action Register] ON tblContacts.ContactID = [Action Register].Contact
GROUP BY tblContacts.ContactName
ORDER BY Max([Action Register].Open);

The problem I am having is that if I use Left Join I can see all the records from the Calls table but not all from the Action Register table. And vis versa if I use Right Join. This is because sometimes we have calls but no issues in the Action Register table and sometimes issues with no calls.

This is my Left Join query using a Min Max Module I found here: [URL] ....

SELECT qryLastCallDate.ContactName, qryLastCallDate.MaxOfCallDate, qryLastIssueDate.MaxOfOpen, qryLastIssueDate.ContactName, DateValue(MaxOfList([MaxOfCallDate],[MaxOfOpen])) AS [Last Contact], ([Last Contact]+21) AS NextCall
FROM qryLastCallDate LEFT JOIN qryLastIssueDate ON qryLastCallDate.ContactName = qryLastIssueDate.ContactName;

How do I get to see ALL the records from both queries.

View 3 Replies View Related

Queries :: Finding Records In A Query Where Date / Time Has Been Used?

Aug 15, 2013

I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.

I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.

View 5 Replies View Related

Forms :: Finding Records Based On Todays Date

Aug 9, 2013

I'm wondering what would be the best way to update a list of records based on today's date. Right now I have it to open the form based on the LAST record and then navigate to the record they would like to update, I would only like to show what was entered that day.

View 1 Replies View Related

Queries :: Finding Previous Record Date Difference?

Dec 17, 2013

I am trying to use a query to find the previous record of a transaction and provide an additional field that computes the difference in the dates in days. (The days from TransferID 23 to TransferID 24)

TransferID Job Number Tool Number TranferDate DaysOnRent
22 1 10 9/1/13
22 1 12 9/1/13
23 2 10 10/18/13
23 2 12 10/18/13
24 3 10 12/15/13
24 3 12 12/15/13

I have seen other posts that reference the following link.

[URL]

View 8 Replies View Related

Queries :: Booking System - Finding Available Room For Date Range?

Feb 18, 2014

So I'm trying to do this database for my ICT coursework and its a full system for dog kennels.

So in actuality the rooms are kennels.

I have a table tblbookings that amongst others has fields:

Kennel No
Date In
Date Out

I need a way of users entering the requested dates for a new booking and getting an output of a list of all kennels that are available to book for that full date range or even better, a way of running this straight from the form for a new booking frmbookings to just leave the first available kennel no. in the field KennelNo?

View 5 Replies View Related

Finding Help?

Aug 21, 2007

Hi all:)

I am looking for some advise, I work for a small service company and we our trying to computerize our service jobs through the use of access 2007, We have completed 80% of the database but are having a few problems completing, for example the search function on some forms etc, I have searched the web for help and have posted the problem on this forum a number of times but I am still unable to get it to work myself as I am new to access , the problem is I need the database completed asap before we take on a bigger contract and I am running out of time, so I was wondering if anyone knew of anyone / or any company out there that could have a look at the search form, I would obviously pay for it, I have tried to approach a few companies but they will not take on such a small job, they only want to take on the development of the entire database?
Anyone know of anyone?

Jackie :)

View 2 Replies View Related

Finding #

Nov 4, 2005

I'm searching through some text fields like the following:
Ann12 #1
Ann12 #2
Ann12 #3
Ann1 #12
Ann2 #14
ect...
I'm doing a like query
Like "*#*2*"
However, the # is the keyword for Date, so what do I use instead?

View 1 Replies View Related

Finding ABC

Aug 17, 2006

Hi

I hope someone can help with this as I can't seem to get this query to work.

I am trying to use a query to search for the highest value in a column but based upon a specific criteria.

i.e. in the column will be ABC1234, ABC1235, ABC1236 and BBC1234 and I want to get the highes 'ABC' value

I looked on the forum and found this code

Dim strSQL As String
Dim MyValue As DataType 'Replace the proper datatype of field
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tableName.ColumnName FROM tableName ORDER BY tableName.ColumnName DESC"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyValue = rs!ColumnName
rs.Close
Set rs = Nothing

I've modified the query to this

strSQL = "SELECT tableName.ColumnName FROM tableName WHERE tableName.ColumnName Like 'ABC*' ORDER BY tableName.ColumnName DESC"

This doesn't bring anything back but I was expecting 'ABC1236'

I'm new to using 'Like' so this could be the problem.

Your help is much appreciated.

View 6 Replies View Related

Finding BAC-5, BAC-8 And BAC-12???

May 3, 2007

Greetings all,
It's been a while since I've had to turn to you for help. I'm usually able to find what I need in searching through previous posts, but no luck this time around.

I am trying to write a query criteria that will return

BAC-5, BAC-8 And BAC-12

I can get the -5 and -8 with:

Like "BAC-[58]"

But I can't figure out how to get the -12...

The actual part numbers are quite a bit longer and there are hundreds where I only need specific dash numbers returned.

I'm sure this one will be simple, I just couldn't seem to figure out how to search for it.

Thanks in advance!

View 3 Replies View Related

Finding Something Thats Not There?

Sep 27, 2007

Hi guys

I have a DB for insurance permits.

tblSubtractors
ID
NAME
ADDRESS
PHONE

tblPolicy
ID
SubbyID
POLICYID
Insurer
PolicyNumber
ExpiryDate

tblPolicyTypes
ID
Name


Most subcontractors will have a policy number for each type of policy in tblPolicy. But is there a way I can run a query to show each subby that doesnt have one of the policies? As an example, the query would show me that ABCcontractors doesnt have Marine Transit insurance. XYZContractors doesnt have Public Liability, etc

I know how to show all the policies that have expired, but I'm looking to identify which ones dont have a policy at all

:confused:

View 7 Replies View Related

Finding ID

Aug 6, 2006

Hello,
I have this code that I use to update a record:

Code:<%Dim idid = session("loginID")Dim rsSaveSet rsSave = Server.CreateObject("ADODB.RecordSet")rsSave.open "log", conn, adOpenDynamic, adLockOptimistic, adCmdTablersSave.find "id=" & idDim Datumlogout, Uurlogout, loggedoutOKrsSave("Datumlogout") = datersSave("Uurlogout") = timersSave ("LoggedoutOK") = ("true")rsSave.updatersSave.closeset rsSave=Nothing%>

Can someone help me please, I would like to have the code changed to:

When the ID not excists, that there is a new record written.
Thanks in advance!

View 1 Replies View Related

Finding An Average

Aug 8, 2007

I have a customer concerns database that contains the dates for when the concerns were reported and tyhe dates for when the concerns were resolved. I am trying to make a query that finds the average of how long it takes for the concerns to be resolved. How can I do this?

View 1 Replies View Related

Finding The Last Row In Worksheet

Feb 29, 2008

Now this is not like me, posing a question, but I thought its about time I codes the app correctly.

My app opens up any given spreadsheet. The first task is to establish the last row used in the spreadsheet. Normally you would loop until there is nothing in a cell in column A, whatever.

However the way the spreadsheets are provided there may be pleanty of blank rows. I have tried using the Edit GoTo Special LastCell and converting it to a macro

But when I run this VB it does not like it for some reason.

I am sure someone out there has come up with a solution by now.

CodeMaster::cool:

View 4 Replies View Related

Finding Current_Date?

Nov 2, 2005

I have a table, with 4 records in it. 2 records have the "date" field for 01/11/2005, and 2 dates are for 02/11/2005.

Is there a way to just query the "current" date? Weather it be in a pop-up box once the query is selected, asking for the date? or is there a restricted word within access2003 that lets you find the current date?

I would essentially like a command button within a form to allow the user hit teh button, and it will automatically find those records within the DB with the current date(set to system clock) and export it to excel(i have this portion, the exporting to excel complete)

Any help would be greatly appreciated.

Connor

View 7 Replies View Related

Finding First Records Only

Nov 1, 2006

lots of customers. lots of orders. i need to query for the first order of each customer only. thoughts?

View 1 Replies View Related

Finding A New Record

Feb 24, 2005

Hi

I apologise if this is really basic - I am learning as i go along. I have designed a database. Most of the info is stored in the clientinfotbl. On the main form, I would have provided an option for users to search for a record based on its ID number and then pull up various forms accordingly. I am using the "find and replace" option to do this. My first question is, is there a better way? This feels a bit clunky!

Second question is, once I have entered a new record in the "newclientfrm" and go back to the mainfrm and search for that record, it cannot be found (once I have closed db and gone back in, it can then find it). Is there a way round this?

Any help would be hugely appreciated :)

View 2 Replies View Related

Not Finding Other Form

May 12, 2005

I would like to make a value equal to another value in another form, but it keeps telling me that it can't find the Form.

newID = Forms![frmLogIn]![UserID]

Error message is 'Access can't find the form 'frmLogIn' referred to in a macro expression or Visual Basic code'.

I have rechecked the spelling and caps on the form name 5 times so that's not it :(

James

View 5 Replies View Related

Finding The Subform????

Sep 1, 2006

Here's a real stumper... I have a form with a subform based on the account number in the main form. This form has been running without a hitch for maybe two years! Now, while updating an unrelated form in the same database, I test the new additions. I get an error message that the main form cannot find the subform, and it goes on to display a blank white box instead! Here's what I have already tried:

1. Checked the name of the subform. It's correct, just like it's been for 2 years.
2. Running compact/repair in case of corruption
3. Deleting, then re-placing the subform on the Main Form.

One other wierdism - after getting the white box where the subform belongs, and after being told that the [subform name] can't be found, I can right-click on the white box, select the "Form" option, and the subform appears! To me, that means that the "can't find" error message is a misnomer, but I'm at a loss as to where to look from here.

I guess what I'm asking is if anyone has seen this behavior of a form "not finding" its subform, yet it can be forced to display it with a popup menu? Also, any experiences with this or any related information would be appreciated.

Thanks-
Scott

View 3 Replies View Related

Finding The Last Day Of A Month

Oct 4, 2004

I need to create a function that will return the last day of a month, regardless of which month it is and, in the case of February, which year it is. (Gilbert & Sullivan got it right - the powers that be played a cruel joke on us with leap years!) I could hard-code in the months into my code, but there's got to be a way to do it without typing in everything manually...right?

View 1 Replies View Related

Finding My Posts

Sep 4, 2006

Is there a way to find all my old posts except looking at all 381 pages?

View 4 Replies View Related

Finding A Record

Feb 5, 2007

Hi guys, need a bit of help with my Database. I have a Table with has ever player to play for my football club (1700 records in total), now i have another table called games (which has a record for every season played for each player, and is linked to the first table using the relationships and a field called ID. Make sense so far?

Well i then use a query to combine and calculate totals of certain fields and then i can use the query to show every single statistic possible on ASP pages. But when viewing my query in Access only 1699 records show up. So my thinkin is that either two records are identical (which is impossible because every p;layer has an ID and they cant be duplicates) OR a player has ZERO games in all years (in other words the player has no records related to it in the second table called games).

My question is, instead of searching 1700 records manually to find which one is there any way i can find which player would have NO records from the related table joint to it?

View 2 Replies View Related

Queries :: Finding The Next Min?

Jul 6, 2013

Im looking to display the 4 lowest figuers obviously Min is the lowest but how would you dind the 2nd 3rd and 4th Min or is not possible?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved