Help On A Report With Dates

Nov 6, 2004

I have a tblCatalogue with fields packetNo (as integer), sendDate, shipDate and deliverDate (as date).
How can I make a report to show for each month how many packets I have sent, how many I shipped and how many I delivered ?

I have tried 3 separate queries using QBE
FIRST QUERY
the first field defined as GroupA: Format([tblCatalogue].[sendDate];"mmm yyyy")
and the second field defdined packetNo with Total set to Count

SECOND QUERY
the first field defined as GroupB: Format([tblCatalogue].[shipDate];"mmm yyyy")
and the second field defdined packetNo with Total set to Count

AND THIRD QUERY
the first field defined as GroupC: Format([tblCatalogue].[deliverDate];"mmm yyyy")
and the second field defdined packetNo with Total set to Count

Each of the queries gives me what I want for each month, like :
SEP 2004 20
OCT 2004 15
NOV 2004 3

What I really need though is to have something like:
Packets sent Packets Delivered Packets Shipped
SEP 2004 20 6 25
OCT 2004 15 8 30
NOV 2004 3 5 15

I have tried to achieve what I need using the following Function, but ended nowhere:

Public resultMonth(12) As String

Public Function Send() As Long
Dim sqlSend As String
Dim rsSend As Recordset
Dim dbSend As Database
Dim sendDate As Date
Dim monthSend, dayFrom, dayTo As Integer

On Error Resume Next
For monthStat = 1 To 12
If monthStat = (1 Or 3 Or 5 Or 7 Or 8 Or 10 Or 12) Then
dayTo = 31
ElseIf monthStat = (4 Or 6 Or 9 Or 11) Then
dayTo = 30
ElseIf (monthStat = 2 And Int(Year(Now) / 4) = (Year(Now) / 4)) Then
dayTo = 28
ElseIf (monthStat = 2 And Int(Year(Now) / 4) <> (Year(Now) / 4)) Then
dayTo = 29
End If
Set dbSend = CurrentDb
sqlStat = " SELECT compul FROM tblCatalogue " & _
" WHERE sendDate BETWEEN # " & Year(Now) & " / " & monthSend & " / 01 # " & _
" AND # " & Year(Now) & " / " & monthSend & " / " & dayTo & " # "
Set rsSend = dbStat.OpenRecordset(sqlSend, dbOpenSnapshot)
If Not rsSend.EOF Then
rsSend.MoveLast
If rsSend.RecordCount > 0 Then
resultMonth(monthSend) = rsSend.RecordCount
End If
End If
Next monthSend
End Function

Public Function test()
Send()For I = 1 To 12
MsgBox (I & " = " & resultMonth(I))
Next I
End Function

The above code gives me the correct numbers but again I cannot get a report with all packets send, shipped or delivered for each month of the year.

May be I am on the wrong track. Can I have some guidance how to achieve my report ?

View Replies


ADVERTISEMENT

Filtering Dates On Report

Oct 6, 2006

hi!
i tried searching the forum for a possible solution to my problem. unfortunately, no luck so far.

Here's my problem.
i'm working with a linked table in access with a date field (service_dt) formatted as a text. if i open the table, the date shows up as 1-Jan-2006. i use this table to populate a report.

im trying to filter the report so that it would show records that have a specific date range.
here's what i write on the filter of the report:
service_dt Between #01/01/2006# and #12/31/2006#
unfortunately, its not filtering properly.
i would appreciate any thoughts.

thanks.

View 3 Replies View Related

Chosing Dates For Report

Dec 7, 2006

Hi,

I need item name, total quantity sold, total quantitiy delivered.

I need some thing that lets me put in two dates, the start date and end date.

Th Sql code for some reason dont' not give me the right result. It must be wrong!!!:rolleyes: Help me fix it!!!


