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
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
I have a query which displays information on course dates. I have 2 fields one called Course Start Date and the 2nd called Course End Date. The fields are formatted as dd/mm/yy and are in the same table.
Some courses run for one day only and so have just a Course Start Date with no Course End Date.
I need to display these 2 fields together (example 01/01/06 and 03/01/06 as 01-03 January 2006) so that I can export the resulting records to an excel spreadsheet to email to a client.
I cannot find a format or expression to combine the 2 fields.
I have monthly reports developed from various query's and what I want to do is have a single report that I can display my monthly totals from each monthly report without developing a whole new report. I want to use the existing totals from the monthlies and have them all on one report. Can anyone help me? Jaxfire
I have several lookup fields in a table that reference data in other tables, each with two fields (unique nummber & text data). The combo boxes in the data entry form work fine, but when I produce reports, the unique number shows up in the output, not the text data from the second field. What can I do to force the data that I need to see in my reports?
On paper I have designed a faculty contact database and have now come to the point where I am designing the form. In the contact information table are Cell, Home, Office fields as well as Personal, Work, Alternate email fields which will contain the corresponding information. On the form after the information is entered the person needs to select an option from each of four combo boxes to indicate which is the preferred number, number for student, preferred email and email for students. The question is how to populate the text fields in a report based on the selection of the combo boxes with the information stored in the corresponding field (cell, home, office phone numbers and personal, work and alternate email). I am assuming it is done in the query but I don't know how or can it be done in vba?
I have a table of UPCs that we use for the tee shirts we manufacture. The problem is that our MRP system organizes the tables with S,M,L,XL UPCs in one row and the XXL UPCs in a seperate row(we use different pricing for XXL). I need to figure out how I can make it so that there is only one row per style number taht has both the S,M,L,XL,XXL UPCs in one row, but can't seem to figure out how to do this. I've attached in image for reference.
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3 V.I. Capital Appreciation Fund.................5 V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3, 5 ,1
I have a database that uses a field for the year (but I'm using a fiscal year that will end June 30, 2013) and another field as an autonumber. I use the year and autonumber as my reference number (i.e 2013-0001). I'd like to be able to combine these fields to generate the entire number. Also, I'd like for the year to add 1 beginning July 1st of each calendar year, and the autonumber start over at 0001 (i.e. 2014-0001 on July 1, 2013). Is this possible, and if so, how can I do it?
im having some problems with my db. i have 2 tables(ATL and BATE) in ATL, i have (im, IMAGEID, BREAK, TASK, LOC, bate) and in BATE, i have (Bate, var)
im trying to take Bate from Batetable, and insert it to ATL, even tho thers already a bate in ATL, both contain different records... i tried this, but didnt work Select ATL.im, ATL.IMAGEID, ATL.break, ATL.task, ATL.loc, ATL.Bate, BATE.bate from ATL, BATE; it worked, but BATE shows the same value for the hole row, it displays the same thing thing, even tho each row has a different value... can anyone help me out? thanks
Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):
UserA - Hours - Date - ProjectA UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectB UserA - Hours - Date - ProjectB UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectA
What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.
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 ?
This is driving me a little mad, and its probably something quite simple!
I am creating a form which will allow users to narrow down their search based on criteria entered into three combo boxes.
The first combo box pop's up a calendar and is populated when the date is chosen on the calendar. The second combo box will provide a list of systems that have a record entered on that date The third combo box will then show a list of case id's based on the system and date selected.
I can get it working when ignoring the date, but as soon as I bring the date combo into the equation I get a data mismatch error. Now I presume that this is to do with holding a date in a text field and comparing with a date/time field in the table, so I have set both to 'short date', but still get the error.
Can anyone provide any pearls of wisdom to help stop me from going insane? (that might be a bit late tho!)
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 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#));
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 have creadted a form with a combo box that has 3 dates in it: February 1, 2005, September 1, 2004 and June 4, 2004.
Is there a way to display in another field on the form(A text field) when one of these dates is selected from the combo box, a text field returns "DATED ON THIS FIRST DAY OF FEBRUARY TWO THOUSAND FIVE"?
I reckon this is a vb issue and any help is appreciated.
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.
