Lowercase to uppercase Results

How can i transform the data entries by a user in lowercase to uppercase.

Hello,

I was searching for some way that I could change all the tables from lowercase names to uppercase. I guess I should have just done all uppercase to begin with but I try to use lower case for everything to keep from having confusion.
I am using Access 2007.
Thanks in advance. Any help would be great. I can't find anything other than an SQL reference to alter table.

how to run a code which will automatically convert to uppercase letter when key in lowercase letter?

anyone can help?

thanks.

Hello Dear friends, I have a question about TextCases in Access Text and Combo Boxes.

When entering the data in text or combo box I want that the text case automatically be changed as I need.
I use this VBA Code:

Model.Text = UCase(Model.Text) 'Converts LowerCase To UpperCase
Model.Text = LCase(Model.Text) 'Converts UpperCase To LowerCase
Model.Text = StrConv(Model.Text, vbProperCase) 'Converts All To Normal (Proper) Case

These codes work very well (Converting Cases as needed), but can't use space bar when writing two sentences(If i have to write "Hello World" it's writing Helloworld then i have to go back and then hit to space between Hello World)


Then I put the code on Before Update Event, but getting the error "Run-Time Error '2115' The Macro or function set to the beforeupdate or validationRule property for this field preventing ms access from saving the data in the field


What I did incorrectly, Please help!

Thank you in advanced

Does anyone know how I can implement the following code into my existing "ProperCase" module so that it would work as one. The ProperCase code I got from these forums works great and has an "Exception List", but the only downfall is it can't detect numeric values before an Alpha character.

Any help would be greatly appreciated as I can't seem to figure out how to integrate the 2 together.

This code checks to see if there is an Alpha character after a number. (Example: 418c centre street) and would then convert it to upper case (418C Centre Street):

	Code:
	Function NumericAddress(strIn As String) As String
Dim arrString() As String
Dim j As Integer
Dim I As Integer
strIn = StrConv(strIn, vbProperCase)
arrString = Split(strIn, " ")
For j = 0 To UBound(arrString)
    For I = 1 To Len(arrString(j))
        If IsNumeric(Mid(arrString(j), I, 1)) Then
            arrString(j) = StrConv(arrString(j), vbUpperCase)
            Exit For
        End If
    Next I
Next j
NumericAddress = Join(arrString(), " ")
End Function

This is the current code I use for ProperCase with an Exception List:

	Code:
	Option Compare Binary
' So that MacDonald  Macdonald in this module only!  The mdlGetException module is set to Option Compare Database
' so that exceptions need only be typed into the exception table once.
Option Explicit                ' Require variable declaration

Global Const mstrTableName = "Exceptions"  'Exception table and field names.  These may be changed if needed.
Global Const mstrFieldName1 = "[Exception List]"   'Holds the exception word.  Case doesn't matter.
Global Const mstrFieldName2 = "Replacement"     'Holds the replacement for the exception.  Capitalize this the way you want
                                                                          'it to appear.
Global Const vbUpperCase = 1  'Converts the string to uppercase characters.
Global Const vbLowerCase = 2  'Converts the string to lowercase characters.
Global Const vbProperCase = 3     'Converts the first letter of every word in string to uppercase.
Global Const vbSentenceCase = 4     'Converts the first letter of the string to uppercase, all others to lowercase.

'Message box types
Global Const MB_OKCANCEL = &H1
Global Const MB_ABORTRETRYIGNORE = &H2
Global Const MB_YESNOCANCEL = &H3
Global Const MB_YESNO = &H4
Global Const MB_RETRYCANCEL = &H5

'Message box icons
Global Const MB_ICONSTOP = &H10
Global Const MB_ICONQUESTION = &H20
Global Const MB_ICONEXCLAMATION = &H30
Global Const MB_ICONINFORMATION = &H40

'Message box default buttons
Global Const MB_DEFBUTTON1 = &H0
Global Const MB_DEFBUTTON2 = &H100
Global Const MB_DEFBUTTON3 = &H200

'Message box return values
Global Const MB_OK = 1
Global Const MB_CANCEL = 2
Global Const MB_ABORT = 3
Global Const MB_RETRY = 4
Global Const MB_IGNORE = 5
Global Const MB_YES = 6
Global Const MB_NO = 7