SELECT DeliveryAA.itemnameAA, Sum(DeliveryAA.delqtyAA) AS SumOfdelqtyAA, Sum(ItemSaleAA.saleqtyAA) AS SumOfsaleqtyAA, DeliveryAA.timedateAA
FROM SupplierAA INNER JOIN (SaleAA INNER JOIN ((DepartmentAA INNER JOIN DeliveryAA ON DepartmentAA.deptnameAA = DeliveryAA.deptnameAA) INNER JOIN ItemSaleAA ON DepartmentAA.deptnameAA = ItemSaleAA.deptnameAA) ON SaleAA.salenoAA = ItemSaleAA.salenoAA) ON SupplierAA.splnoAA = DeliveryAA.splnoAA
GROUP BY DeliveryAA.itemnameAA, DeliveryAA.timedateAA
HAVING (((DeliveryAA.timedateAA) Between #1/1/2004# And #12/31/2004#));

View 1 Replies View Related

Chosing Dates For Report

Dec 7, 2006

Hi,

I need item name, total quantity sold, total quantitiy delivered.

I need some thing that lets me put in two dates, the start date and end date.

Th Sql code for some reason dont' not give me the right result. It must be wrong!!!:rolleyes: Help me fix it!!!


SELECT DeliveryAA.itemnameAA, Sum(DeliveryAA.delqtyAA) AS SumOfdelqtyAA, Sum(ItemSaleAA.saleqtyAA) AS SumOfsaleqtyAA, DeliveryAA.timedateAA
FROM SupplierAA INNER JOIN (SaleAA INNER JOIN ((DepartmentAA INNER JOIN DeliveryAA ON DepartmentAA.deptnameAA = DeliveryAA.deptnameAA) INNER JOIN ItemSaleAA ON DepartmentAA.deptnameAA = ItemSaleAA.deptnameAA) ON SaleAA.salenoAA = ItemSaleAA.salenoAA) ON SupplierAA.splnoAA = DeliveryAA.splnoAA
GROUP BY DeliveryAA.itemnameAA, DeliveryAA.timedateAA
HAVING (((DeliveryAA.timedateAA) Between #1/1/2004# And #12/31/2004#));

View 3 Replies View Related

Help With Dates, Query And Report?

Apr 11, 2008

I have this pretty difficult thing that I would love to get done.

In table the user can set two dates, the begin date for a period and the end date for that same period. The time between the two dates is the time employee has been working.

Now, I'd need the query to show the first of ALL dates on one person and the LAST date. So, if the person has been working first between 1.1.2008 and 31.1.2008 and then between 1.2.2008 and 29.2.2008, the report should show that the person has been working 1.1.2008 - 29.2.2008.

I've tried all I could think of, but can't figure out how this is done.

Also, I've been wondering if it's possible to make the query show if the employee has been away, as in, if the person hadn't been working for couple weeks, let's say 1.3.2008 and 14.3.2008, but then worked between 15.3.2008 and 31.3.2008, the report would show that the person has been working
1.1.2008 - 29.2.2008
15.3.2008 - 31.3.2008.

Heh, that's whole lot of things to do. I hope that was enough information for someone to be able to help me!

View 4 Replies View Related

How To Count Dates On Report

Aug 25, 2011

I created a query and one of the fields in it is for dates. I need to create a report that will only count how many entries have dates and it shouldn't count those with no/blank dates .

Is there a way to put a criteria in my query for date field? What would be the formula? Or is there a formula that I can put straight to my report that will only count the ones with dates?

View 14 Replies View Related

Print Report In Range Of Dates And More

Jul 12, 2005

I need to print out a report from a start to end date. But theres a catch i also need to print those items from previous months that are not yet resolved. I have a checkbox that tells me if the record has been resolved or not. Is there a way that I can put both the conditions into my query, one for the dates of transactions and the other being all outstanding items no matter what the date.

View 2 Replies View Related

User Defined Dates Into A Report

Oct 3, 2006

HI,

I have a query [CustomerOrdersByDate] that requests user input for [CustomerID], a 'Start Date' and an 'End Date' for the [OrderDate] field.

the Report works fine, however I'd like it to be able to take the Start & End dates the user has inputted when running the report, and make it appear in the Report header
e.g. Orders Received from [CustomerID], Between {user defined}[Enter Start Date] And {user defined}[Enter End Date].

I have added a text box to display the results and played around with a few different expressions, etc... unsuccessfully so far, and as I'm not up to working with VBA code, I would appreciate some help!

View 2 Replies View Related

Insert Parameter Dates In A Report

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

Running A Report By Selecting Dates

Jun 23, 2005

Dear All:

I have created a database to keep track of transcripts that are requested. This database has their names, ID number, date requested(With a pop-up calender), transcript type requested(This is a combobox where the choices are "Official Copy" and "Student Copy") and address.

So far, it works great. What I wish to do is run a report that I can choose the begining date and ending date for a specified month to see the total "Official Copies" and "Student Copies" for a given month.

Any ideas out there?

Hats off to Colm!

Regards,

Dion

View 4 Replies View Related

Reports :: Report Of Pivot With Dates

Jun 14, 2015

I have a table with columns

position, DateTime, value
(datetime being a text field by the way, not my fault :-))

I wrote a simple pivot query

TRANSFORM Avg(value) AS AvgOfvalue
SELECT Position
FROM table
WHERE (dateTIME)>format(Date()-2,"DD-MMM-YY")
GROUP BY position
PIVOT dateTIME;

The idea was that I could report each last 2 days with this query.The pivot query works OK.Creating a report based on this query, the control source of the fields is linked to columns like "12-JUN-15 07:00" which will not be available after some days.Isn't it possible to create a pivot report on a pivot query based on dates?

View 8 Replies View Related

Reports :: Count Dates In A Report?

Sep 30, 2014

I need the total of days in a report but exclude the repeated ones.

So user are working sometimes in different work orders on the same day but our administration only needs to know the number of days worked in one period of time.

i send a jpg with the example i use the =Nz(Count([Date Worked]),0) but that way i get all the entries counted

View 2 Replies View Related

Reports :: Printing Report Between Two Dates

Jun 1, 2014

Any code for viewing Report between two dates. So when you open the report it will ask for the starting date and ending date.

View 4 Replies View Related

Pass Dates From Form To Report To Query

Jan 29, 2008

I am trying to enter dates in a form that calls a report that invokes a query that uses the dates. It has been a less then satisfying experience. I am getting a Run-Time error 3122. Is it possible and I need to work on syntax or do I need to think of another way? BTW how do I lookup the Run-Time errors?
Thanks for helping an old guy learn new tricks.
Jim

View 5 Replies View Related

Query/Report Of Multiple Dates Within Same Table

May 17, 2007

I have a table with a separate record for each client.
Each record has 5 future dates called ToDoDate1, ToDoDate2.... and tasks to be done called ToDo1, ToDo2....

I want to create a query that will search the 5 dates from all client's record and pull all the dates selcted within a range and list the ToDoDate, ToDo and name and a report.

I created a parameter query to prompt for user for dates to search between and can get the report to work for ToDoDate1 but I'm unsure of how to do this for the multiple ToDoDate fields.

Any help would be appreciated. Thanks

View 1 Replies View Related

Combining Combo Box Field With A Between Dates Report

Apr 9, 2008

Hi I currently have 2 seperate reports that I want to use in one.
I have...

1. A Date report that works from a form with a to and from field, it then finds all reports between those two fields.

2. A Client report from a form, a simple drop down box that gets it info from a query, it then works of a macro to find all records to that client

What I want is to have the one form where you can 1st select the client at the top from the drop down and then you enter the to and from date, once you click ok it will bring up all records for that client within the dates.

This is the code I use for the dates, is there a way to add an extra bit that makes it look at the client combo as well to just show the records for that client between the specified dates...Code:Private Sub OK_Click()Dim strReport As String 'Name of report to open.Dim strField As String 'Name of your date field.Dim strWhere As String 'Where condition for OpenReport.Const conDateFormat = "#mm/dd/yy#"strReport = "clientnameanddate"strField = "DateJobReceived"If IsNull(Me.txtStartDate1) ThenIf Not IsNull(Me.txtEndDate1) Then 'End date, but no start.strWhere = strField & " <= " & Format(Me.txtEndDate1, conDateFormat)End IfElseIf IsNull(Me.txtEndDate1) Then 'Start date, but no End.strWhere = strField & " >= " & Format(Me.txtStartDate1, conDateFormat)Else 'Both start and end dates.strWhere = strField & " Between " & Format(Me.txtStartDate1, conDateFormat) _& " And " & Format(Me.txtEndDate1, conDateFormat)End IfEnd IfDebug.Print strWhere 'For debugging purposes only.DoCmd.OpenReport strReport, acViewPreview, , strWhereEnd SubPS I didnt write this code i just edited it for my own use so please reply in simple terms

Any suggestions are appreciated !!!
Thank You

View 1 Replies View Related

Reports :: Listing Consecutive Dates In Report

Dec 1, 2013

Is it possible to list out each consecutive date in a report when given a range of dates without having to create a table of dates?

For example, given the date range 12/01/2013 to 12/05/2013:

In report:
12/01/2013
12/02/2013
12/03/2013
12/04/2013
12/05/2013

Is it possible to do this dynamically in vba?

I want to ultimately join this to a recordset for the report.

View 1 Replies View Related

Reports :: Dates To Be Shown Horizontally In Report

Dec 20, 2014

I need to show field values in relation to dates, but I want the dates to be shown horizontally in the report.

I tried the crosstap quarey but it is not working for me, as i need to report many unrelated raws.

I've attached brief explanation of what i want.

View 3 Replies View Related

Reports :: Allow User To Enter Dates On A Report

Feb 26, 2014

I would like my user to be able to enter dates on a report.

I have entered unbound fields on the report; but it doesn't keep any of the information (dates) for printing or otherwise. (I don't need to save these dates since they change monthly.)

View 1 Replies View Related

Macro To Open A Report That Includes Certain Dates?

May 11, 2012

I have a bunch of reports that need to be printed, but only if they include a certain date. I want all of the dates to show up though.

For example:

There are 3 reports with a bunch of different dates in the bill date column.

Reports A and B include the date 04/30/2012, along with a bunch of other dates.
Report C does not include the date 04/30/2012.

i want reports A and B to open since they include the date 04/30/2012, and I want all of the other records with other dates to show up on the reports along with records with a date of 04/30/2012. I do not want Report C to open at all.

View 2 Replies View Related

Parameter (Date) Query To Get Input Dates On Report?

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

Reports :: Filtering A Report Through Start Of Week Dates

Jun 23, 2015

I've restructured my 'application' to have one table and a lot of query/forms. In light of not figuring out the combo box situation I altered the requirements and what I needed thinking I may have an easier go of it, but alas not exactly my experience. I have 5 reports that I want to filter them by week(start of the week). I have a function which can turn any regular date entered to the start day of the week(monday). I thought my best way about this would be to fill a combo box with all the accepted dates through a separate table's column.

Is there a way to pop a dialog box when the user clicks to generate the report that will prompt them to choose a week then run that criteria against the report and only bring back that date?

View 14 Replies View Related

Reports :: Create Report By User Between 2 Dates Using Combo Box

Mar 5, 2013

I managed to create a report by user using a combo box. When an user selects a name from the combo box, it generates a report showing all the records by that name.

However, now I need to be able to generate a report as above but between 2 dates. How??

I am having two sets of criteria.
1) by user
2) between 2 dates

