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#));
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#));
SELECT TOP 10 tblPortCallList.Port, tblPortCallList.Arr_Date, tblPortCallList.Dep_Date, tblPortCallList.Security_Level_Ship, tblPortCallList.Security_Level_Port, * FROM tblPortCallList ORDER BY tblPortCallList.Arr_Date DESC , tblPortCallList.Dep_Date DESC;
As shown, it picks the top 10 entries in a table.
Is there a statement, which can choose specific entries in a table, in stead of the top 10? E.g. entry number 2 in a table? I have three text boxes, which need to show the previous port of call, arrival date and departure date. The table in question is sorted after arrival date and then departure date.
Is it possible to get Access to open the print dialogue box instead of printing direct to the default printer when using the print command from a button?
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 ?
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.
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!
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?
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.
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!
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.
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.
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?
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
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
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.
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
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.)
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.
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?
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
