Parameter Query Using Dates Leading To Subtotals
Nov 4, 2011
I have a list of coded transactions in column A, Amounts in B and Dates in C.
I want to show subtotal for all amounts with the same codes in D
I can do this simply without the date parameters, but I need to give timeframes.
When I add the [From] and [Until] questions it then shows the amounts next to each date even though the date column is hidden.
I'm aiming for the subtotal of amounts for same codes between given dates:
Example
ABB 100 1/6/11
ABB 100 6/6/11
ABB 150 1/8/11
ABC 50 1/7/11
ABR 50 2/8/11
ABJ 50 1/5/11
If I enter 1/6/11 and 30/6/11 I would expect ABB to show 200 total as 150 is after that date and so not counted.
View Replies
ADVERTISEMENT
Mar 13, 2006
Hi
I've tried searching the forum but cannot find anything relevant.
My problem is I have a combo box in an access form which displays dates from a table. The table field and the combo box format is set to short date. When the value changes the data on the form body changes relevant to the selected date. This works fine except when the date has a leading zero (e.g. 06/03/2006) in which case no data is displayed. I have a variable of type date which takes the value of the combo box and then an sql statement which uses this variable. I have used breakpoints and foudn that the variable has a value of (e.g.) 06/03/2006 and this is how the date is stored in my table. I'm wondering if I'll need to do something with totext, turn the date into a string and make sure it has the leading zero?
Do you know if this is a known problem with access/access forms? If anyone can point me in the right direction as to why the code has a problem with leading zeros I would greatly appreciate it.
Thanks in advance.
My code is:
Private Sub cboDate_Change()
Dim datTemp As Date
datTemp = cboDate.Value
''cboDate.SetFocus
Form.SetFocus
Form.RecordSource = "SELECT [staff].[staff_id], [staff].[lastname], [staff].[forename], [staff].[bocs_name], [staff].[headset], [staff].[phone_uic], [staff].[bocs_uic], [staff].[group], [staff].[type], [staff].[email], shifts.* FROM staff LEFT JOIN shifts ON [staff].[staff_id]=[shifts].[staff_id] WHERE [staff].[type] <> 'Leaders' AND (((shifts.date) Is Null Or (shifts.date)= #" & datTemp & "#))"
End Sub
View 2 Replies
View Related
Apr 4, 2008
Good Evening Everybody,
I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:
ID: Auto Number
Area: Text
Equipment:Text
EquipSrlNo:Text
EquipLocalNo:Text
MOPNo:Number
DateDone:Date/Time
MOPPeriodicity:Text
I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???
SQL
SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity
FROM [Main Data]
WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));
Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.
Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?
Look forward to your response(s)
Best Regards
CarolW
View 4 Replies
View Related
Apr 28, 2006
I have a report that has an underlying query that asks for Start Date and End Date.
Is there anyway that I can get what the user inputs into the box to be put into the Page header of the report?
ie: user enters into the parameters
[Start Date] 01/01/2006
[End Date] 04/04/2006
Then when the report displays it says
Report for the period 01/01/2006 to 04/04/2006
Thank you for your time
View 4 Replies
View Related
Feb 3, 2014
I have a parameter that I need to get a table contents between dates. In the query:
Birthdate: XXTable: criteria as follows: Between [Enter Start Date] and [Enter End Date:]
When run it gives me the Error - ! This expression is type incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables
I have tried almost everything. The formatting of the Birthdate is x/x/xxxx or shortdate. Will this affect the input thus affecting the outcome of the query.
View 4 Replies
View Related
Apr 12, 2013
I have a query with the following fields
*Date From
*Date Till
I have got the following formulas for each
Date From - >=[ENTER Start Date]
Date Till - <=[Enter End]
The results work fine to show which vehicles are booked between these dates, the only thing is I want to be able to show the vehicles that are not booked for the given dates in the parameter query is this possible?
View 2 Replies
View Related
Sep 17, 2013
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
View 2 Replies
View Related
Apr 5, 2006
Hi there - I've looked at quite a few posts and although some of them seem to start of heading in the right direction there doesn't appear to be anything that quite hits the spot for what I'm looking for.
I'm trying to create a query that creates the following (see spreadsheet as typing it out didn't help.)
I hope that's clear - any help appreciated.
Cheers,
Mark
View 1 Replies
View Related
Nov 19, 2013
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies
View Related
Nov 19, 2013
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies
View Related
Jun 14, 2005
Hey folks,
sorry if this is an oldy but.........!!!!!!!!!
I need to insert the parameter dates for a query into a report. I have tried all the methods I can find but none seem to work. I think its to do with the way my queries are structured.
I have a basic query (q1)! Based on this is a grouping query (q2)! Based on this grouping query is my report. I have set up a form prompting for the parameter dates. This all works ok and my report displays the correct data (between the dates entered). The problem is that I cannot get the report to show the parameter dates.
Help. Its driving me nuts.
Billsack
View 1 Replies
View Related
Jan 16, 2008
I have created a db with 10 linked tables from 10 db's.
I then build 10 queries associated to these linked tables.
The 10 queries filter data by start date and end date.
The data generated from each query is then exported to excel (each query has its own sheet in the woorkbook).
Dim Output_Path_And_File As String
Output_Path_And_File = "C:" & "Production Labor hrs Querie.xls"
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs 3-WAY", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-ACV", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-EAP", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-EVMV", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-PFE", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-propor", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-SEGR", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-TBO", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-VCA", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-VFS", Output_Path_And_File, False, ""
How can I set these queries up by typing the start date and end date only once rather than 10 times?
Thank you
View 5 Replies
View Related
May 22, 2007
Greetings all
Came across a problem and wanted to see if anyone else has seen this or knows the cause.
I am working in Access 2003.
I have two tables stored in a backend. One table updates the other. The database I inherited was doing this via the copyobject. I wanted to use a del query to delete recs in target table then an append query to update from source table to target table.
Both tables have data fields named in the following format: 01/2007, 02/2007, etc.
If I write a select query and save it, it works fine. However, if I write an Append query, the INSERT clause chops off the leading 0 of all fields upon save which causes the query to not work correctly. Anyone know why this happens?
TIA
Tim
View 8 Replies
View Related
Jul 9, 2007
Hi everyone,
I have 2 linked tables from 2 different databases, there is a common field in both: “CORP_NBR” I need this field in order to create a join, but the problem is that in one table this field stores leading zeros and in the other table it doesn’t. I can see in design view that this field is text type in both tables. I cannot change field types because I don’t own the objects.
Is there a way or a function that I can add in my join query to ignore leading zeros? :confused:
I appreciate your help
View 6 Replies
View Related
Feb 15, 2008
Not sure if the question belongs in queries or reports. I've a list of product codes that we produce daily, weekly, monthly. Some repeat several times within a week or month. I want to list them in order by cost. I can do that, but when they repeat, I wanted to combine them into one subtotal, then get sorted by cost with the other product codes.
Right now, I'm dumping the table into Excel and doing it manually. I sometimes have 7 lines of the same product code. I insert a line and subtotal them by their product code. Then, I delete the data leaving the subtotal line. I then can sort them with the other subtotal and other once product codes.
:-[ I'm hoping this is an easy answer. I'm not good with Access. It's turned out to more of a trial and error thing for me.
Thanks
Laura
View 2 Replies
View Related
Aug 2, 2006
Hi,
I'm trying to produce a subtotal in a query to compare it to another field to determine whether to include the record or not.
For example, in my table I'd have,
ProductA 3(units) Lot1
ProductA 4(units) Lot2
ProductB 2(units) Lot3
ProductA would also have a minimum inventory level of 6 in another table. So the parameter I would like to create would sum all the different lots of ProductA and compare it to the MinLevel entry.
Any ideas?
View 3 Replies
View Related
Nov 15, 2005
I have a report that has the following categories:
Parent--Unit--Month--Spend--Expected--Rate of Expected
Now i have it summing the spend by Parent. I would like it to sum by Parent by month, is this at all possible?
View 1 Replies
View Related
Dec 1, 2005
I am creating a Sales History Report. It displays products sold by customer by Sales Rep. The groupings are Sales Rep, Customer, and Product. I am hiding the detail section which contains invoice line item records. So the report shows the product subtotal by Company. The problem I have is I need to see the product subtotals in the Sales Rep and Report Total sections also. So the report would look something like this:
Sales Rep A
Customer A
Product A
Product B
Product C
Customer B
Product A
Product B
Product C
Sales Rep A Total
Product A
Product B
Product C
Sales Rep B
Customer R
Product A
Product B
Product C
Customer S
Product A
Product B
Product C
Sales Rep B Total
Product A
Product B
Product C
Report Total
Product A
Product B
Product C
Is there a way to accomplish this without doing sub-reports?
View 3 Replies
View Related
Apr 17, 2007
I am looking to do some calculations in a form that can be output to a report.
Basically within the form i have 4 controls as well as the others
Tables
Benches
Chairs
Other Equipment
These controls are bound to a table.
The first 3 controls need to have have fixed costs 2.00, 1.50 and 1.00 respectively. The last needs to be manual input as it can be different dependant on the items in the memo box.
I then need each one to calculate separately to give me a sub total then add all together to give a total
This total needs output to a report for sending out a bill.
I currently have the 4 controls with 4 unbound textboxes beside them for the subtotals:
TablesTotal
BenchesTotal
ChairsTotal
OtherTotal
4 unbound textboxes for the fixed amount and manual input amount (maybe this is where i'm going wrong)
TableCost
BenchCost
ChairCost
OtherCost
and then one more unbound textbox for the total:
TotalTotal
Do i need to create a table for the fixed costs? If so whats the best way to set this up. What should i be binding textboxes too if anything?
Hope this makes sense.
Thanks in advance
View 2 Replies
View Related
Dec 12, 2013
I decided to link Excel files and all is running fine except when I run a query on the file I get about 1,041 blank rows before my data is displayed in the query's datasheet view or any report based upon this query... the data seems good but why the leading blank rows? The data in the linked Excel sheet does not look like this and another Excel file's query looks fine and they seem set up identically!
View 2 Replies
View Related
Mar 9, 2005
Hi,
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers]
but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
Thanks very much!
View 3 Replies
View Related
Jul 12, 2005
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies
View Related
Nov 9, 2006
Hi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
View 2 Replies
View Related
Mar 18, 2008
I got a field in my client table which stores their phone numbers, ive selected number for data type but it removes the leading zeros.
How do i ensure the full number which has been entered remains?
View 7 Replies
View Related
Aug 12, 2005
Hi all,
I have a column in my DB that is actually for NASCAR car numbers. Some of which use leading zero's and some don't. i.e. There is a 01 car, a 1 car, but also an 8, and a 08, etc. If I use the Number data type, I lose the leading zero's, but it can be sorted by car number easily. If I use text, it displays the number properly, but the sorting is "ICK." Is there a way to get the best of both worlds?
Thanks,
View 2 Replies
View Related
Dec 18, 2006
Hey guys, I'm new, and I am sure this has been asked 10000 times before. I have tried finding the answer, but cannot.
Anyway...
I have an item number field [Serial_Number] that is pulling its information from a company database table. All item numbers should be 6 digits. For example:
1764 should be 001764
98 should be 000098
01888 should be 001888
Make sense?
I cannot make changes to the table, so really I just need to format Access' output to have the leading O's. I tried an append query, but that did not work because I do not have access to change anything (I could have not done it right).
It seems like this should be a simple answer.
Thank you!
Scott
View 2 Replies
View Related