Can this be done?

View 7 Replies View Related

Modules & VBA :: Filter A Report Based On Two User Inputted Dates?

Oct 23, 2014

I am trying to filter a report based on two user inputted dates, but can't seem to figure it out. I've played around with quotation marks, and # but can't seem to figure the syntax out.

Code:
Me.OrderBy = "Date Submitted"
Me.OrderByOn = True
Dim Date1 As Date
Dim Date2 As Date
Date1 = UserInput
Date2 = UserInput
DoCmd.ApplyFilter WhereCondition:="[Date Submitted] > Date1 and < Date2"

View 11 Replies View Related

Current User Report With Hold And Open Ended Dates

Jul 10, 2012

I manage a fleet of vehicles at work with drivers assigned to these vehicles. Each driver may opt to pay a contribution for private use of these vehicles.

This can normally be done on an excel database, however, the drivers sometimes go on leave, so I need to put their payments on hold. At this time, other drivers may take over for that period or the vehicle is left at the office. Also, the drivers change their level of contribution from time to time, so I need to adjust that accordingly too.

When the driver takes over a vehicle they complete a form that has the contribution level (there are 4 to chose from) and the dates they will have it from. They always provide a start date, however, sometimes the end date is left open.

Sometimes, driver A will say something like 'From 1 Jan 2012 To Open' then driver B may take it for a week and say 'From 1 July 2012 To 1 August 2012'.

I want to know if Access is the right tool to produce the report with the current driver, and that it will revert back to the Driver A after 1 August?

View 1 Replies View Related

Reports :: Option Button To Filter Report With Dates In Access 2010

Jun 9, 2014

I have a retort that I want to use an option button to filter the report is a specific date is 48 hours before the current time. I'm not having any luck. Below is the code I have been playing with, but no go.

Private Sub NoMove_Click()
Select Case Me.NoMove.VALUE
Case 1
Me.Filter = "CLM = 'Now() - 48'"
Me.FilterOn = True
End Select
If Me.Filter = "" Then
MsgBox "The filter returned Null"
Else
End If
End Sub

View 14 Replies View Related







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