Public dbs As Database, rst As Recordset
Dim varImpWord As Variant, varPropWord As Variant, varOutput As Variant, varTemp As Variant
'                ^--Improper word            ^--Proper word              ^--Output string
Dim intResult As Integer
Public varSepChar As Variant, varWordList() As Variant, intSepCharCnt As Integer, intWordCount As Integer
Dim booCallFromPropMan As Boolean
Function ProperCase(varPropCaseInput As Variant, intConversion As Integer) As Variant
' This function takes a variant and applies word caps. It also has an exception list stored as an Access table where
' it overrides the word caps rule. Individual cases will have to be added to the exception list manually or at run time by
using the
' ProperManager wrapper function. The exception list cannot contain wildcards; however, capitalization of the exception
(first
' column) isn't important.  The replacement (second column) must be capitalized the way you want it to appear.  The table
' contains two indexed columns of type text and length 50. Table name and column names are given in the constants above.
' The return value is a variant containing the string in Proper Case.
Dim I As Integer
                        
        If IsNull(varPropCaseInput) Then GoTo ProperCase_Exit
                        varOutput = Null        'Initializes variables
                        varImpWord = Null
                        varPropWord = Null
        Select Case intConversion
                Case Is = vbProperCase
                        ParseWords (varPropCaseInput)
                        For I = 0 To Len(varPropCaseInput)      'Iterates through word list
                                If IsEmpty(varWordList(I, 0)) Then      'Word element is empty
                                        If IsEmpty(varWordList(I, 1)) Then      'Word separator element is empty - completed
proper casing.
                                                ProperCase = BuildOutput(varPropCaseInput)      'Build output string
                                                Exit For
                                        End If
                                Else    'Word element is not empty
                                        varImpWord = varWordList(I, 0)      'Get word
                                        varTemp = GetException(varImpWord)      'Check word against exception list
                                                If Len(varTemp)  0 Then   'Word is present in the exception list
                                                        varPropWord = varTemp       'Get replacement word for exception
                                                Else    'Word is not in the exception list
                                                        varPropWord = UCase(Left(varImpWord, 1)) & LCase(Mid(varImpWord, 2)) 
'Proper case word
                                                        If booCallFromPropMan = True Then ManageException
                                                        'Goes to exception management sub if ProperCase was called from
ProperManager
                                                End If
                                        varWordList(I, 0) = varPropWord     'Inserts the proper cased word back into the word
list
                                End If
                        Next I
                Case Is = vbUpperCase
                        varOutput = UCase(varPropCaseInput)
                Case Is = vbLowerCase
                        varOutput = LCase(varPropCaseInput)
                Case Is = vbSentenceCase
                        ParseWords (varPropCaseInput)
                        For I = 0 To Len(varPropCaseInput)      'Iterates through word list
                                If IsEmpty(varWordList(I, 0)) Then      'Word element is empty
                                        If IsEmpty(varWordList(I, 1)) Then      'Word separator element is empty - completed
proper casing.
                                                ProperCase = BuildOutput(varPropCaseInput)      'Build output string
                                                Exit For
                                        End If
                                Else    'Word element is not empty
                                        varImpWord = varWordList(I, 0)      'Get word
                                        varTemp = GetException(varImpWord)      'Check word against exception list
                                                If Len(varTemp)  0 Then   'Word is present in the exception list
                                                        varPropWord = varTemp       'Get replacement word for exception
                                                Else    'Word is not in the exception list
                                                        If varWordList(I, 2) = 1 Then
                                                                varPropWord = UCase(Left(varImpWord, 1)) &
LCase(Mid(varImpWord, 2))
                                                                'If this is the first word in the string, then proper case
word
                                                        Else
                                                                varPropWord = LCase(varImpWord)     'Convert word to lower
case
                                                        End If
                                                        If booCallFromPropMan = True Then ManageException
                                                        'Goes to exception management sub if ProperCase was called from
ProperManager
                                                End If
                                        varWordList(I, 0) = varPropWord     'Inserts the proper cased word back into the word
list
                                End If
                        Next I
        End Select
        ProperCase = varOutput
        booCallFromPropMan = False
        
ProperCase_Exit:
End Function
Function ProperManager(varPropManInput As Variant, intConversion As Integer) As Variant
' This function can be optionally used to *wrap* the ProperCase routine and perform some management of the exception list.
' Deleting items from the list is up to the individual developer.

        booCallFromPropMan = True   'Tells the ProperCase function to call ManageExceptions when needed.
        ProperManager = ProperCase(varPropManInput, intConversion)
        
ProperManager_Exit:
End Function
Sub ManageException()   'Queries the user and adds exceptions to the list

        If varPropWord  varImpWord Then
        ' ProperCase has changed the text. Confirm with user.
                intResult = MsgBox("ProperCase has changed '" & varImpWord & "' to '" & varPropWord & "'. Is this correct?",
_
                MB_YESNO + MB_ICONQUESTION, "ProperCase Exceptions Manager")
                If intResult = MB_NO Then   'Not correct
                        intResult = MsgBox("Do you want to add '" & varImpWord & "' to the exception list?", MB_YESNO + _
                        MB_ICONQUESTION, "ProperCase Exceptions Manager")
                        If intResult = MB_YES Then  'Add to exception list
                                varPropWord = InputBox("How would you like this word capitalized in the future?", ,
varImpWord)
                                'Get the replacement capitalization for this word in the future.
                                Set dbs = DBEngine(0)(0)
                                dbs.Execute "INSERT INTO " & mstrTableName & " (" & mstrFieldName1 & ", " & mstrFieldName2 &
") VALUES (" & "'" & varImpWord & "', '" & varPropWord & "');"
                                'Inserts the exception word into column 1 and the replacement word into column 2 of the
exception table.
                        Else    'Don't add to exception list
                                varPropWord = varImpWord
                        End If
                Else    'Correct - keep changes
                End If
        Else    'No change
        End If

End Sub
Function BuildOutput(varBuildInput As Variant) As Variant       'Builds proper cased output string
Dim ii As Integer

        For ii = 0 To Len(varBuildInput)    'Iterates through proper cased word list
                If IsEmpty(varWordList(ii, 0)) Then     'Element does not contain a word
                        If IsEmpty(varWordList(ii, 1)) Then     'Element does not contain a word separator
                                Exit For    'All words and separators have been found.  Output string is complete.
                        Else
                                varOutput = varOutput & varWordList(ii, 1)      'Concatenate word separator to output string
                        End If
                Else
                        varOutput = varOutput & varWordList(ii, 0)  'Concatenate word to output string
                End If
        Next ii
        
        BuildOutput = varOutput
        
End Function
Public Sub SetSepChars()
'Sets up an array of the accepted word separation characters.  These may be modified by the developer as needed.

        varSepChar = Array(" ", "-", ".", ",", ":", ";", "(", ")", "", "/", "'", Chr(9), Chr(10), Chr(13))
'                                                                                 ^--Tab  ^--{LF}  ^--{CR}
        intSepCharCnt = UBound(varSepChar)    'Counts the number of word separation characters in the array

End Sub
Function ParseWords(ByVal varParseInput As Variant) As Variant
Dim intStartPos As Integer, intEndPos As Integer, intArrayIndex As Integer, varTestChar As Variant, _
intTestPos As Integer, iii As Integer, varWord As Variant

        If IsNull(varParseInput) Then
                GoTo ExitParseWords
        Else
                SetSepChars
                ReDim varWordList(Len(varParseInput), 2) As Variant
                'Declare word list array with as many rows as there are characters in the varParseInput variable (to be
conservative).
                'Column 0 holds the final parsed words, column 1 holds the word separators, and column 2 holds an integer
                'describing the corresponding word's position in the input string (1 for first word, 2 for second word,
etc.).
                intArrayIndex = 0       'Initialize variables
                intStartPos = 1
                intEndPos = 0
                intWordCount = 0
                For intTestPos = 1 To Len(varParseInput) 'Iterate through entire input string
                        varTestChar = Mid(varParseInput, intTestPos, 1)     'Get character to be tested
                        For iii = 0 To intSepCharCnt  'Iterate through word separator characters
                                If varTestChar  varSepChar(iii) Then    'If something other than a word separator
                                        If iii = intSepCharCnt Then   'All word separators have been compared with
                                                varWord = varWord & varTestChar     'Concatenate with previous characters in
