Hello friends,
I have data in my table field as "Orange" and some other entries in as all lower cases "orange", which really should be representing the same values.
Now when i use SQL in my modules to query and process this data, or create my report, it collects them as different values i.e. "Orange" is selected as one category and "orange" as another category, when really it should be getting them all (Orange, orange, ORAnge) as one category.
Perhaps this is a basic question, but everything i have tried doesn't seem to work. Can anyone tell me the best way to handle this?
hi all I have a form that has textboxes, a combox and a subform, when i complete a record, I will like everything to capitalized in the table but I can't seem to figure out how to do this on the subform fields. I tried the ucase function for the after update but the subform doen't have an after update
We have a situation where mail sent to Canada using the mail merge is being returned because it's not in all caps. I've used the following code in each fields after update:
Me.ActiveControl = UCase(Me.ActiveControl)
What I'd like to do is make it so only the Canadian addresses are in upper case. This would be determined by what is in the state field. AB, BC, etc. Any U.S. address would need to be in proper case where the first letter of each word is capitalized.
In my Access 2000 database I have a form which displays patients' treatment details. I have a text box in which the Forename and Surname are concatenated to display the full name and the Surname is included in a UCase function so that it is displayed in capitals:
This works fine on my office computer (XP professional), but when I run it on my home machine (XP Home) in order to carry out further development, the UCase function is not recognised. I get the #Name? message in the text box and if I try altering the formula I get the message:
The function you entered can't be used in this expression *You may have used a DoEvents, LBound, Ubound, Spc or Tab function in an expression *You may have used an SQL aggregate function, such as Count, in a design grid or in a calculated control or field
As far as I know, the versions of Access are exactly the same on my home and office machines. Is anyone able to shed any light on why this function works on one machine and not the other?
A few years ago, I wrote an application in Access 2000 that worked fine. Last year I upgraded to Access 2003. Everything works the same, but the UCase, Left and Right text functions give error messages when used in queries. Anyone have a way to fix this?::confused:
The Update Query is to prompt name entry in all lower case, then convert it to upper case.
The Update Query must do the formatting, no utilizing the Input Mask.
This is the code I thought would work.
Field: Firstname Table: Good Data Update: UCase([Firstname]) Criteria: [Enter first name in lower case]
The parameter box part of the Query runs fine. When I run the Query the parameter dialog appears instructing you to enter your firrst name in lower case. After I enter the name in lower case & click "OK" the reponse is "You are about to update 0 row(s).". There is nothing entered in the table when I check it.
Can anyone tell me what I need to change to make the Query update the table with the uppercase name?
I have a table with data in Title Case (England) and would like to run an update query or a code to change all the records to UpperCase (ENGLAND). Can anyone help me? Thank you.
There are two tabs named Table1 and Table2. In actual there are two tables in Access database named Table1 and Table2. How the data is stored in ACcess tables, I have made two tabs in excel workbook. Now I want Access VBA code that will check if data in Reference field of Table1 matches with any of the data in Reference field of Table2.
If it matches then change the status of the corresponding record of Table2 with either "Withdrawn","Obsolete" or "Updated". SO it depends upon which field out of "WIthdrawn","Obsolete" and "Updated" in Table1 stores "Y". At a time only one of them will have "Y" and rest of two fields will have "N" as shown in the sheets.
As in the example, now Reference "R566" of Table1 matches with Table2 Reference so the status field in Table2 for that record will be "WithDrawn".
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected Total Envelopes=sum(Envelopes) for date selected Total Documents=sum(Cases) for date selected Total Pages=sum(Pages) for date selected
I have a database that I import data from an excel spreadsheet into multiple times daily. The table that this data is imported into has several key fields that if the data already exisits in the table, and I attempt to import data that is the same except for one or more of the key fields is different. At this time the database it creates a different record. I am trying to get the database to overwrite the data in the database.
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded 121/2/201310:00:00 AM3:00:00 PM 221/3/20132:00:00 AM11:00:00 AM 321/4/201312:15:00 AM11:30:00 AM 421/5/201310:25:00 PM11:00:00 AM 531/2/201311:00:00 AM3:30:00 PM 631/3/201312:00:00 PM10:00:00 PM 731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code: Private Sub Test_Click() Dim strSQL As String
I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.
:confused: Hi I have some 100 questions for which user must answer by selecting any of the options provided using option buttons.
As i cant fit all 100 qtns in one form am splitting it into soem 10 forms.
User answers the qtns in 1st form and clicks on next button to goto to nxt form thn agn he answers the qtns in tht form and clicks on next button to goto nxt form..so on.. until he answers all the qtns in the last form.
I want to transfer the answers selected from 1st form till the last one..where all the answers are stored in one table whn a submit button is clicked.
please let me kno ASAP if thr is anyway i can do it. thnk in advance
Hi I have some 100 questions for which user must answer by selecting any of the options provided using option buttons.
As i cant fit all 100 qtns in one form am splitting it into soem 10 forms.
User answers the qtns in 1st form and clicks on next button to goto to nxt form thn agn he answers the qtns in tht form and clicks on next button to goto nxt form..so on.. until he answers all the qtns in the last form.
I want to transfer the answers selected from 1st form till the last one..where all the answers are stored in one table whn a submit button is clicked.
please let me kno ASAP if thr is anyway i can do it. thnk in advance
I am new to access (2007), There is a datasheet in the form where we enter our time in/time out. We enter data using the form but that is one data at a time only. So I tried to paste multiple data (records) from excel into the access table. After pasting into the table, the data appears in the query, but not in the form.When I go to the form to check if the data I pasted into the table will appear in the form's datasheet...some data appears but some does not.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
Hi, I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
