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 Replies
ADVERTISEMENT
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
May 4, 2006
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.
Any help????
View 3 Replies
View Related
Aug 29, 2005
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
View 8 Replies
View Related
Aug 18, 2006
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?
View 1 Replies
View Related
Sep 6, 2014
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?
View 14 Replies
View Related
Jul 17, 2013
I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.
View 14 Replies
View Related
Oct 24, 2007
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.
View 1 Replies
View Related
Mar 17, 2007
REPOST from Design Area
Hello all,
I hope that the following explination of my problem in clear...
I have a table with a series of dates in individual fields: Day1, Day2, etc.
What I would like to do now is to combine those into another query/table where the dates are all in one field.
ie:
Original Table
[Day1] March 1
[Day2] March 2
[Day3] March 3
New Table
[Date] March 1, March 2, March 3.
Can you help me figure out how to accomplish this? I greatly appreciate your time and sharing of any thoughts that you have on this situation.
t
View 4 Replies
View Related
May 12, 2013
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...
View 2 Replies
View Related
Jun 5, 2007
Hello All,
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
Any help would be greatly appreciated.
View 14 Replies
View Related
Dec 13, 2012
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?
View 4 Replies
View Related
Aug 20, 2007
hello people
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
View 5 Replies
View Related
Jan 16, 2014
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.
View 12 Replies
View Related
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 3 Replies
View Related
Apr 11, 2006
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!)
View 1 Replies
View Related
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
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
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
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
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
Dec 17, 2004
Dear All:
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.
Thanking in advance
Dion
View 2 Replies
View Related
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
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
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
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