Queries :: PROPER Function - Transform Names In Upper Case In Capitalized Word
Dec 16, 2013
I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that
Code:
StrConv(Left(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",")-1), 3)&", "&StrConv(Mid(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",") + 1), 3) AS CompleteName
Which as you can see is quite a complex statement, how can I simplify it?
Example of a name: VITOR, FONSECA, preferable output, Vitor, Fonseca
View Replies
ADVERTISEMENT
Mar 16, 2013
Is there any way of making data that is inputted in lower case to automatically change to the first letter of each word being a capital ...
View 4 Replies
View Related
May 2, 2007
Any ideas how to Capitalise data in a table.
View 14 Replies
View Related
Feb 23, 2006
Hi
I have a simple database where I wish to force only Upper Case text even if user inputs Lower Case text, how can I do this?
ViRi
View 5 Replies
View Related
Aug 2, 2005
Hi,
Is it possible to filter out entries beginning with a capital letter with a query?
Ive tried Like UCase, but that doesn't work, and there is no upper case wild card.
(I am creating a book index and want to have a look only at the entries that are people's names)
many thanks
View 13 Replies
View Related
May 26, 2006
Just been searching around, found a few posts of uppercase questions, none that quite fit with mine, anyway,
Basically, the data I am working on produces names as such "JoeBloggs" "FredPerry"
Anyway, Im going to use a query to seperate these out into first name and last name, but, I can't seem to find a way to specify a capital letter as a wild card. So far:
CapPos: InStr(2,[calllogs]![CalledNumber],"A")
Then I am going to use the mid function to seperate the two, depending on the position of the first capital letter.
But I just need to replace "A" with something that will find any capital letter.
Cheers
View 10 Replies
View Related
Jul 10, 2006
I have a field in the table that contains numbers and a mix of upper and lower case words.
I would like to have this to it check for numbers and then makes the first letter in a word upper case and the remainder lower case.
I know you can do it like this >L<????????? but that dow not work if the word lengths are different. Unless I am missing soemthing.
Example of text in field is this:
100 RCSCC MAJOR PAIN
I would like the RCSCC to remain in UPPERCASE and then move to the next work and change the first letter to UPPER CASE and the remaining letters lower case then repeat this on the next word if there is one in the field.
Thanks for all your help.
View 1 Replies
View Related
Jul 10, 2006
Is there away to change what is in the table field [surname] to the first letter being upper case and the remaining letters lower case.
Thanks
Sorry I found it. Just in a little rush.. Should have looked harder.
View 1 Replies
View Related
Jun 19, 2007
Hi All, A quick question..
Is there a quick way to change about 5000 primary keys fields from lower case to uppercase?
Thanks Anna :)
View 4 Replies
View Related
Sep 13, 2004
Hi All,
I want to change existing entries in a table to upper case format. I know that it is possible with a query (update I suppose) using an expression with the "UPPER" function. But I am not quite sure how to implement it in Access. A little help would be welcome...
View 2 Replies
View Related
Oct 6, 2015
what do I do so that when I type into a field the first character becomes an Upper case automatically.
View 1 Replies
View Related
Apr 12, 2007
This is kind of a weird question. I downloaded and imported a table with cities and zip codes in it. All the city names are in upper case. I want to use the city names in that table as the row source for my City text box.
But in my database, I store city names in Proper Case, with just the first letter capitalized. So when I use the table as the row source, it pulls in the city name all in upper case
Is there a way, either within Access or not, to change the city names in that table to Proper Case?
View 4 Replies
View Related
Mar 5, 2008
Hi,
I hoping someone might know what expression I need to create to convert text in a field in an append query to proper case.
The reason for this is that I am receiving data in all uppercase and really need it to be in proper case, that is only the first character of each word in the field is capitalised.
A field name for instance is strProductDescription and I want the data to change from "FRIDGE FREEZER" to "Fridge Freezer" on appending the data to another table, hence a proper case function or expresssion.
Any assistance would be most appreciated.
John
View 10 Replies
View Related
May 22, 2015
I have a database containing a mixture of upper and lower case input. I can make upper case appear on the screen by using > in the format box in the property section for each field. This works great but the same old uncorrected stuff remains on the database. How can I get the database to store the corrected upper case data after I have entered it in lower case?
View 11 Replies
View Related
Oct 12, 2006
Hello all:
I have a database with the following fields: Last_name, First_name and Mid_name.
These fields are concatenated into a field called Full_name using this method: =[First_name]&" "&[Mid_name]&" "&[Last_name].
I wish to have the Full_name field convert the text in it to proper case.
Any ideas on how to go about it?
Thanking in advance,
Dion
View 3 Replies
View Related
Apr 27, 2005
I am trying to change all the fields of a table from All Caps to Propercase. I have used strconv propercase before, however only on a field by field basis. I have about 5 or tables that I have to do this to and am trying to save the typing. I know I can do this with a recordset (easily in asp), but am not familar enough to do this in vba. Can you do a for each fld, like in asp, or am I going at this the totally wrong way. I tried using an update query with the *, but access doesn't like that too much. Any help is greatly appreciated.
Thanks,
Josh
View 1 Replies
View Related
Jun 29, 2014
Is there a Input Mask or Rule Validation that would automatically or require user to input data into Access field in a proper case format? Such as my text would be "My Text" when viewing the actual table? If so how would you do it when in Design View of Table.
View 1 Replies
View Related
Jan 21, 2008
Hi Folks,
Does anyone know how I can use the Proper Function in an append query.
I have a field whereby all the data is in UPPER case, but I only want the first character of each word to be upper case.
I have looked up the Proper function, which should do the job, but when I apply it in the specified field I get an "Undefined function "Proper" in expresssion message
my expresssion is as follows: salutation: Proper([strSalutation])
Any assistance would be greatly appreciated.
John
View 6 Replies
View Related
Dec 22, 2014
I have a table with multiple rows per member that I would like to convert to 1 row per member. I have a table that looks a bit like this:
Member_IDDiagnosis
10000Hypothermia
10000Frost Bite
10001Fatigue
10001Dehydration
10001Exhaustion
99999Exhaustion
99999Hypothermia
And I'd like to convert it to this:
Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4
10000 HypothermiaFrost Bite
10001 Fatigue DehydrationExhaustion
99999 Exhaustion Hypothermia
The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.
View 3 Replies
View Related
Mar 28, 2014
I have a table with acct numbers of various lengths, ie:
246
18
36895
352119
472
I need to run a query so that all of the acct numbers are 8 characters long with zeros as placeholders in front of the actual acct numbers, ie:
00000246
00000018
00036895
00352119
00000472
I tried using the expression NewAcctNo: 00000000+[AcctNo], but that did not fix the problem.
View 2 Replies
View Related
Mar 24, 2015
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code:
TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
View 2 Replies
View Related
Feb 28, 2006
Hi everyone, please help, i am in depserate need here. Am i right in thinking nested IF functions are limited to 7 variables, i need to choose from 12, so i need to use something different
it seems like a CASE function could do what i want it to, but i really have no clue where to start, shall i enter this into a query criteria box, or on the form itself ina new field box...??
Can you give a few hints as to what the code would look like, i have two fields, obviously. One is 'lesson_type' and one is 'cost' i want the cost to change depending on what is selected in the lesson type box.
LESSON TYPE ----------- COST
Beginner 1hour ----------- 15
Beginner 2hour ----------- 29
Beginner 3hour ----------- 42
Test Retake 1hour -------- 13
Test Retake 2hour -------- 25
Test Retake 3hour ---------36
Disqualified Retake 1hour -- 25
Disqualified Retake 2hour -- 49
Disqualified Retake 3hour -- 72
Advanced 1hour ---------- 10
Advanced 2hour ---------- 19
Advanced 3hour ---------- 27
Is it possible to do that, change the cost correpsonding to what one is entered in lesson_type...??? And where do i put it??? What do i do with it??
Thanks, dragon2309
View 4 Replies
View Related
Aug 17, 2014
My problem now is about evaluating the result of that query in order to calculate quarterly and annual performances. The query returns correctly:
1) NULL - when I don't have values in the given month
2) 0 - when one of the components of the performance formula is 0
3) value (positive or negative) which can include any positive or negative value
In order to calculate the quarterly values I need the performance of the last month in the quarter and the first month in the quarter. My monthly values are stored in columns so say for Quarter 1, I would need something like this:
Code:
[mar]/[jan]-1
As you might have thought, the problems come when I have NULL or 0 values. Let me give you a couple of examples.
[jan] is NULL
[feb] is a value
[mar] is a value
Than my formula would have been
Code:
[mar]/[feb]-1
Second case:
[jan] is 0
[feb] is 0
[mar] is 0
In this case I would like the formula return '-' because I want to be able to spot the case in which the quarterly performance is 0 because the initial values were 0 from the case in which the performance was actually 0 (i.e., say [jan] = 101 and [mar] = 101 then performance is 0 which is different from having [jan] and [mar] = 0 thus performance '-').You can combine the three states (NULL, 0, value) with the three months and find many combinations (I have found 27 relevant ones).
I initially thought to use SWITCH in SQL to evaluate the 27 combinations but I found out that SWITCH would evaluate non more than 15 conditions. So I guess the solution should be use a function in VBA which does what I was about to do with SWITCH in SQL.
I have built a test function which evaluates the numerator of my formula using only two cases (CASE 1: all variables have a value <>0, and CASE 2: the first month is null, the second is 0 and the third a value). Here the code:
Code:
Function evaluate_s(Var1, Var2, Var3) As Double
Select Case Var1
Case Is <> 0
[code]....
My main question here is what is wrong in the syntax of my function. Why is not correctly evaluating at least the two cases in my function? I always get 0 for all records.
View 8 Replies
View Related
Jun 4, 2014
I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
'Daylight 6:00am - 4:00pm
'Afternoon 4:00pm - 2:00am
'Midnight 2:00am - 6:00am
Code:
Public Function getShiftForRecord(DelayStart As Variant)
On Error Resume Next
Select Case DatePart("h", DelayStart)
Case 6 To 16 'Daylight 6:00am - 4:00pm
[Code] ....
View 11 Replies
View Related
Jun 10, 2015
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:
Code:
MsgBox (checkDATI(Me.CausaleMov, Me.txt_tipomov))
It's not update those field.
View 6 Replies
View Related
Jan 3, 2014
I would like to select a case depending on the output of a function.
This function tests the syntax of the reporting month.
If the syntax is fine nothing should be done further in the main sub else it should return to the Input window for the reporting month.
Somehow it doesn't work out.
Code:
Public Function RepMonthCheck(rep_date As String) as Boolean
If Len(rep_date) <> 6 Or Left(rep_date, 2) > 12 Or Left(rep_date, 2) < 1 Then
MsgBox ("Reporting rep_date is not in the correct format = mmyyyy")
Return False
ElseIf Right(rep_date, 4) > 9998 Then
MsgBox ("No forecast available for year 9998")
[Code] ....
View 3 Replies
View Related