the same word
                                                If intTestPos = Len(varParseInput) Then     'Entire string has been tested
                                                        varWordList(intArrayIndex, 0) = varWord     'Insert word into proper
element
                                                        intWordCount = intWordCount + 1     'Advance word counter
                                                        varWordList(intArrayIndex, 2) = intWordCount    'Enumerate word in
array
                                                        intArrayIndex = intArrayIndex + 1   'Advance array index counter
                                                End If
                                        End If
                                Else    'If test character is a word separator
                                        If Not IsNull(varWord) Or intTestPos = Len(varParseInput) Then
                                        'Complete word has been parsed or end of string has been reached
                                                varWordList(intArrayIndex, 0) = varWord     'Insert word into array
                                                intWordCount = intWordCount + 1 'Advance word counter
                                                varWordList(intArrayIndex, 2) = intWordCount    'Enumerate word in array
                                                intArrayIndex = intArrayIndex + 1   'Advance index array counter
                                        End If
                                        varWordList(intArrayIndex, 1) = varTestChar     'Insert word separator into array
                                        intArrayIndex = intArrayIndex + 1   'Advance array index counter
                                        If Not IsNull(varWord) Then varWord = Null      'Re-initialize word variable
                                        Exit For
                                End If
                        Next iii
                Next intTestPos
        End If
        ParseWords = varWordList        'Set output to word list
        'GoSub PrintWordList     'Test function by printing to Debug window - uncomment this line if you want to use
        GoTo ExitParseWords
        
