I want to write an sql or vba code to delete records based on the month...For example I have a field called date which contains a date and I want to delete all Dates that are In April
Ive tried Delete from LinkTable Where month(Date) = 'April' Still Nothing
I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.
For example:
Start date: 01/01/2014 End date: 31/12/2015
service frequency: Every 4 months
Using the above information the service months are: 1 - April 2014 2 - August 2014 3 - December 2014 4 - April 2015 5 - August 2015 6 - December 2015
I am trying to export records to excel that are in a given month based on an input month. The user would select the month with the records desired and click the button to export them to excel. The attached zip file has an image of the database and the code behind the button is:
Code: Private Sub Command33_Click() DoCmd.OpenReport "HLA_TAT", , , "Len(Exception & '') > 0 AND Receive_Date > #" & Forms!HLA_TAT.Date & "#" End Sub
[CODE] can not access referenced form HLA_TAT [?CODE]
I'm trying to subtotal data in an excel file but I am getting a subtotal method of Range class failed in the red text below. I have been trying to get this to work with no success.
Code:
Public Function SUMMARY() Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") With xlApp Dim wb As Object
I would like a query to return dates based upon the input of just the day and month. At the moment I have a parameter query which asks for 'start date' and 'end date' and this works fine, but I want the query to return all the records for all the years in the database and not just the current one (date format is dd/mm/yy)
So if I type <start date> 01/01 and <end date> 02/01 the query will return:
I have a subform on which I log the times spent on any particular project.
I have another subform on which I want to select a month and use this to select all the instances on which work was done on any given project for a particular month. I would prefer to use a pop up calender to select the month (i.e. by slecting the last day of the month in question)
Has anyone got any idea how I might do this - I am completely stumped.
I am looking at creating a query which would report whether a specific field is yes/no. However, I would like it to based on that days specific month. For example, I have 12 check box fields, one for each month, and I would like to create a report where it would look up whether or not the current month has a check box in it. Additionally, I would like to create another where the criteria would require me to put in a month to search for. For example, to search to see whether or not december has been checked.
I need a subgrouping for a report I am working on.
I have a DateIn field that contains date as mm/dd/yy. I have managed to get a count of all of a certain type of document that came in during a certain period of time that usally spans a quarterly period. So typically there are three months in the query. I would like a subgroup that breaks the numbers up in the 3 month period by each month.
So if a total of 150 docs moved in 3 months it shows 50 moved the first month, 50 the second, etc. I believe this is grouping by month. I have not been able to determine the criteria for getting the query to group by a single month as well as the three months period being grouped by document type. Here is what I have so far.
SELECT ctblDocuments.DocTypeID, tlkpDocTypeList.DocType, Count(ctblDocuments.DateIn) AS CountOfDateIn FROM ctblDocuments INNER JOIN tlkpDocTypeList ON ctblDocuments.DocTypeID = tlkpDocTypeList.DocTypeID WHERE (((ctblDocuments.DateIn) Between #7/1/2005# And #9/30/2005#)) GROUP BY ctblDocuments.DocTypeID, tlkpDocTypeList.DocType ORDER BY tlkpDocTypeList.DocType;
I currently have a form that creates a report based on a query that takes in a start date and an end date. What I would like to do is to create comboboxes that will let the user choose a month and year for the report. Is there a simple way to choose an entire month in access? I've tried using the Month() function to no avail.
The SQL statement for where I select the start and end date is below:
WHERE ((([tblData].[EntryDate]) Between [Forms]![frmByRange]![BeginningDate] And [Forms]![frmByRange]![EndingDate])
I need to enter workload counts for 10 people, and it is done on a monthly basis. So I have a table of Months (Jan-Dec), a table of names, and a joined table with the months, names and a field for the workload counts.
I would like to make a form where I could select the month and all the names show up so I could go and enter the counts for everyone at the same time. I've attached a diagram to show what it would look like
I have been searching through the forum to see how I can do a query that does subtotals, but to much to my avail there has been nothing that specifically answers my question. So here it is. If I have two tables say one called A and the other B. Now in A there are two fields that I want to use and these are item and quantity (The quantity is the number of that item). In the other table I want to use the field rate. Both tables are related by the item. How do I do a subtotal query that gives me the subtotal for an item i.e. rate * quantity, and shows the information in the specific format:
This helps tally Sick day and Half Sick Days for me, but if they are a year old, then they should not be counted. Once I got the field to say "Other" I thought I was home free, but it is still counting the sick days.
I know it's probably still getting the value from the qryVacation, but how do I have it so it just subtotals off the form iteself?
I have a form were I got the corrent fields: date, account, ammount; the account field is a numeric field. What I am trying to do is to have a extra field on the same form wich will give me the total ammount for the relative account as soon as I have inserted the 3 data. What I don't knowis if I need a query to extract all the ammount for the relative account shown on the field "Account" or it can be done in a easyer way!!!
I have a code in my 97 Access db that exports a query in Access to Excel. The code also includes all the formatting needed for the Excel sheet including subtotal rows. What I would like it to do is automatically format the subtotal rows to be a different color so they stand out. This is part of my code:
Have a table containing a column called "BOC" (Budget Object Code) that contains approximately 85 different 4 digit codes, with one selected for each row. (See sample below). Table has 30,000 rows of data. What I need to do is run a query that will give me a subtotal dollar amount for each unique BOC, and then display the results in descending order for both Obligations and Expenditures. Any ideas??
I haven't been on for a while. so hello everybody.
I am trying to keep a subtotal in a table. there is an index (actually its formatted text, but its a number and i can converted i.e. cdbl()). I was thinking that i should create one query with dmax in one column and and then use another one to show the value plus the previous value.
Hi all, I have a subform within my form for quotations (see attached).
A user will manually type in "Repair Cost" and "Accessory Cost". The subtotal field is calculated using this expression =Nz([Repair Cost])+Nz([Acc Cost]) in the Control Source box This is all Ok.
I want to subtotal these on the main form then work out the VAT and final total.
My problem is, No matter what expression I seem to use on the main forms subtotal, it will not work.
Hi, I am trying to work out away of subtotalling a field in access, similar to that of the subtotal function in excel, Basically my table is structured so i have a store number column and a transaction number column so as an example my data looks like this: Store noTransaction Num 1234522664499 1234522664499 1234533775599 1234533775599 1234544886600 12345611227755 12345622449955 12345677556699 The transaction number is made up of several other columns and basically represents an item sold by an assistant at a certain time - if the asst sold 2 items i will get the same transaction number appear twice, if they only sold 1 item it will only appear once. I want to be able to count the transactions by store so i end up with a result like this (based on the above data) Store no Count of Trans No 12345 3 123456 3
i was wondering if there is any easy to follow information anywhere or examples with how to do..Specifically on creating an invoice??
The form would need customer details then a subform with the qty, price per unit, extended price then a section on the form which works out the subtotal adds it to freight charges then gst and finally the total.These all automatically then being placed in there appropriate tables..i Managed to figure this out once before but i cant for the life of me figure it out again..I have spent weeks trying any clues???
I have a set of records that contain the following attributes;R_Name, Cost, StartDate, EndDateI need to subtotal the cost for each R_Name by year. The start and end date attributes can span several years and I need the correct cost for partial years.
find the attached MS access DB for INVOICE printing . I am facing two issues there .
1. {form INVOICE } sub-total must be Calculated from line total on {INVOICE DATA Subform} , I know this can be accomplished by adding a new field at sub-form and calling that value to main form's sub-total field .
2. And the real issue is i want to save this value from subtotal on form INVOICE to table field name subtotal .
3.I wish to make a reporting format for each INVOICE NUMBER , whenever report is being generated , the report shows more than 1 invoice details .
I have set up a basic database that has a customer table , orders table , join table and products table. I have created an invoice form that has customer details and then in a subform has the order ID and product ID , name , price and quantity.I have a text box on the form to which I am trying to output the sub total to when the form is launched(quantity X price for the current order).I was trying to create a query that will return the subtotal for an order.I had the query calculating the subtotal but I was then trying to get t the cost based on the order ID. I was then attempting to get the Order ID for the query from the open form by adding this to the criteria in the query:
How to keep Access from including the detail line items in my report total?
Below is a quick image of part of the report.
The report is compiled from two tables. The first table has detail information on each line, in four columns:
State Name Amount Quantity (Not used)
AL Store 1 313.94 60 AL Store 2 12.59 1 AZ Store 3 576.45 90 CO Store 4 172.22 24 CO Store 5 502.25 88
The 2nd table is a summary by state with three columns:
State Amount Quantity (Not used) AL 326.53 61 AZ 576.45 90 CO 674.47 112
I'm trying to make the report group by state, then name, amount and a subtotal for each state. This all works fine in the report.
I want to also add the amount field from the 2nd table (State Subtotals) to the report just for reference. I've managed to make this work as well. (See State Total on image)
The problem that I'm having is that when I try to do a final total of the State Total column in the report footer, it seems to add the (State Subtotals) amount field on every detail line (See red numbers in picture. they are not actually on the report, but Access thinks they are and includes them in the final total).
So far I've tried different joins, hiding various fields, sub reports, ect. I just can't figure out how to stop Access from calculating the detail lines.