Reports :: Counting Items And Putting Them In Text Boxes?
Jun 6, 2013
I've got a table with 23 columns. Column 1 is the ID row which has a unique client ID in it. Then we've got a column called 1st_Reason and one that goes with it called 1st_Transfer_Date. This pair repeats for 2 through 10. Each of the Reason columns can have a set value, for example "First Processor".
What I need to do is create a date with months on the Y axis and the 5 different reasons on the X axis. I need to count the number of "First Processor" across all 10 Reason columns for each month. I would need to repeat that for each other reason type, but if I can get one to work I can simply change the reason type.
Here's what I'm using to count May's total:
Code:
=Sum(IIf([Current_Reason]="First Processor" And [Current_Processor_Transfer] Between #4/30/2013#
And #6/1/2013#,1,IIf([1st_Reason]="First Processor" And [1st_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,0)))+(IIf([2nd_Reason]="First Processor" And [2nd_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,IIf([3rd_Reason]="First Processor" And [3rd_Transfer_Date] Between #4/30/2013#
[Code] .....
I get wildly inconsistent results. On one of the types, it's only counting where it's the 1st_Reason. Some of the others return the same value regardless of which month I have selected.
View Replies
ADVERTISEMENT
Mar 20, 2007
I've asked this in a previous post but only got confused. Please forgive me, I'm a newbie at this. I have a table that I need to count a text field.
Table Name: ACE/ARB Table
Field Name: MR# (houses a 8 digit patient number)
Field Name: Failed Patient (houses a "TEXT" field with possible entries of "YES" "NO" or left blank.
Each MR# may have may different entries such as:
MR# 12345678 Failed Patient: YES
MR# 12345678 Failed Patient: NO
MR# 12345678 Failed Patient: NO
MR# 12345678 Failed Patient: (blank)
MR# 87654321 Failed Patient: NO
MR# 87654321 Failed Patient: YES
I need my report to count distinct MR#'s:
From the example above, I would need my report to show/count "2" medical records.
Any help on this would be GREATLY appreciated, I'm going crazy here. Also, if any suggestion has to do with SQL do I type what you would send me directly into the SQL statement from a query?
View 7 Replies
View Related
Oct 16, 2014
I have an access project that I am working on and need to be able to select multiple items from a listbox and have the exact selections appear in a textbox on the same form. I have looked around and have not been able to find any code that works.
I have tried:
Me.user2 = Me.slct_auditor.Column(0, 1)
Me.user3 = Me.slct_auditor.Column(0, 2)
Me.user4 = Me.slct_auditor.Column(0, 3)
Me.user5 = Me.slct_auditor.Column(0, 4)
Me.user6 = Me.slct_auditor.Column(0, 5)
Me.user7 = Me.slct_auditor.Column(0, 6)
Me.user8 = Me.slct_auditor.Column(0, 7)
but when skipping the first item in the listbox it is still passed as into the textbox.
View 4 Replies
View Related
Jun 4, 2013
I have a single field in a table called "Client Contact", where users enter a semicolon between the name, address, and city state & zip. My reason for this was so we could copy client info with a single copy and paste (like from an email). But, on the final report, it needs to have these three parts split up into different lines, or even different textboxes. I can't find a way to do that.
View 1 Replies
View Related
Jan 30, 2015
Is it possible to use find and replace to modify text in report design - or else is there something else I can do to get the same effect? I have a really useful report and I want to modify it for use with a different dataset. To do that I will need to change the text in over 150 text boxes. I have seven different fields which each occur over 20 times in the control source formulas in the text boxes, because they are used in different ways in different calculations. If this was excel I could do a simple find and replace to change e.g. every reference to fieldname OLD to fieldname NEW, but I cannot see how to do that within access report so am haveing to click on each text box in turn, go to properties and edit the text box .
I am trying to switch to a more general naming system in the modified report so then I will be able to assign data with the required fieldnames for the report within a query. But the report I am starting with has field names based on years 2013, 2014, 2015 etc.I want a quick way to change each reference to those field names to my more generic new field names.
View 2 Replies
View Related
Apr 11, 2013
I have a report that has Bill to and Ship to addresses. They are positioned across from one another (ship to on the right and bill to on the left of the page). The issue is that the 'can shrink' doesn't work when there is data in the same field on the other address..
View 1 Replies
View Related
Feb 5, 2015
I have 10 unbound textboxes with the Tag Name "LoopID" in my report. I have to display the "PatientNumbers" field from the table tblPatient in those text boxes. Below is my code.
When I run the report, I get the error message: Run-time error '2448.' You can't assign value to this object and the code "Ctl.Value = rst!LCANumber" is highlighted.
Private Sub Report_Current()
Dim strSql As String
Dim dbs As Database
Dim rst As Recordset
Dim x As Integer
Dim Ctl As Control
strSql = "Select PatientNumber from tblPatient"
[Code] ....
View 14 Replies
View Related
May 4, 2006
Can anyone help?
I have a table with the following:
Booked with a y/n field
Person Name with a text field
So...
Yes(True) Jim
Yes(True) Jim
No(False) Jim
What I need is to be able to show a query that shows if the checkbox is ticked (True) and the Name is Jim total up how many true items there are. So from the above I can see that Jim has 2 true items. I would then like this shown in a text box is this possible?
Also, it is not always going to be a person called Jim so... (From above)
Yes(True) Jim
Yes(True) Jim
No(False) Jim
Yes(True) Jack
So how would this go on a query in a sub form? So Name Jim would show 2 and Jack would show 1? At the moment is is grouping the name and I dont want that.
Any ideas
Cheers
View 4 Replies
View Related
May 18, 2005
Hello,
I am trying to count the number of certain items on a form. I have the formula =count([field]) which gives all entries for that field. How can I separate out certain criteria such as count only items that have the number 1 in this field?
Thanks
View 4 Replies
View Related
Sep 1, 2014
I have a Table of Special instructions. Each type of a Yes or No Text Box. There are 13 items in this table along with the ID key.
Each Field has a Special Description. I used the Y/N format for ease of use for user input to simply select the applicable options.
However, I need the text description to display on the printed report, which is not the problem.
So i created a separate text box for each item that simply says; If True, "Description", else blank. And named each one sp1...sp14.
So now, I want to take these text boxes with the proper descriptions and string them together.
My formula is: =Trim([sp1])&" "&([sp2]) etc.
This does produce the proper text results, however, and oddly enough, each item displays on its own line rather than in a string.
I get:
SP1
SP1
Instead of the desired result of SP1 SP2
This seems to simple, and probably has to do with the yes/no format. I've tried with and without (), and using + instead of &, and to troubleshoot, I eliminated the " ". No luck. Everything is coming back as a single column.
View 3 Replies
View Related
Aug 27, 2013
I have a form that has three cascading combo boxs, plus a subform. The third combo has a list of questions, and you put your answer in the txt box in the subform.
What has to happen is that the combo box has a number of questions and each has to be answered, is there a way to know if all these questions have been answered by knowing what listindex or listcount ect that has be used and to have something set to advise the user that there are more questions and what ones are to be answered.
View 3 Replies
View Related
Jul 25, 2013
I am having trouble creating a chart within a report. Let me start off by explaing my report.
I have many unbound textboxes on my report that all have the code very similar to this:
" =Count(IIf([Complaints Table]![Month]=1,IIf([Complaints Table]![Decision - Our Favour? (Y/N)]="Y",0))) "
This basically gives a count of for a specific month. There are twelve rows of text boxes and two columns. There is a query applied to the report to input the year, as this is a yearly report.
What I want to do is link a chart to each and every text box to show the data in an easy to view format. But I can't seem to figure it out, and I've had no luck on the web .
View 1 Replies
View Related
Dec 31, 2013
I have a report and within the report I have added some text boxes with some simple logic such as sum, count, etc. These text boxes function well as long as the logic is referencing existing fields. The minute I try to have a text box reference another text box I get the "Enter Parameter Value" box pop up. I don't understand this as the text box I am referencing has a vaule based on what it is referencing.
Example: 1st text box control source =Sum([existingfield]) I get a value.
2nd text box control source =[existingfield2]/[1st text box] I get "Enter Parameter Value" of 1st text box.
View 11 Replies
View Related
Feb 25, 2014
I have a query where I want to add sequential counting to all items that are part of a group. I have grouped the query on MaterialCode and want to add a sequencial counter for all orders that have been assigened to a materialcode
For Example:
Key MaterialCode Order Counter
abc1 111111A 987654 1
abc2 111111A 687654 2
abc3 222222B 655511 1
abc4 333333A 251544 1
abc5 333333A 555555 2
View 3 Replies
View Related
Apr 5, 2015
I have a combo box with an associated label.
I want to eliminate the label and have the label text be part of the row source of the combo box.
The combo box is based on a select statement.
View 14 Replies
View Related
Jul 22, 2005
Putting the same text into every cell in a query column
Hi All,
I sometimes have to mark every record in a query with the same text in a field, when the field is either empty or contains whatever text.
At present I do this by copying the text, and then pasting it (ctrl-v cursor-down, a thousand times) into every cell in a column.
How can I do this in a more efficient way?
Thanks for your help.
Adrian
View 8 Replies
View Related
Jun 19, 2005
hi guys
i have 2 questions
1.
how can i count selected or not selected checkbox fields (yes/No) in a tex box ?!
( for example we have a 2 yes/no fields that are Male and another Female , and i want to make a form that shows number of males and number of females that selected in a text box )
2.
how can i make a search form that when i chose check boxes and then Search button it will show me the list of selected checkbox that selected in main form
( for example if we have 3 checkboxes 1.(low) 2.(mid) 3.(high) in main database and form , and if i want to make a search form that shows me list of enterees that is (Mid) )
thanks alot
View 8 Replies
View Related
May 14, 2013
So I'm fairly new to access as I've only spent one semester working with it. I have a project (work related, not school) that I can't seem to get a handle on.
I have a database with several yes/no fields (y/n)
name | y/n 1 | y/n 2 | y/n 3 | y/n 4 | etc
Is it possible to count how many occurrences of yes appear in each row in the table, and do that for every row? The object is to have a "grading system" if the person has like 4/5 then they are an A while another who has 2/5 is a D.
If this can be done then I can use this to come up with a way to deal with some of the other fields I want to tally and grade.
View 14 Replies
View Related
Apr 30, 2005
i have a combo box containing dates, however it will only display the date at the top of the list, no matter which date is selected.
any help would be gratefully excepted!
cheers
View 2 Replies
View Related
Sep 4, 2005
Hey Guy and Ladies,
In one database (Originally an Orders Database created by wizards)
This code to add items to a drop down list in the “orders details subform” works fine.
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long
If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!ProductID.Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID
Exit_ProductID_DblClick:
Exit Sub
Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
When the same code runs in my new database
Private Sub OptionID_DblClick(Cancel As Integer)
On Error GoTo Err_OptionID_DblClick
Dim lngOptionID As Long
If IsNull(Me![OptionID]) Then
Me![OptionID].Text = ""
Else
lngOptionID = Me![OptionID]
Me![OptionID] = Null
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!OptionID.Requery
If lngOptionID <> 0 Then Me![OptionID] = lngOptionID
Exit_OptionID_DblClick:
Exit Sub
Err_OptionID_DblClick:
MsgBox Err.Description
Resume Exit_OptionID_DblClick
End Sub
I get the error message
“You tried to assign the Null value to a variable that is not a Variant date type”
Any suggestions?
Also if anyone knows a better way to add items to a list box “On the Fly” I would really appreciate it.
It would be nice if you could type in the value and it would automatically add it to the list for future use.
Thanks for all of your help,
Joe
View 7 Replies
View Related
Aug 3, 2006
Need some help here....
I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list.
Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean.
(I have attached a word doc. that shows the table relationships.)
Thanks a lot ahead of time!!
ScrmingWhisprs
View 13 Replies
View Related
Oct 4, 2006
This is a somewhat complicated question to explain, so please bare with me.
I have a form with a ‘list box.’ The box is populated using a Table/Query Row Source Type method. The table I am grabbing the list from is “Department,” where it contains a list of my company’s departments(ie. Accounting, HR, Payroll, etc.).
Now, the only thing this table does not have is the word “ALL.” I need this word in the “List Box” because I want users to have the option to select ALL.
Is there anything in VBScript code wise or in Row Source that I can do to include this word?
I know I can manually go into the Department table to simply add the word, but this is not the point, because there is more to that(I have different list boxes that are grabbing lists from different tables, AND I have to refresh these tables twice a week). Please help.
Thank you.
Joe
View 1 Replies
View Related
Sep 21, 2004
i want the user to make a selection from one of two option boxes, with the choice that they make i want a list box to appear straight away, depending on there selection, can anyone help please.
Cheers
Andy
View 6 Replies
View Related
Apr 24, 2006
Hi guys, I'm new to Access and am in great need of some help.
I have a query that serves kind of as an attendance tracker where the students are listed down the left side with the weeks across the top. A check box is in each column that is checked to mark their attendance.
Now the problem: I need to figure out a way to sort by those who have been to 80% or more of the meetings. Preferably by it checking a box automatically in the "80% or more" column. Not sure if Access can do all this. Please help.
Thanks
View 4 Replies
View Related
Feb 18, 2014
I have set up a database to record several different sets of records for a volunteer group that I run. The types of records that I am keeping are hours on duty, uniform issued and attendance at training. The attendance form is a subform which contains a list of dates and the two fields of checkboxes showing if they attended or sent their aplogises.
What I am trying to do is to count the number of times that members attended training. I have set up a 'unbounded' text box in the footer and would just like to add a formula in to the command box of the box properties. Anything more i.e. coding then it goes over my head very quickly.
View 3 Replies
View Related
Jan 25, 2015
I have strange issue when creating auto populated text boxes which displays rows from combo boxes.
In one database auto populates works with simple text box Control Source edit "=[ComboName].[Column](NumberOfColumn)".
Somehow same method doesn't wotk in different database: here one time I have to insert VBA code at On change Event:
Me.TextBoxName = Me.ComboName.Column(NumberOfColumn)
And other time it wont work with On change but only with After Update Event (code is same).
Another thing this morning happens was that when I tried to add new record trough form where combo box and tex boxes are located, MS Office suddenly stopped working after selecting combo box selection (with message Microsoft Office has Stopped Working). It is 2013 version.
I got it work after deleting and re-inserting VBA code to autopopulate text box at After Update Event.
View 3 Replies
View Related