I have imported a large contacts table from an old database. The Name field has had all the names input as JOE BLOGGS etc. I can force the table to read joe bloggs etc using < in the format box in properties for this field. But I want the names to read in the Joe Bloggs format, first letter in each name being upper case. Is there an easy way of doing this.
Just got back in to databases after 15 years so wee bit lost at the moment.:o
On my form, there is a button that will auto-populate the subform.
If this button is clicked before the autonumber ID is established the code crashes. I have tried saving the record using...
DoCmd.RunCommand acCmdSaveRecord
...but this does not save the record. After executing this command I can even go into the table where this record is supposed to have been saved and it is not there.
There must be something I'm missing here. Any ideas?
I have a drop down list box on my form called FieldA. I would like for FieldB to update with a number depending on what is in FieldA. For example, if FieldA has Red, I would like FieldB to return a 2. I know this needs to have a case function done, but how do I get it linked to FieldB?
I've purchased & downloaded this Zip Code Database which has every Zip zcode in the U.S. which I use for different projects/reasons. The problem is, ALL of the data is upper case letters. I was wondering if in Access (or Excel) is there any way I could convert all of the letters (except the first letter) from upper case to lower case letters?
I'm trying to update some field value depending two string field using a function with if and case, function below:
Code: Private Function checkDATI(tipotransazione As String, tipovendita As String) As String Dim r As String r = "" If tipotransazione = "VENDITA" Then Select Case tipovendita
[code]...
And then when I call this function in a command button event as:
I know when creating a text field in the format option you can use the > or < sign so that when text is entered it automatically changes it to uppercase or lowercase - but i need it to be Title Case, any one know how I can do this....
I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.
Code: Private Sub ProductID_AfterUpdate() Dim qflPrice As Variant Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlQry As String Dim instID As Integer
I have a lengthy CASE statement in my database that displays specific text in a field based on the value of another. Simple stuff but for some reason it randomly will not work on certain values, and never the same one twice. Is there a commonly known cause for this? I have verified that the spelling and spacing etc. are correct in my code so that shouldn't be causing the problem.
Trying to filter a form based on a field with wildcard. My form has a txtCustFilter control where a customer's name can be entered in part or whole. The Customer's name is in PCCustomerName
This code works but, I'd like to make it case insensitive
Dim strFilter As String strFilter = "[PCCUSTOMERNAME] LIKE ""*" & Me.txtCustFilter & "*""" Me.Filter = strFilter Me.FilterOn = True
I have a quick query question. I have a table that lists employees and workcodes and time spent at the workcode, by date. There are multiple workcodes and I'm trying to create a query that lists each employee and total time spent in each workcode. The poblem I'm having is this: Unless the agent spent time in all the workcodes, he doesn't show up on the query.
For instance: Agent A, spent 200 minutes in Code A, 220 minutes in Code B, and 10 minutes in Code C. Agent A shows up fine in the query. Agent B, though, spent all 430 minutes in Code A. He doesn't show up at all.
I think it's also worth mentioning the format the information comes to me in. It's an Excel file broken up into 4 columns, Date, Agent, Code, and Duration. As much as I would like it to be Date, Agent, Code A, Code B, ect, I have no control or say in its format.
I have an Access Application that has a simple username/password interface that controls user access. It can then track who does what and controls access to different screens. Very simple.
One terminal is used by multiple users and they forget to log-out the previous user as it is a Production envirnoment.
Is there a timer that I can add to the App that logs Access off automatically after a period of time??
So I have a table that does a count on ratings in a certain category. The problem is that I want a number to appear for each of the three categories, even when the count returns nothing. In other words, we have 3 rating types, "poor, good, and great," and I need a result for it, even if no one gave the category a poor rating. Now I figured that if I created a table (tblCateg) that just held the three categories (sCatDesc) and did a join on the other table (Ratings) with the column (IN_Category) that I needed, it would force the query to make all 3 ratings appear and a count for each. Unfortunately, this didn't happen. All that appeared was --------- Great 17 Good 7 ---------
I figure the query is just seeing the count for the Poor rating as a Null rather than a 0; anyone know how I can force it to see it as a 0 instead?
Here's the query I'm using:
SELECT tblCateg.sCatDesc, Count(Ratings.IN_Category) AS CountOfIN_Category FROM tblCateg LEFT JOIN Ratings ON tblCateg.sCatDesc = Ratings.IN_Category WHERE (((Ratings.RatingDT) Between #1/1/2007# And #3/31/2007#)) GROUP BY tblCateg.sCatDesc;
I have a report button on a form and the form is the way of inputting in to two tables. I need a way to force the update of the tables before the query that the report is based on, is run.
Is there a command that forces an update of the tables?
This should be an easy one, but I’ve been know to say that before…
There are 2 tables (Experts and Education) I also have a form called Edit expert. On this form I have a tab control with 2 tabs. Each tab has a subform on it. The subform data entry property is set to yes. I want the user to enter the contact info of an expert on one tab, click the next tab and enter the education that the expert has. The 2 tables are in a one to many relationship with ExpertID being the primary key. So when you add a record to the education table you need the ExpertID. Well I want to be able to give it to the user. I have a control ExpertID on both subforms. On the first subform, the control source is ExpertID from the Experts table. On the second subform, the control source is set to the ExpertID on the previous subform. It works great until access tries to enter the record into education. Even though the correct expertID is in the box, it says that “you can not add or change a record because a related record is required in the table experts” so this leads me to think that access has not yet committed the Contact info record yet. What I want to know is can I force access to commit the record before the user moves onto the next tab? I have zipped a dumbed down version so you can hopefully understand what I’m rambling about =) all you need to do is pull up the Edit Expert form, enter a rec and then click on the Education tab. you'll see the correct ExpertID for the record you are about to enter, but after you enter the rec and try to click else where it yells at you. thanks for your time and effort!! - me
Hi, I know this is a fairly easy one. I really try to avoid asking answered questions, but I've honsestly searched through the forum with no results..
I have three statuses: 1 = Ongoing 2 = Complete 3 = Dropped
I want to force status 2, if IIf([EPsDL]/[TotalEps]=0;2;""), so that it would force on status 2, only when the calc. gives zero, and thus letting me change status freely, as long as the calc. is >0. I don't know whether to use IIf, nor where to put it at all. Would it work in a query, and if so - where to put it?
I have a form with a couple of sub forms, one of the sub forms only has a check box and a txt field and both are required before moving on to another record.
I have this following code that works partly, but it only works if one of the fields have info entered. It doesn't however stop the user from tabing through the sub form without entering 'any' data in either one.
---------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer) If Trim(Me!txtContactAgentName & "") = "" Then 'Validate Name Field MsgBox "Gotta Enter Your Name!" Me!txtContactAgentName.SetFocus Cancel = True ElseIf Me!ynCCContactLogLeft <> True Then 'Validate CheckBox MsgBox "Gotta Have a checkmark in Logged!" Cancel = True End If End Sub --------------------------------------------- Also the below throws up an error when I know it's got all the correct information. ------------------------------------ Private Sub txtContactAgentName_LostFocus() Forms.frmAllCustInfo.SetFocus Forms.frmAllCustInfo![AddNewCustomerRecord].SetFocus End Sub
I've gotten rid of all the linked tables from my frontend, so I'm fetching all my data from the backend with VBA code.
This works splendidly until I got to creating reports!
I can populate the report detail with the below code, by opening a recordset and filling in the appropriate text boxes when the report calls the Format_Detail subroutine, but the problem is, that opening a report with no recordsource sets the FormatCount property to 0, so it only runs through the format_detail once. Setting the .nextrecord to FALSE allows as many sub calls as I need, but I can't figure out how to force the report to generate a new detail line! It's looping through all the records in the recordset, but it's only generating one line on the report
Any ideas?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error Resume Next
If tags.EOF = True Then
[txtfound] = tags.RecordCount tags.Close Set tags = Nothing Me.nextrecord = True Exit Sub
Set tags = backend.OpenRecordset("SELECT Tags.TagCode, Codes.Description, Count(Tags.TagCode) AS CountOfTagCode FROM Codes INNER JOIN Tags ON Codes.Code = Tags.TagCode GROUP BY Tags.TagCode, Codes.Description")
I have form that I've set up and I need to make sure the user does only uses certain fields. For instance, when the user presses the 'New Record' button, it should ask him/her "Which country? 1) UK 2) US"..Once the country field is populated, I need it to go to the City field and ask "Which city".
