I have Query which has dates in multiple columns. I need to know if there is a formula that will allow me to display the oldest date and the newest date?
Below is the output of my Query in Excel. I need the output to have two additional columns: Beginning Date and End Date. Based on the example below, I would need the beginning date to show 6/21 and the end date column to show 6/23 so when I export to excel, it shows these two dates. In Excel, there is a min/max formula that can do this but it looks like the min/max in Access only displays the min/max data? Unfortunately, creating this formula is not an option after the fact. Secondly, I need the word "DIRECTION" to show in it's own separate column in the in the query. I don't know if I can do this without adding another table to the Query or if I need can just add a column in design view and put something in the criteria that will do this.
I have included an example of my current output and what I need the output to read.
Current Output
Sunday
In
Out
Monday
In
Out
Tuesday
In
Out
As you can see it consists of the name and a date of the backup. When it populates the combobox I want it to go in date order I newest to oldest. At the moment its going of the first number.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10.
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I currently have a form where I would like the form to display the oldest account first, so the overall objective is the employee will action the oldest account first and then go onto the next one etc.
I have a crosstab query that I ONLY need the newest date from the table I'm pulling from reguardless of what it is for. Here is what I have:
TRANSFORM Max([Cust Count].[Active Customers]) AS [MaxOfActive Customers] SELECT [Cust Count].Node, Sum([Cust Count].[Active Customers]) AS [Total Of Active Customers] FROM [Cust Count] WHERE ((([Cust Count].Services) In ("core","data","telephone")) AND (([Cust Count].ASC)="uh") AND (([Cust Count].Date)=#6/12/2005#)) GROUP BY [Cust Count].Node, [Cust Count].Date ORDER BY [Cust Count].Node PIVOT [Cust Count].Services;
I know this is only setup to get items from 6/12/05 but more will be added to the table and I only want the new info for this query. Thank you.
I am a relatively new to some of the more advanced features of Access. Here is my problem:
I have a table that will be linked to a graph. Each record is for a particular date. There can be multiple dates within a month, but for each month I only want to display the most recent date within a given month. In other words, the graph will only display data from the last record in a given month.
My idea was to somehow flag the records that are the newest date within a month. However, I would also be open to running an append query to simply delete these records as the table is updated (though I would rather preserve the records and stick with the "flagging" option).
I need to make a query that will show the oldest cases and then open this one in the main form. It will be possible that there could be several cases with the same date. For example the query runs and the results are 7, how could I get one of those cases in the main form.
The idea is that a person will click on a button called "next case", the query will run and then it will open the case in the main form.
Option Compare Database Option Explicit Function Backup() Dim fso As FileSystemObject Dim sSourcePath As String Dim sSourceFile As String Dim sBackUpPath As String Dim sBackUpFile As String Dim strSQL As String
sSourcePath = Application.CurrentProject.Path sSourceFile = Application.CurrentProject.Name sBackUpPath = Application.CurrentProject.Path & "Odontiatreio BackUp" sBackUpFile = sSourceFile & Format(Date, "ddmmyyyy") & " " & Format(Time, "hhnnss") & ".mdb" 'Add new file to table. 'Assumes tblFile has 2 fields: ' FileID (AutoNumber) ' FileName (text) strSQL = "INSERT INTO BackUp(BackUpFileName) " _ & "Values('" & sBackUpFile & "')" CurrentDb.Execute strSQL 'Delete oldest filename if there are more than 5 records. strSQL "DELETE * FROM BackUp " _ ============>Error<========= & "WHERE BackUpID IN(" _ & "SELECT MIN(BackUpID) FROM BackUp " _ & "HAVING COUNT(*)>5" CurrentDb.Execute strSQL, dbFailOnError Set fso = New FileSystemObject fso.CopyFile sSourcePath & sSourceFile, sBackUpPath & sBackUpFile, True Set fso = Nothing End Function
Error: Compile error: Expected Sub, Function, or Property
Set fso = New FileSystemObject fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True Set fso = Nothing
End Function
---------------------
Now, what I want to do is delete the oldest file after there have been 5 files copied. In other words, I will be running this once a day and only want to retain the newest 5 copies. Once the sixth has been copied, delete the oldest one in the directory.
I am trying to identify how long inventory has been in a specific location. The information that I have is the current inventory level and movements that have put the inventory there. Assuming FIFO I would like figure out the following.
ex: I have 4 movements with various dates AND I have 3 pieces currently on hand, how do identify the date of the last 3 movements (assuming that the one that has been moved out was the first one in)? Of course there is a long list of part #'s, on hand quantities and movements into the location so the logic should work for all.
I've been searching the forum for an answer and see that there are a lot of knowledgable people out there but didn't find an answer to my problem so I'm hoping someone can help.
I have a table that has PartNo, Unit_Price, Updated_Date. It is filled with every purchase we have made for the past two years, so there are multiple records for the same part numbers with different prices and different dates. I'm looking to pull the most recent dated record for each part number. For example: PartNo Unit_Price Updated_Date 123 7.89 08/12/07 123 7.91 10/15/07 123 8.02 1/10/08
I would like it to return the bottom record only (and do this for each of the hundereds of part numbers we have) so I end up with a table that looks like... 123 8.02 1/10/08 456 1.52 1/22/08 789 10.59 8/02/06 and so on... :D
I have looked for ages for a solution to this and have not yet found one to suit me so here I am.
I am creating an Access 97 db in which I have a form (section1) which shows its records in datasheet format(results from a query). On this form is a button which opens another form (fileViewNewSec1) where the user can input the data for a new record. In the closeEvent() part of this form I have put:
=[Forms]![section1].[Requery]
This successfully refreshes the form and the new record can be found. However, there are many records in this database and it would be nice if once the form (section1) has been refreshed that it would focus on the newly created record. I am not sure if anyone can help me with this.
The records are sorted by "fileRef" and therefore the newly created record is not placed at the bottom of the records. They do however have an auto number primary key, I am not sure this will be useful but they you go.
I am creating an access DB that has job listings and displays the DB online.
I am creating tables rows for 'BeginListing' and 'Endlisitng' etc that are stored as a Date
How do I get the asp/vbscript to check the date 'EndListing' of the entry and to stop displaying it when the date is later than today (so it takes them offline when the date is expired)?
My script is like this and loops through each record set displaying the contents:
'Loop through the recordset Do While not rsJobPostings.EOF
'Write the HTML to display the current record in the recordset Response.Write ("<div class='sitetext'><br> Job Number: ") Response.Write (rsJobPostings("Job_Number")) Response.Write ("<br>Job Heading: <b>") Response.Write (rsJobPostings("Job_Heading")) Response.Write ("<br>") Response.Write (rsJobPostings("ListingEnds")) .... you get the idea.
I've almost completed the DB I'm working on, but still have a few loose ends to tie up that I can't seem to figure out. I've spent many hours already on this forum searching for solutions, and have tried a few different things, but I still can't work out these bugs.
First, I need my form to open with the latest records filtered first, so that the most recently added records will be easiest to find and edit. I'm not sure how exactly to do this. I've tried a run query when the form is opened, but I still need all the records displayed or accessible. The form is called frmDenial, and I would like it to be looking in the DateLogged field of the form. Any suggestions?
Second, I have command buttons that will export data in the fields to MSWord Templates via bookmarks. The button runs well by opening the document, inserting the data, and printing. I have also tried several different code syntax to close word once it is done, but it is still staying open after printing. Here is a sample of the code: ________________________________________ Private Sub Print_Letter_Click() Dim objWord As Word.Application 'Start Microsoft Word 2000. Set objWord = CreateObject("Word.Application") With objWord 'Make the application visible. .Visible = False 'Open the document. .Documents.Open ("G:PharmacyPrior Auth Docs and DataRevised Pharmacy Denial ProcessesKAN Not Nec or Benefit2.dot") 'Move to each bookmark and insert text from the form. .ActiveDocument.Bookmarks("bmkFirstName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRFirst)) .ActiveDocument.Bookmarks("bmkLastName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRLast)) .ActiveDocument.Bookmarks("bmkHRN").Select .Selection.Text = (CStr(Forms!frmDenial!MemberNumber)) .ActiveDocument.Bookmarks("bmkAddress1").Select .Selection.Text = (CStr(Forms!frmDenial!MBRAddress1)) End With Print_Letter_Click_Err: 'If a field on the form is empty, remove the bookmark text, and 'continue. If Err.Number = 94 Then objWord.Selection.Text = "" Resume Next End If objWord.Application.Options.PrintBackground = False objWord.Application.ActiveDocument.PrintOut objWord.Application.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Quit Microsoft Word and release the object variable. objWord.Quit Set objWord = Nothing Exit Sub End Sub _______________________________________
Finally, I have two cascading combo boxes set up that will auto pop related fields based on the selection made. But, if an item is not in the list, I would like the user to add it to the linked table to appear in the list. I also have this working well, with a pop up asking the user if they want to make the addition, type in the new item, and add to the list without requiring the user to refresh or exit then re-enter the form. But it will only add the item name, and not the item description (another field in the form and another column in the table). How can I modify the code to prompt the user to enter these other details? I can link it to a pop up sub form to enter the data, but if possible, would rather the boxes pop up to have the user type in the data. Here is the code I have so far in the NotInList Event... __________________________________________ Private Sub DrugName_NotInList(NewData As String, Response As Integer) Dim DB As DAO.Database Dim rs As DAO.Recordset Dim strMsg As String strMsg = "'" & NewData & "' is not an available Drug" & vbCrLf & vbCrLf strMsg = strMsg & "Do you want to add the new Drug to the current Database?" strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it." If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then Response = acDataErrContinue Else Set DB = CurrentDb Set rs = DB.OpenRecordset("tblDrug", dbOpenDynaset) On Error Resume Next rs.AddNew rs!Drug = NewData rs.Update If Err Then MsgBox "An error occurred. Please try again." Response = acDataErrContinue Else Response = acDataErrAdded End If rs.Close Set rs = Nothing Set DB = Nothing End If End Sub ______________________________________________ The two other colums in the tblDrug that I need the user to be prompted to fill are Denial Reason (column 3) and Alternative (column 4). What would be the best way to accomplish this?
I would really appreciate any help or suggestions with any of these problems. Thank you so much! :o
I need a Query that grabs the lastest "newest" data from the 5 tag.table tags generated from the query below and Display them in a result,
SELECT FloatTable.DateAndTime, FloatTable.TagIndex AS FloatTable_TagIndex, FloatTable.Val, TagTable.TagName, TagTable.TagIndex AS TagTable_TagIndex FROM FloatTable INNER JOIN TagTable ON FloatTable.[TagIndex] = TagTable.[TagIndex];
I am trying to write a query that will check all records in a table but exclude the 10 newest records in the table.
The table is from a stock program i have wrote for the company i work for (i am a novice access user). what ive been asked to do is write a duplicate order system that will flag up if the order has already been packed.
the table logs the [OrderID] with each item [barcode] scanned out with a barcode scanner what i want is a query that checks the OrderID for a duplicate entry in the entire table but because the OrderID is entered with every item scanned i want to ommit the last 10 records as prety much no order has more than 10 items i understand this may not be 100% fool proof but it is close enough.
The other option is to have it ommit all records within the last 15 minutes there is also a [Time] and [PackDate] Field which im guessing could be used for this the time field records Now() and the PackDate records Date(). After searching the web i cant seem to find anyway to ommit the last 'n' records and the few things i have found with the Date("m",-15,Date()) doesnt seem to work
SELECT TblDietPlan.DietPlanID, TblDietPlan.ClientID, TblDietPlan.MealDate, TblDietPlan.MorningSnack, TblDietPlan.AfternoonSnack, TblDietPlan.EveningSnack FROM TblDietPlan WHERE (((TblDietPlan.ClientID) Is Not Null) AND ((TblDietPlan.MealDate)=Max())) ORDER BY TblDietPlan.MealDate DESC;
I have six textboxes in my form. First 5 textboxes have dates ( MMDDYYYYY). In the sixth textbox I need to display the minimum date among these five dates.
I have a series of forms in my shiny new database and I have added the auto time and auto date fields to the form headers. I have added the code to keep the time ticking over and it all works fine.
Unless the is no data. If for any reason there is nothing in the Details part of the Form, the date and time do not display. As soon as any data shows up, so do the date and time.
The VBA code is running in either case so I am a little baffled. Usually Google is my friend but not in this case.
I have recently started learning to use Access and all is going very well except for the date. As can be seen from the images below I have set up my date function correctly in Control Panel and everywhere I use a date it is displayed correctly. In Access however numbers are added which I cannot figure where they are coming from and don't want them there. In the second image you can see what the various Format options look like. When long format is used in a Form or Report it displays those additional figure. I cannot get 2015 to display but get 15316 or similar instead.
I have a field named "PICK_DATE" in text format where pickup date is stored in DD/MM/YYYY format.
Now I wanted to generate a report with the order by clause of PICK_DATE. But it is not giving me the desired result. I want to see the latest PICK_DATE at the top of the report in desending order.
Can any one pls help me out to solve this problem ? :confused:
I was wondering how difficult it would be to do the following:
1. A user opens up a form. 2. The user uses a combo box to select his/her name. 3. There is a date combo box on the form and by default, it is set to today's date. So when the user selects his/her name, the records for today are displayed. Changing the date will show only the records for the date shown. 4. The user will have the ability to add and delete records specific to the criteria chosen in the text boxes. Adding new records will only add records for the date chosen in the combo box. E.g. if he/she adds a record for today, it will only be seen when today's date is selected.
Will a subform have to be used for something like this? Or could it be done with one form using filters?
Any thoughts and/or approaches on this would be greatly appreciated.
In a Query, how can I display in one column a value that can come from 2 different fields based on a selection.
Say I have two date fields (Date1 & Date2) they can both be blank, or one or both of them could have a real value. To make it simple here is the logic I want to employ. If Date2 > " " display Date2, otherwise display Date1. I want the value to appear in the same column. Can this be done in a query or must it be done in a report? Thanks!
