Modules & VBA :: How To Arrange Table Data Into Date Order
Dec 31, 2013
I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:
strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";"
Set rst1 = db.OpenRecordset(strSQL)
The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.
Hello All, I am new to access so this might be a stupid question but here goes.
I am making a new database for my orders (which I download from my eCommerce software in the form of a CSV file).
The Problem I am having is that my Order numbers are unique and that is what identifies the orders, however in the table data each order that has more than 1 item is listed on 2 seperate table lines. All info on the rows are identacle until the Part # field and the price field.
How can I make access understand that these few lines of data with the same order # are actually the same order with multiple items on the order?
Okay I have an Orders Form, which generates a SubTotal Price (ex VAT), VAT, Shipping & Freight Charge based on an IIF statment of being under a 50.00 order apply 20.00 charge. And finally a Order Total with it all added together. This works fine, however I cannot seem to find a way to post these prices in my Orders Table. When I click on Datasheet View for the form, all the details appear as they should.
What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:
Private Sub Combo0_AfterUpdate() Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;" Combo2.DefaultValue = [Combo2].[ItemData](0) Command4.SetFocus End Sub
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
I've accomplished some simpler things like hiding fields based on the data in another field but nothing very complex. To the point: I have a report that shows data in both rows and columns, the report has a 7 columns, 3 of which could or could not contain data other than zero, in which case the column is not displayed. The problem I have is that it could be any of those three at any given time depending on what data is available for that report and what the user wants to see. Basically, if a given company has no data regarding column "A", then column A is hidden, but the main grouping is done through rows so all companies contain at least 0 for all columns (what I did is that, if a field sums the whole column returns 0, then the column is not visible). What I need to get working is the second part of this, have the columns rearrange themselves depending on which column is hidden (it could be that all 3 are hidden). How could I make this work? (btw, autoshrink is not usefull for this since it only shrinks vertically, not horizontally).
Help - Why don’t my date fields sort in date order ?
I’m having a very annoying problem with my date fields. I have a database that keeps records of patients details and their visits to the practioner.
I have a form based on Patients table with name address etc, and a subform based on a Case Details table with details of visits and advice given etc.
I need the Case Details subform to display the latest visit/record at the bottom (the earlier visits at the top of the list). For some reason I just cannot achieve this. Dates seem to be scattered all over and I can’t understand why ??
I have tried sorting the Case Details table in ascending order, sorting the ‘Date of Visit’ field on the form in ascending order (which does work but as soon as the form is closed and re-opened, all the dates are mixed up again).
In the ‘OrderBy’ property of my Case Details Subform, it says:- [Case Details Table].[Date of Action] which according the help I have tried to look at should sort the records ??
I’d really appreciate any pointers as to where I’m going wrong
Code: SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type FROM tbl_test group by facility_type;
It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.
The error I get is a syntax error the SELECT statements.
There are two tabs named Table1 and Table2. In actual there are two tables in Access database named Table1 and Table2. How the data is stored in ACcess tables, I have made two tabs in excel workbook. Now I want Access VBA code that will check if data in Reference field of Table1 matches with any of the data in Reference field of Table2.
If it matches then change the status of the corresponding record of Table2 with either "Withdrawn","Obsolete" or "Updated". SO it depends upon which field out of "WIthdrawn","Obsolete" and "Updated" in Table1 stores "Y". At a time only one of them will have "Y" and rest of two fields will have "N" as shown in the sheets.
As in the example, now Reference "R566" of Table1 matches with Table2 Reference so the status field in Table2 for that record will be "WithDrawn".
A Inputbox will shows up and then ask for a Special date.
I have a table which has for one ID always a Special date.
Many ID can have the same Special date. So i want to Export all with the same Special date.
I've created the following code. When i enter the date, it doesn't Export anything.
Code: Dim xlApp As Object 'Excel.Application Dim xlBook As Object 'Excel.Workbook Dim xlSheet As Object 'Excel.Worksheet Dim rst As DAO.Recordset, Rechnungsdatum_Finanzamt As Long, tmpStr As String
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected Total Envelopes=sum(Envelopes) for date selected Total Documents=sum(Cases) for date selected Total Pages=sum(Pages) for date selected
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code: Private Sub Test_Click() Dim strSQL As String
I have a database which in one part records customer orders. What I would like to is identify customers who have not ordered for a given time period i.e. 30 days.
I know once I have built the query, I will require a parameter for the time period i.e.30 days.
I started by trying to record the last date ordered for a customer in a query, but am having problems with this. I had a query including information from my table for Orders (tblOrders). This had:
Order ID Customer Name Date Order Required
I do not need any more info apart from these fields.
The results would include all orders, so for some customers there would be numerous 'Order ID' & 'Date Order Required'. In the query, I did try to select the summary option, to group information by Customer - but the 'Date Order Required' was always the first date ordered. What I would like is only the last ones for all customers to be shown.
Can someone provide some guidance on how I can achieve my objective.
I have a report that I want to see the records by Job Due Date. I have it set up where it puts the records in ascending order in the query, and it works, but when I open the report they are not in order anymore. I have checked in the properties window and they are still supposed to be pulled in ascending order by job due date. Why won't it put them in the right order??
I have two identical Databases - one is a Live DB and the other is a Training DB. The data is held on a sql server connected to an access front end. (I do not have access to the sql server).
- On each table I have a field called UpdatedOn - On each table every record has one field that is a unique ref. But the name of each field containing the unique ref is different on each table - There are about 100 table to loop through.
What I need is some code that will loop through each table and each record, and where the UpdatedOn field on the live DB is not equal to the UpdatedOn on the training DB - update the whole line on the training DB with the Live DB data using the unique ref
I intend to only have one access client and import the sql tables from the Live and Training into the one. Therefore for example sqlMyTable will be live DB and sqlMyTable1 will be the Training DB
I am trying to run a query that will give me all of my customers who last purchased on or before 2004. I do not want anyone who purchased anything in 2005. I am including, name, address, telphone number, salesman, order date, and shipping method. I leave all criteria blank, and I have the order date totaled at "max", I run the query, then I filter the form for the shipping method of "no cod". When I run the query, it gives me my customer listing for everyone who purchased from 1999 on and where shipped out no cod. That is fine, except that some of the customers are listed a few times with different dates. Say, John Smith, my query is coming up with an order from 1-5-99 and then a few pages later, (because I am also putting it in descending order) he is on another page with the date of 2-5-2000. Why is this? In my criterea for the "order date" I have it as "MAX", which should bring me this customer only once. Is that correct? I am really having trouble with this. Can anyone help? I only want the customers listed once with their last order date.
I have tried posting this message before, but with no results. I am trying to get a query that will give me all of my customers and show what their last order date is. The query that I have now is coming back with ALL the dates the customers made purchases. Here is how I have the query set up: Field1 Customer ID Field 2 Customer name Field 3 Address Field 4 State Field 5 Phone # Field 6 Order Date (Last in Group by) Field 7 Shipping Method Field 8 Salesman name
The query is a select query and I hit the totals button on the toolbar. In Field 6-Order date, I have it grouped by "last". Another problem that I have it I am working in a 97 access database. I have tried to convert it to 2003 with no success. So, I am not able to make NEW queries or reports. I can modify them, but when I close the query or report design, it does not save.
Can anyone help me with this query problem? It is very perplexing.
I'd like to prevent for duplicate time/date for same GuideCode. How can i prevent for those during the entry time or save time ? Is it possible ?
Capture1.
Capture2.
1. AUNKZ(GuideCode) is already assigned in other file number(12345) for 03-Aug-14 (as 02-Aug-14 to 04-Aug-14).
2. TOD(GuideCode) is already assigned in same file number(23456) for 10-Aug-14, 12:00 PM (as 10-Aug-14 to 10-Aug-14, 8:00AM to 1:00PM). I'd like to prevent if the file number are same or not same.
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