PrintWordList:
        For intArrayIndex = 0 To UBound(varWordList)    'Iterate through word list array
                If Not IsNull(varWordList(intArrayIndex, 0)) Then Debug.Print varWordList(intArrayIndex, 0)
                'Print each element in array to the Debug window
        Next intArrayIndex
        Return
        
ExitParseWords:
End Function
Function GetWord(ByVal varGetInput As Variant, intIndex As Integer) As Variant
'This function will parse a string and return whatever word you like.
'Accepts: input field and index number starting with 1
'Returns: the nth word in the input string.
Dim iv As Integer

        If IsNull(varGetInput) Then
                GetWord = Null
                GoTo ExitGetWord
        Else
                If intIndex < 1 Then    'Can't get a zero word!
                        MsgBox ("Please enter a number greater than or equal to 1.")
                        GoTo ExitGetWord
                Else
                        CountWords (varGetInput)
                        If intIndex - 1 > intWordCount Then     'Index number given is greater than the number of words in
the string.
                                MsgBox ("Please enter a number between 1 and " & intWordCount & ".")
                        Else
                                For iv = 0 To UBound(varWordList)   'Iterates through word list
                                        If varWordList(iv, 2) = intIndex Then   'Column 2 (word count) equals the index
number asked for.
                                                GetWord = varWordList(iv, 0)    'Retrieves the correct word
                                                Exit For
                                        End If
                                Next iv
                        End If
                End If
        End If
        
ExitGetWord:
End Function
Function CountWords(ByVal varCountInput As Variant) As Integer
'Counts the words in a string.

        ParseWords (varCountInput)
        CountWords = intWordCount

End Function
Sub TestParseWords()    'Tests the ParseWords function
Dim strAString As String, v As Integer, intCnt As Integer

        strAString = "Once, I thought I could write a module like this easily; now, I know better."
        
        'Find out how many separated words are present
                intCnt = CountWords(strAString)
                Debug.Print intCnt
        
        'Now call the other function to retrieve each one in turn
        For v = 1 To intCnt
                Debug.Print GetWord(strAString, v)
        Next v

End Sub
Sub TestProperManager()     'Tests the ProperManager function

        Debug.Print ProperManager("GREEN TREES have purPLE LEAVES, MCDONALD.", vbLowerCase)

End Sub
Sub TestProperCase()        'Tests the ProperCase function

        Debug.Print ProperCase("GREEN TREES have purPLE LEAVES, mcdonald.", vbSentenceCase)

End Sub



Hi,

I need the text in the field to allow both lower and upper case but when the user enters "i" (only 1) it changes to "I" , "ii" works just fine.
How can I stop this??

I guess I could write an event code like:

Dim LowerCase, UpperCase
UpperCase = Me![FieldName]
LowerCase = LCase(UpperCase)
If Me.[FieldName].Value = "i" Then
Me![FieldName].Value = LowerCase
Else...

Any other ideas????

Thanks
Paul

Hello,

I'm totally new to access...8 years ago I was reasonably proficient with foxpro and oracle, but literally haven't even thought about databases and queries since then. The non-profit where I work uses access and wants the data stored in uppercase (they don't use forms at all) but a lot of old data is in lowercase or mixed case. I was just puttering around with this and was only going after 1 field at this point in my where clause but when executed I get a pop-up that requests I enter parameter value wheat.fname.... Here is my query
SELECT WHEAT.*
FROM WHEAT
WHERE
WHEAT.FNAME UCASE(WHEAT.FNAME);
I thought this would return all rows where the stored data for FNAME doesn't match the forced uppercase of the FNAME field -- all records with mixed or lowercase in fname field.

Can anyone please tell me where I'm falling short here?
Thanks for the assistance!

is it possible to convert words, input as lowercase, to uppercase automatically when user moves to next field?

Was looking for the code for optional, posted the codes below as a future reference for others.

Trying to put an input mask that allows for an optional two last digits.
AA-AAA-AAAAA aa
Tried AA-AAA-AAAAA-aa
But the last dash stays example 11-222-22222- or 11-222-22222-33 the "" prints what ever literal follows it, in this case a dash.

Any suggestions on how to allow for a number, with an optional 2 digits but with out a traling dash?

Some more example can be found here:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
But, an optional "-" followed by optional numbers is not covered.
so, my "-" is just hanging out there.


	Code:
	 
The following characters define an Input Mask:
Character - Description 0 - Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed). 
9 - Digit or space (entry not required, plus and minus signs not allowed). 
# - Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data
is saved; plus and minus signs allowed). 
L - Letter (A to Z, entry required). 
? - Letter (A to Z, entry optional). 
A - Letter or digit (entry required). 
a - Letter or digit (entry optional). 
& - Any character or a space (entry required). 
C - Any character or a space (entry optional). 
. , : ; - / Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings
in the Regional Settings Properties dialog box in Windows Control Panel). 
 
< - Causes all characters to be converted to lowercase. 
> - Causes all characters to be converted to uppercase.  i.e UCase()
 
! - Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask
always fill it from left to right. You can include the exclamation point anywhere in the input mask. 
 
 - Causes the character that follows to be displayed as the literal character (for example, A is displayed as just A). 
 
Examples: LLLLLL - 6 Letters [A-Z] are 'required'
AAAAAA - 6 Letters or Digits are 'required'
NOTE: Select the Input Mask Field for a Field in Table Design View on the bottom Pane and press F1



Hello All, Using 2003; I have a database that imports data from a spreadsheet. When I look at the data in the table; its in all caps. but when I view it in a form for editing or report, some of the data is in lowercase. I need it to be upper case like in the table. I tried the Keypress code but it only changes new data not existing. Why does it change case in the forms and on the report and how can I force it to uppercase.
thanks.

One of the fiels of my report (made out of a query), I have employees' last names, all in uppercase. How can I switch them to Uppercase on the first name's letter and lowercase on the others?

Thanks,

Camedeiros

On a form within excel, there is a textbox which allows the user to enter a password. This is then validated using a button which uses the If/Then statements. However, it is designed for the user to enter the password in lowercase. If the user enters it in uppercase or a mixture of both, the password is not validated.

I am looking for a code that will allow the user to be able to enter the password in either or a mixyure of both upper and lowercase.

Hi

I'm using this code that I found on the internet to correct capitilisation


	Code:
	Public Function ProperCase(AnyText As Variant) As String
'r_cubed
    
    'Convert passed text to all lowercase. Use ProperCase() as you would a built-in Access function.
    'If passed value is a null, ignore all the stuff below.
    
    If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase
        
    Dim intCounter As Integer, OneChar As String
    
    'First convert to initial cap, followed by all lowercase.
    AnyText = UCase$(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2))
    
    'Look at each character, starting at the second character.
    For intCounter = 2 To Len(AnyText)
        OneChar = Mid$(AnyText, intCounter, 1)
        'If current character (OneChar) is a space or hyphen...
        Select Case OneChar
            Case "-", "/", ".", "'", "&", "(", " "
                '...convert the character after space/hyphen/slash/period/apostrophe/ampersand to uppercase.
                ' Such as A.B.C. Industries, Sharron O'Conner, B&B Mfg
                AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter +
2, 255)
            
            Case "c"
                ' Take care of the McAfee's, McDonalds, McLaughlins
                
                If Mid$(AnyText, intCounter - 1, 1) = "M" Then
                    AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText,
intCounter + 2, 255)
                Else
                    ' Handle the MacDonalds, MacPherson and such
                    If (intCounter > 2 _
                      And Mid$(AnyText, intCounter - 2, 2) = "Ma") Then
                        AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText,
intCounter + 2, 255)
                    End If
                End If
    
            Case " "
                Select Case Mid$(AnyText, intCounter + 1, 2)
                    Case "de"
                        'Add any other exceptions here Example: Oscar de La Hoya
                        AnyText = Left$(AnyText, intCounter) & LCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText,
intCounter + 2, 255)
                    Case Else
                        ' Example: A B C Manufacturing
                        AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText,
intCounter + 2, 255)
    
                End Select
        End Select
    Next
    
    'All done, return current contents of AnyText variable.
    ProperCase = AnyText
    
Exit_ProperCase:
    
End Function

My problem is that i have two areas this doesn't help

1) For degree titles i.e. PhD and MSc

2) For my company we have a set of "initials" to refer to eachother as these can range from two to five characters which should all be capitalised but without "." between the letters - if I need to I am happy to put anything round these characters to distinguish them i.e. [ ]

So for example we could have the sitiation where I want a project title to be

PhD Project - [ABCDE] (Blah Blah Blah Blah)

And with the code above I get

Phd Project - Abcde (Blah Blah Blah Blah)

Can anyone who can actually make any sense of the code above suggest some new "Cases" and their associated code that I can use?

Many thanks

Hi,

I am new to programming in Access and therefore need some assistance, as my knowledge is very limited.

I have a number of Model numbers that due to the nature of how they are provided could have numerous variations. I need to know if I can write code that will automatically produce all the possible variations, for instance:

Model No: B1322N1GB

Because of the way customers write their information the B could be mistaken for the numerical character "8"

The numerical character "1" could be mistaken for the alphabetical characters "I" [Uppercase] or "l" [Lowercase L]

The numerical character "2" could be mistaken for the alphabetical character "Z"

The alphabetical character "N" could be mistaken for the alphabetical character "H"

Also as some of the model numbers include the alphabetical character "D" this could be mistaken for the alphabetical character "O" or numerical character "0" [zero]

I need to be able to write code that could produce the model number with all these possible variations.

This would give me a complete listing of all those possibilities and I could then produce a process that would pick these out and then allocate the correct model number.

Any assistance with this would be most appreciated

FAB

John Lee

i was trying to enforce referential integrity for my tables but was told that tblReviews has data that violates data integ. rules

while looking thru tblReviews i found some authorCodes that started with lowercase letters whereas tblAuthors has a field Code that starts with uppercase.

i'm basically wanting to write a select query that'll show me all authorCodes in tblReview that starts with a lowercase. I know that STRCONV will do conversion, but what function will tell me the status of a letter, whether the letter is uppercase or lowercase?

i just want to see these problem records with the lowercase letters before i go about updating them & seeing if that clears my data integ. problem

[This message has been edited by arage (edited 08-09-2001).]

Hi all,

I've used this function to get the MsAccess database name :

Function GetDbName()
'Returns the filename of the currently opened MDB
GetDbName = CurrentProject.Name
End Function

and it worked. But the database name is in this format : ThisIsTheDatabaseName.mdb. The problem is that it returns this in all lowercasing : thisisthedatabasename.mdb.

Can anyone give me a method to return in the same format lowercase & uppercase as the file name ?

Thanks,
Doris

