Same Data, Different Criteria, One Report!?

Aug 5, 2005

I am trying to create a tax report for my business and am not very familiar with Access beyong making basic reports from a single table and query.

Here's the problem:
I want to report total costs for my unsold inventory purchased and worked on prior to 2005. From that I want to be able to subtract the total costs of sold inventory purchased and worked on prior to 2005. Yes, all in one report. Is this even possible?

My report contains these columns from a single table-
inventory #, purchase date, cost, sold or unsold (yes or no check box), 3 columns of additional costs and 3 columns of cost dates. Also a couple other confusing columns of costs that were split between myself and co-purchasers. These include a column of negative costs, the date paid, the balance paid to the co-purchaser, and the date of the balance paid.

I have been sorting by purchase date prior to 2005 and unsold.
The problem is I have no idea how to include the values of the sold inventory in with this report of all the unsold inventory costs without creating a massive report with all my inventory from which I can not sum the columns properly.
The other problem is that when I sort by purchase date I get cost dates from after 2005. I can limit the criteria for one of the date columns, but not all.

My questions are:
How do I included both sold and unsold inventory information in a report and sort by inventory # without simply listing all the information together in one column by inventory #? Can I do the calcualtions I want to do in one report?

How can I limit all of the date columns to show only before 2005? If I make each of the date columns with criteria AND >#12/31/2004#, they limit down to only a few records because many of the date fields in my database are empty. If I use the OR criteria it doesn't work.

I actually have many other questions but I suppose those are the biggest ones.


I know that this is a very specific topic, so if anyone has suggestions as to great resources for Access help (ie. books, tutorials, etc.) particularly with creating tax or inventory reports from a single table, that would be extremely helpful.
Anything would be extremely helpful!! I'm sunk.

View Replies


ADVERTISEMENT

Queries :: Using Field Data On Form As Criteria In Report

Jul 12, 2013

I have a form. On the form I have a button to run a report.The query associated with the report selects all records within a unit (field name (Unit) is used as the selection criteria).Rather than type in the unit name when the report is run, I want to select the unit that is currently shown on the form.

View 4 Replies View Related

How To Query Data For Specific Criteria - Criteria Help

Aug 20, 2007

I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?

StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%

View 2 Replies View Related

Report Criteria

Apr 26, 2007

I require to select records to print based on a date range selection
When I use BETWEEN I dont get the start date or end date records
I only displays the records in between which I suppose sounds logical.

How can I get my report to include all the records I select.

Could this be anything to do with the format of the dates being used ?

Thanks
:confused:

View 1 Replies View Related

Report By 2 Criteria

Sep 19, 2004

I would like to track the deals made per person per day as well as each person and the whole company overall. I was able to make this work with two reports, but I have a feeling I can get this done with one. Well, I tried to get it done with one and I couldn't get it to work. The problem was that I was trying to open the report based first on the RepID (in tblSalesReps, and in the current form frmSalesReps) and the close date of the deal ([tblJobs].[CloseDate]) attempting to use the following code:

Code: Dim stDocName As StringDim today As Varianttoday = DatestDocName = "rptDealTrackerIndiv" DoCmd.OpenReport stDocName, acPreview, , _(("[RepID]=" & RepID) And ("[tblJobs.CloseDate]=" & today) Exit_cmdOpenrptDealTracker_Click:Exit SubErr_cmdOpenrptDealTracker_Click:MsgBox Err.DescriptionResume Exit_cmdOpenrptDealTracker_Click

which is pretty much a simple modification of the wizard button that Access provides you to open a form (I'm providing the WHERE statement). Whenever I tried this (and several variations thereof, before I said to hell with it) I received a 'Type mismatch' error, an 'Invalid bracketing of [tblJobs.CloseDate]' error, and an 'Object needed' error. All I want is for the report to display the deals that RepID X made today. Why is this so hard, and why did I have to make two reports? Or, is this even possible with one report?

View 5 Replies View Related

Report Criteria

Jul 14, 2005

I am creating a report from a crosstab query but need to add criteria to the report. I have created a form to enter the criteria and then added the following code:

Private Sub ViewRpt_Click()
On Error GoTo Err_ViewRpt_Click

Dim myCriteria
Dim rptNameasString
rptname = "RptWeeklySales"
myWeek = Me.[PickWeek]
myLocFrom = Me.[StoreLocFromCombo]

DoCmd.OpenReport rptname, acViewPreview, , "[Week]=" & myWeek & "and" & "[Store Location]=" & myLocFrom

With just the myWeek criteria it was working fine. When I try to add the myLocFrom I keep getting an error message that "Kentwood" is not a valid field name or expression. Kentwood is from the StoreLocFromCombo control.

Any ideas where the problem is?

I also need to add the LocThru but I am trying to take one step at a time.


Valerie

View 3 Replies View Related

Query Criteria For A Report

Nov 30, 2012

I have a client that I made a database for with Purchase Orders. He wants to call them purchase Orders instead of Invoices (whatever).I have two reports created, Sales by Customer and Sales by Account Manager. I have two fields on the reports menu for Start Date and End Date.

The queries for each of these reports looks at the Start Date and End Date just fine. Now, the client wants optional criteria of selecting all customers or a specific customer, or all Account Managers or a specific Account Manager.

the fields are CustomerID and AccountManagerID. What would the criteria code look like for each to give the user a choice as specified above?

View 2 Replies View Related

Forms :: Open Report Button To Select Data From 7 List Boxes And Present In A Report?

Aug 13, 2015

I have a form with 7 List boxes linked to 7 Query's which in turn are linked to a table. Each list box if for a particular trade.

I am trying to select a person or persons from each List box and then have them sent to a report. I have Code to do one list box, but do not know how to link all boxes with code to a 'Open report' button.

The code I am using is as follows:-

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made

[code]....

View 9 Replies View Related

Images In Report - Limit To Name Criteria

Aug 16, 2006

hi
i have created a form bringing together 3 queries. one of the queries contains images the other 2 queries are text/data information.
i am now creating a report to print out the queries. each query is made into a report and then combined with a main report [2 of the queries are subreports]. the code i have used to link the images so that i get a unique image with every record is

me.[cempic2].picture = me![combined_mage_path]

however when i print the report if the record contains more then 1 image i get the following [screenshot 2].
what i need to do is to limit the image print out for each record to the first image only. the only common naming criteria for all my images is 001.jpg, 002. jpg and so on
what i need to do is to show only the image for each record that ends with the criteria *001.jpg. how would i do this in VB?
thanks

View 1 Replies View Related

Criteria For Report Using The SendObject Method

Sep 5, 2007

I am using a macro and the SendObject method to email a snapshot of a report to 29 different program coodinators(which is working fine). Using the macro i have 29 exact reprorts except for the different criteria in the ProgramID field for each program. I would like to get away from the macro and code the SendObject method and specify the criteria for the ProgramID field for a single report. I will most likely use a SelectCase statement for the 29 different programs. Can anyone tell me how to set the varible criteria on a single report using the SendObject method.

View 1 Replies View Related

Reports :: How To Filter Report Using 3 Criteria

Jan 18, 2015

I have following table, forms and reports in my access database:

Tables:
1- maintbl
Query:
1- mainquery based on maintbl
Forms:
1- Input or mainform based on mainquery
2- unbound form: to Filter report
Report:
1-mainreport based on mainquery

Objective:

I want to filter report on followings:
Name(Text)
Month(Text)
Year (Number)

I have 3 combo boxes on ubound form and want to filter report based on three above mentioned criterias.

View 1 Replies View Related

Reports :: Hide Report Footer Based On Report Data?

Dec 21, 2014

How do I hide the report footer based on the report's data ?

I'm trying to hide if number of users = 1

The report's data is a query built inside the report's RecordSource, not a self standing query.

View 10 Replies View Related

Form/Report/Query Aka How To Change Criteria Value

Feb 14, 2006

I have built a database with only my department in mind that tracks three types of documents; Which works fine. Now some of my fellow department heads desire to use what I have built.

This is not a problem as their data structure is the same. The difficulty lies in changing the 30 queries. In the queries I hard coded my department number in the receiver (department field) criteria.

Additionally, my HQ is requesting me to perform some analysis on the other department's data. So other than manually changing the department number each time; Can I use a form or report to modify these 30 queries? I attempted to use a combo box but it would not hold the value when I closed the form.

I saw this thread today, http://www.access-programmers.co.uk/forums/showthread.php?t=102036 , but am not sure it will do what I want.

Suggestions welcomed.
Gunner...:confused:

View 8 Replies View Related

Make Text BOLD On A Report (criteria).

Feb 5, 2008

Not sure if this is possible but here goes (even if I make a fool of myself for asking).

I have a report that lists data based on criteria set in a form. The main criteria is Month (Month4Report) and Year (Year4Report).

The report lists all jobs that started that month and also any jobs that had work done during that month - even if they started in the previous month.

What I would like to do is highlight any job that has an actual start date prior to the current searched month (Month4Report). By making it bold or other way.

Is this possible?

Thanks.

Martin

View 8 Replies View Related

Reports :: Report Based On Multiple Criteria

Jul 27, 2015

I am in the learning phase for MS Access reports. I have a datasheet which contains the employee daily activities.I need a report when either or both criteria is selected. For ex..criteria1 is Start date and End Date and criteria2 is Employee Name.Output of the report should be based on the selection of the criteria ,as I said either or both.

View 3 Replies View Related

Queries :: Textbox In Report As Query Criteria

Jun 3, 2014

I have a simple report which i open from a navigation form, i have a textbox in the report i wish to use as criteria, on start the report will be blank until i enter a name into the textbox and requery. However im not sure how to link this and how to stop access asking for this information before the report is opened?

View 5 Replies View Related

Creating Labels Report With (TimesToRepeatRecord) Criteria

Dec 7, 2014

In my database (db) I have first create simple report which can print label for certain article in number of times user wants to. That part of db is marked as OldLabels in db that I have attached below.

Now, I plan to update this code via form NewLabels so user could choose more than one article and for each article he can define 'TimesToRepeatRecord' number. Unfortunately, I am little confused how to achieve this. This is the old code (from Report) which have done most of the work.

Code:
Option Explicit
Dim intPrintCounter As Integer
Dim intNumberRepeats As Integer
Private Sub Report_Open(Cancel As Integer)
intPrintCounter = 1
intNumberRepeats = Forms!VPDEKLARACIJE!TimesToRepeatRecord

[Code] ....

I have also attached db named "Demo" so you could see the issue.

[URL] .....

View 1 Replies View Related

Sub Report - Data Stops Appearing In Sub Report After Page 2?

Dec 21, 2012

I have an asset data base to generate individual asset detail reports with a sub report on the same page listing similar assets from a separate query. I have set the master and child fields, one to many. This works perfectly for the first two assets (pages), however the sub report stops showing data on the third asset (page).

View 13 Replies View Related

To Display Parameter Query Criteria On Report Even For Nil Results?

Nov 10, 2005

Hi,

Need advise on how to display on my report the criteria that i had specified in the parameter query even if the result is nil.

How can this be done??

Thanks!

View 5 Replies View Related

Print Report From Form Based On Multiple Criteria?

Nov 17, 2006

Hi,

I have the following code which i found on another thread on this forum (thanks to original author) which is attached to the On Click of a button which prints the report corresponding to the details displayed in the form.

Dim strCriterion As String
Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me![ReferenceNumber]) Then

strMsg = "You cannot print a Blank Form!!."
strTitle = "Print Error"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle

Exit Sub
End If

If Me.Dirty Then
Me.Dirty = False
End If

strCriterion = "[ReferenceNumber]=" & Me![ReferenceNumber]
DoCmd.OpenReport "DoC Certificate", acViewNormal, , strCriterion

This works fine, however, i need to be able to select the report based on more than 1 criteria. For example, the Reference Number can be repeated but is distinguishable from each other by an Issue Number i.e. ReferenceNumber = 93, Issue 1 or 2 etc. At present when i run the above it prints all versions of, in this case, reference number 93, which given that each report is only a single page isn't a show-stopper but it would be nice to have it working as i would like.

I have tried adding to the strCriterion line such as strCriterion = "[ReferenceNumber]=" & Me![ReferenceNumber] and "[IssueNumber]=" & Me![IssueNumber] but no joy. I have tried bracketing the whole line and variations thereof, again no joy.

Can anybody tell if what i am attempting to do is possible and if so how do i go about it?

I have tried the above coding using MasterID which is the Autonumber PK but it produces an "Enter Parameter Value" box for MasterID. Obvioulsy if i can get it to work for the Autonumber then my problem goes away but i can't seem to figure out why it works for Reference Number (Number) and not MasterId (Autonumber)?

Regards

Alan

View 5 Replies View Related

Modules & VBA :: Filtering Form / Report - QueryDef Criteria Using OR

May 7, 2015

I have a form with 3 combo boxes that filter another form/report. The first combo (cboByCategory) contains options from 2 different fields within the same table. Before I added this add'l piece of code, all 3 combos worked fine. I am not getting error messages, it just does not filter on the other 2 combo boxes - cboDiv and cboGender.

Code:
Private Sub cmdModifyRecords_Click()
On Error GoTo Err_cmdModifyRecords_Click
Dim stDocName As String
Dim strFilter As String
Dim stLinkCriteria As String
stDocName = "Modify_OpenItems"

[Code] .....

View 5 Replies View Related

Forms :: Use Form To Get Criteria For Query To Create Report

Apr 1, 2013

I want to create a form that allows a user to enter criteria that will be passed to an existing make table query. suggestions on a user friendly book on Access 2010 programming, I'd be really interested. I'd like to be able to do more with Access 2010.

View 3 Replies View Related

Reports :: Displaying Query Criteria In A Report Header

Apr 22, 2014

I can't get my reports' unbound field's to display the criteria parameter I enter when report is opened. I have done this before but not in Access 2010.

In my parameter, I do have a long string inside the brackets (e.g. =[Enter START Date "1/1/14" or Leave Ranges Blank to show ALL]). I am wondering if this or the quotes inside the brackets are causing it not to work.

View 10 Replies View Related

Modules & VBA :: Open Report Criteria - Enter Parameter Value

Aug 5, 2013

I have one form which have two multiselect listboxes. They work, but every time when I choose id_organizacija from listbox popup enter parameter value comes up.

I am already tried to put id_organizacija in [ ] but that didnt solve problem.

Code:
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
Set ctl = Me.lstOrganizacija

[Code] ....

View 12 Replies View Related

Modules & VBA :: Creating Label Report With (TimesToRepeatRecord) Criteria

Dec 7, 2014

In my database (db) I have first create simple report which can print label for certain article in number of times user wants to. That part of db is marked as OldLabels in db that I have attached bellow.

Now, I plan to update this code via form NewLabels so user could choose more than one article and for each article he can define 'TimesToRepeatRecord' number. Unfortunately, I am little confused how to achieve this.. This is the old code (from Report) which have done most of the work

Code:
Option Explicit
Dim intPrintCounter As Integer
Dim intNumberRepeats As Integer
Private Sub Report_Open(Cancel As Integer)
intPrintCounter = 1
intNumberRepeats = Forms!VPDEKLARACIJE!TimesToRepeatRecord

[Code] .....

I have also attached db named "Demo" so you could see the issue. [URL] .....

View 1 Replies View Related

Reports :: Choosing Which Report To Run Based On Text Box Criteria?

Nov 10, 2014

I have a form with a command button that runs a report. I have been asked to modify things and if the data in the text box is "A", run report "A". If the data is "B", run report "B" and so on.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved