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.
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 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 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.
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.
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 have a control (command button) on a form that opens up a report which filters out a single record. The filter is based upon a value in a text box located on the form. Within the VB code, I put some code to prevent the form from opening if the text box had no data in it.
When i click on the button (assuming the text box has a value in it), I get the error message I defined, and the report opens properly. This tells me there is an error being generated.
My question is, how I determine what error has been generated? And how do I fix it?
Here is the code I used:
//This is the code for the form. Private Sub cmdButtonPreviewLabel_Click()
On Error GoTo Form_Closure_Error2 DoCmd.OpenReport "reportLabels", acViewPreview
Form_Closure_Error2: MsgBox "Sorry, but I cannot print a label without a Job Number.", vbOKOnly, "Notice" Exit Sub
//This is the code for the report Private Sub Report_Open(Cancel As Integer)
i have a query that takes fields from various related tables to give me the information that i want
The primary table has 199955 records
The query displays only 199939
16 recordss are missing
I know why this is: the primary table (CWT reqd equip) has a field on it that is a primary key on another table (cwt Equip). On "CWT reqd equip" 16 records must have an entry in this field that is not present on the table where the entry is a primary key (cwt Equip).
I dont know how to identify what these 16 records are on the primary table. I want to get rid of them so i can enforce referential integrity between the two tables.
can anyone help? Have i wrote this clear enough?
thanks
p.s i've attached a doc showing how the query is built. the other tables other than the two i mentioned aren't important, for this.
Is there a way to query any system tables to list all tables that are being referenced in any queries, forms, reports, modules, etc.? We've inherited a rather large Access application containing some tables that we suspect aren't being used at all by the application, and we'd like to verify this suspicion before deleting these tables.
I need to sort out the repeat customer, e.g. I have a list of customer's name, like Nike, Emerson, Alcan, etc... and if they sign another contract with my company again this year, I will name it like Nike 05, Emerson 05, etc...
So how can I get the result of how many customers have signed the 2nd contract with us. I have no clue now... :confused:
What I am doing is trying to have access identify and label various lanes of transit.
So, for example. I have the below...
Origin Destination Via Transit Time Hong Kong New York East Coast 38 Hong Kong New York West Coast 32 China New York East Coast 34 China New York West Coast 29
Is there a way in Access to have it identify that Hong Kong to NY via West Coast is the "DEFAULT" where HKG to NY via East Coast would be "ALTERNATE" ?? And then do the same for the next origin? The table I have now has over 71 different origins but I need it look at each origin independently.
I have a tblOrders that has 1800 records with 31 fields primary key tblOrder which is a number.
Periodically users export data from SAP into a spreadsheet, which are changes to the original data but may not contain the exact same fields as the master tblOrders. The data in this spread sheet identifies changes and may contain aprox half of the fields that are contained in the master tblOrders. The field names however (in the spreadsheet) will be of the same type as in tblOrders.
What I would like to do is identify differences between the tblOrders records and the records in the spreadsheet, and then update the tblOrders to show the changes.
how would you proceed? Maybe create a new table by importing the excel spreadsheet into access and then somehow querying the differences and updating the master. If anyone has done anything like this b4 please could you update me...
I have two tables, which have same fields. The tables are used for keeping record of news clippings, that are clipped in two different offices. I understand that the best way to manage the database will be combining them into the same table. Yet, it's not an option for the time for some technical reasons. Each table has the same fields as follow:
Input date issue date headline in English news source category
I want to have a list of news clips from the two tables by a specific category. I will have a form, that allows user to select category as the criteria to query the two tables. I manage to do that in a single table, but how to do it for two tables. This will be very help if you can help. ideally, I can use category to query news clips as well as academic journal clips (from another table) table.
I have a global function which I have hooked onto the onClick event of controls. Currently I am using commandButtons as my control of choice. In my function I can then reference Forms.ActiveControl which is no problem.
However, for lots of reasons I really need to use Image controls. Now, my problem. Image Controls have an Onclick Event, but they do not get focus so the Forms.ActiveControl will not work.
How can I identify the image control that has generated the click event if the control itself does not get focus and so cannot be referenced by ActiveControl? Working on a possibly work around, but could do with the best brains in Access on the case!
Basically I would like to populate column three "OUTCOME" according to the following logic:
if train A10 has multiple destinations (we see this in the second column) then in outcome I woudl like to have "multiple destinations" if the destination is only one (in case of trains A15 and A16) , then I would like to have that city in column 3 "OUTCOME".
I have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.
I have a form linked to table tblINCIDENTS. When I create a new incident, I select DATE_LOGGED in a text box. This updates the field in tblINCIDENTS.
I would like to show on the form (and store in the table), e.g.
"Record 5 in Year 2013"
I already had the new record number as an autonumber, but now I created 2 extra fields in the table - recordno and year.
I have a hidden text box which, on DATE_LOGGED.CHANGE, finds the Year() from DATE_LOGGED. I have tried using a Query to find the MAX RECORDNO of YEAR. I managed to display the Max (having manually populated the fields!) in a textbox, but am now going round in circles!
I'm wondering if there's any way to find out who has a database open.
I've saved the file as an ACCDE for the users and kept the ACCDB file as my working file. Whenever I make changes to the db I need to save a new ACCDE file on top of the existing one. In order to do that, I need to ensure no one has the db open. Half the time I need to track down 1 of 15 people to find out who it is that has it open so I can kick them out and save.
I don't have any login features, but could use the POD # that each cubicle has associated to their computer if Access allows that...
Is there any way to identify the Top row in a continuous view form?This changes of course as you scroll down. What I'm thinking of doing is a page jump page forward, or back. The form is based on a query and has a line number field, but I can't figure outhow to get at it in code.
I have made a program that can identify a entry in a field (ID) and change the quantity of a field. However, if can only identify numbers. I have the code as follows:
Code: Option Compare Database
Sub AddToInventory() Dim tb As DAO.Recordset Dim I As Integer I = 1 Do Until I = 2 On Error GoTo Canceled
[code]...
The "where ID2 = " only works with numbers. Is there anyway I can make this work with Strings? Basically, Find the string in a column and update the quantity.