I have a simple task that I am not able to get working. I am creating an Outlook Mailitem to display, but when I try to add attachments to it, I get a runtime error 438 - Object doesn't support this property or method.

The error is occurring when I try to use the mailitem's Attachments.Add method. It looks to me to be a reference problem. I have a reference to the Outlook.12.0 library.

Another interesting this is when I type the code in, it shows the Add method in the list of methods. That tells me it's in the definition of the Attachments class. After selecting the Add method from the list, finish the line and press enter, the Add method changes to all lowercase, like it doesn't recognize it. I've tried typing in the other methods of the Attachments class and those methods stay uppercase.

I've also tried late binding, but the same things happens.

Below is my code, any ideas?

Thanks, Tom

Private Sub SendEmailFromMemo()

Dim otlkApp As Outlook.Application
Dim otlkItem As Outlook.MailItem
Dim db As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim emailstr As String


' validate all info is filled out
If Me.MemoSubject = "" Then
MsgBox "Enter a Subject First.", , "Cannot Send Email"
Exit Sub
ElseIf Me.MemoText = "" Then
MsgBox "Enter Message First.", , "Cannot Send Email"
Exit Sub
ElseIf Me.MemoClient = 0 Then
MsgBox "Select a Contact First.", , "Cannot Send Email"
Exit Sub
End If

' get the email address of the contact to send to
Set db = CurrentProject.Connection
Set rst.ActiveConnection = db
strSQL = "SELECT Email FROM Client where Client = " & _
Me.MemoClient
rst.Open strSQL, db, adOpenKeyset, adLockOptimistic

If rst.Fields("Email") = "" Then
MsgBox "Contact Has No Email Address.", , "Cannot Send Email"
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Else
emailstr = rst.Fields("Email")
End If

rst.Close

' Open a connection to outlook and create mailitem
Set otlkApp = CreateObject("Outlook.Application")
Set otlkItem = otlkApp.CreateItem(olMailItem)

' enter info
otlkItem.To = emailstr
otlkItem.Subject = Me.MemoSubject
otlkItem.Body = Me.MemoText
otlkItem.Save

' see if there are any attachments to send
strSQL = "SELECT * FROM MemoAttachments WHERE MemoID = " & _
Me.MemoID
rst.Open strSQL, db, adOpenKeyset, adLockOptimistic

If rst.RecordCount > 0 Then

Do Until rst.EOF
If FileExit(rst!FileName) Then
otlkItem.Attachments.add rst!FileName
End If
rst.MoveNext
Loop
End If

' display the email
otlkItem.Display

' clean up and exit
Set rst = Nothing
Set db = Nothing
Set otlkItem = Nothing
Set otlkApp = Nothing

End Sub

Is is possible to have Access convert . switch / replace characters in lower case into uper case?


Eg I have an input Mask for Zip Code (Netherlands styeZ)
the ZIp code over there is as follows

4 digits then 2 characters
eg. 1276 HG , 3873 JY , 8120 PL

I would like the database to automatically switch/convert lowercase text entered into Upper Case
eg. If i were to enter " 6123 kr " as i press enter/ go to the next field on the table to enter data the " kr " are turned into uppercase

How is this possible?

I have a table that is created by a MakeTable qry. After the table has been created, one of the field names is in lowercase. If I change that field name to all uppercase in Design View of the table, save and close, the field name reverts back to lowercase when the table is reopened. If I change the field name to uppercase when the table is in Datasheet View, save and close, the uppercase format "sticks" and is not reset to lowercase when reopened. Why would a change of case not be possible when made in Design View but be possible in Datasheet View?

Thx for insight.

Wm

I am trying to write a query to convert text in a field from upper case to title case. I can easily convert text into lowercase using the lcase function or if I wanted upper case using the ucase function, but there does not appear to be a function to convert to title case. i.e first letter of a world in capitals and the rest lower case.

Assuming was converting to lower case my formula looks like this:

Title Case Salutation: LCase(Format([Upper Case Salutation]))

Where "Title case salutation" is the name of the field to be and "upper case salutation" is the name of the current field

Is there a Tcase formula or similar to do the same but convert to title case?


Not finding an answer? Try a Google search.