I'm using M$ Access 2002. I have a query which contains an expr as one of the fields. The expression adds three text fields together to create a salutation. This is the expression:
Where all three fields are of the Text data type. I am trying to display the students grades for next year. So if the value of [students].[gradelvl] is "10", it should display "11". If it were a Number data type I think this would be easier for me to figure out.
I finally am figuring out this Access db stuff. I have managed to produce what I believe are normalized tables, built a query or two, and now a report. However, what I need to do now is something that I don't even know is possible. Further explanation is that I need to produce a query that automatically counts how many of a particular course has been taken. So, in a sense the query would show that 500 people have taken course A. If I have to build a query for each course, that is fine (because there is a course B, C, D, etc.). The problem is that the tbl the courses are contained within has the following properties:
I thought the easiest way to build a query would be by the CourseID. Problem? Well obviously ALL courses are contained within this field/column.
I was able to produce a query with the following SQL that produced totals for all courses...which was fine.
SELECT tbl_EmployeeCourses.CourseID, Count(*) AS Expr1 FROM tbl_EmpoyeeCourses GROUP BY tbl_EmployeeCourses.CourseID;
But the problem is that I can't get these totals in their OWN queries. When I print the report, I want it to say in the report footer (obviously in separate textboxes) "Course A total:___" or "Course B total:___"
I know I will later be able to build on that to produce a grand total if the need should arise. but it's pulling these apart now that's whipping me.
Does anyone have any pointers or can tell me what I am doing wrong please? I would greatly appreciate it. Thanks so much!
I have successfully used an Expr code in my query to get the calculated result that I need. My problem is I can't seem to display the results on a form. I get the error "Name?"
When I try to find the query on data control which I used, it does nothing but shows me the fields from my table. How can I get a query expr column to display in my form?
the name of the query is "together" and the column's name is "everyone"
Can someone please point me in the right direction
I have searched and found references to converting text to dates. I need to go the other way. I have client# field and a date field. I would like to make an update query to populate a new field by concatenating the two fields minus the "/". I am making a new key field in an existing table. Then in the future when data entry is done, I would like to convert the date and client# as they are entered. I was looking at using the left$ or right$ function, but the dates may be in mm/dd/yyyy format or m/dd/yyyy format or m/d/yyyy and so on, depending on the month and day. Is there an easy way to convert the date to a number? I tried datevalue, but it leaves the "/" in there. I'm not sure how to section off the parts of the date I need, when the month and day vary between 1 and 2 digits. Thanks for any help.
This is very close to the label question that I also have posted. I am trying to get my report to have the back ground of some text boxes turn yellow if they are populated. I can get them to turn yellow, but they turn yellow whether they are populated or not. I have the code in the Report Open event. Here is some of the code that I have.
If Not IsNull(MMSJob) Then MMSJob.BackColor = 65535 Else MMSJob.BackColor = vbWhite End If
Hello, in my Access database there is quite a bit of VBA code. I have set my LAN and file path in some of this code but will need to change it when this database will get to other users using a different LAN and path.
Is there a way I can avoid them to open the code and manually change this information? I was thinking of some sort of text box where they will write their path (ex. \S01000sharedoffice*.*) and use a cmdbutton to overwrite the existing path?
Not sure if this is possible but I have a few text boxes on a report set to vertical yes under properties. They print fine, but on the pre-printed forms I'm using they print upside down. Is there a way to realign they way the text prints when using the vertical yes setting? I really need they text to be inverted. I'm I asking an impossible question? Thanks..
I am having a query which having a category field like Electrical, Sports, House hold etc.
What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...
Option Compare Database
Public Function fncGrade(intNum%) As String Select Case intNum Case 0 To 1: fncGrade = "Same as Previous" Case 2 To 32: fncGrade = "C-3" Case 33 To 40: fncGrade = "C-2" Case 41 To 50: fncGrade = "C-1" Case 51 To 60: fncGrade = "B-3" Case 61 To 70: fncGrade = "B-2" Case 71 To 80: fncGrade = "B-1" Case 81 To 90: fncGrade = "A-2" Case 91 To 100: fncGrade = "A-1" Case Else:: fncGrade = "X-X" End Select End Function
Hi all, I am trying to have the rowsource change on a "subcategory" combo box depending on what the "category" combo box says.
I added an event procedure to the "Category" combo box which changes the Row Source in the subcategory combo box to something else. I'm not sure that I did this right as if I close the database and reopen it, what I selected in the subcategory combobox is gone and it is blank. Any ideas? Here is my simple, likely incorrect event procedure:
Private Sub Category_Change()
If Category.Text = "Rare Books" Then SubCat.RowSource = "scRareBooks"
End If
If Category.Text = "Legal Documents" Then SubCat.RowSource = "scLegalDocuments"
I am using a combo box to enter Employees names for training hours...I want to use a pivotchart but when i use the training hours table/form the pivotchart gives me the combo box numbers instead of the names. I know there is a formula or something I am suppose to use here or even a query but I don't have a clue as to how to do this....if someone would be so kind in directing me with instructions and help...thanks :confused:
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it. The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
When the user clicks on say; the customer ID dropdown menu, i want the label text on the right of it to get darker (make it more visible). How can i do this?
I'd like to change the colour of the text in a command button when the user click the button, but for the text to return to it's original colour when the botton pops back up.
I'm assuming you use the following commands:
Private Sub cmdClose_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) *******CODE******** End Sub
Private Sub cmdClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) *******CODE******** End Sub
But I'm not sure as to how I would enter the correct code.
I have about 25 text boxes on my form that use the dcount or count functions to obtain a number. (Text boxes are labeled 'Text1' through 'Text25') When the value of the textbox is 0, I would like to have the color of the text box turn red.
Is there a way I can do this using a for loop? Or a with statement?
Checking each one individually just seems like poor programming. Any thoughts? Ideas? Thank you.
I was trying to see if there is a way to convert text to a hyperlink in a query.
Background : I'm pulling information from a lotus notes database using an OBDC connection and storing it into an access database and using that to upload to a sharepoint site. While we are converting over to a sharepoint site, I was trying to make it easy to get back to the original site for each document to check the work.
I want to change text in txt boxes. when a new customer is being created we are too lazy to put uppercase in. so i want to change the text so the first letter is uppercase. i have used this on every event and it doesn't change a thing.
I have a box with age in called [agecalc]. I want to change the border of the box to red to indicate they are a Juvenile if under the age of 17.I already have some code to write the text, I just need to change the border of this text box called agebox
i have a database with a large number of records. Navigating through the records through a form one by one is a pain, so i want to create a text box where you can enter the ID number of the record, and whichever record has the matching ID number it changes to that specific record. I know that something like this is already apparent at the bottom of Access, but i want my user to do everything within the database itself, as all of the panes are removed whilst being used
I am trying to add a percentage sign (%) after a number I get from a table, thus the number 1,5 should display 1,5% and NOT 150%.
I am using the Format Property of the text field.
I tried #,##% which converted the value to a percentage (1,5 became 150%) Then I tried #,##"%" and #,##\% Access ignored both and changed the value in the property field to #,##% Using the Format function is not an option because it is a bound field which should be editable.
How do I change the background colors and text formatting in all of the reports in one database at the same time? If this is not possible, is there an easier way to change the report formatting without going through every report?
I have a report that prints labels (similar to avery labels) which pulls data from a query. These labels are all unique and vary in length. Due to the length variance, I want the font size to get smaller for labels with more characters. I want the text to go down to another line when necessary.
In my report under the OnPage Event Procedure I wrote this:
Private Sub Report_Page() If Len(Text2) > 20 Then Text2.FontSize = 8 If Len(Text2) > 10 And Len(Text2) < 21 Then Text2.FontSize = 10 If Len(Text2) < 11 Then Text2.FontSize = 14 End Sub
This works to some degree but the problem is that once there is a longer label, all of the proceeding labels are resized & the longer label that should have been resized was not.
Is there another place I should be writing this code? I looked under events for the textbox but there doesn't seem to be anything that would work since none of them would trigger when printing labels
I have created a member data base form in Access2010.
That has been populated with 366 member records
I have two text box fields that are currently free type fields, Member Type & Member Status.
What is the best way I can change these to show only the relevant options:
i.e. Member Type ( Full, Family, Pensioner, Life. ) Member status ( Current, Lapsed, Deceased)
I have tried to right click the field but the "change to" option is greyed out & not available.
I have also tried to insert a combo box or list box to show these options. The "use control wizard" option has been highlighted but when I drag either a combo box or list box into the form. the wizard does not activate.
