Alphabetizing - With An Apostrophe
Apr 2, 2005
Hi, I may be dense, but I cannot find an answer to this question anywhere. If it has a simple and obvious answer, my apologies.
I have created an index with a field called "Surname" -- I need to know how to force Access to alphabetize the names disregarding the apostrophes (or a space) - for example, O'Brien. The way it works now, all the O' (s) go at the beginning of the Os (O'Brien, O'Malley, O'Neal, Oakley). I want it to go - Oakley, O'Brien, Oddfellow, O'Malley.
Is there a way to do this and still display the apostrope? I feel that I could set something for the field in the design view, but for the life of me I can't figure it out.
Thanks in advance for any help. :)
Jeri
View Replies
ADVERTISEMENT
Mar 28, 2013
I have been using Access for many years, but have come against a problem which I can't fathom.
I have a database with a field for "Organisation Type" which contains all the various options which can be chosen by the user.
However, when I'm in a field, it refuses to show that list in alphabetical order, defaulting to an order I don't recognise which is most unuseful as it makes it almost impossible to find the right item, especially for a novice user.
I thought this was perhaps because the form was based on the database table, and this didn't allow sorting.
So, to test my theory, I created a query based on the table and added alphabetical sorting to that field. As soon as I use the query as a basis for the field, I find the problem remains.
View 4 Replies
View Related
Nov 2, 2004
For example, if I'm coding an insert manually, if I need to insert a string from a textbox, I have to do something like this
mycn.execute "Insert into Test(FName,LName)values('" & me.text1 & "','" & me.text2 & "')
I have to put a ' at the end of each string values
Is there any way to bypass this? I mean I could put a validation code and each time I detect an apostrophe I handle it so that it doesn't create a problem
But I mean a simple way to bypass this
View 2 Replies
View Related
Mar 24, 2008
Hi,
In my database if the user wants to change a record, I make them store the old record in another table. I am using the following code.
Private Sub cmdArchive_Click()
On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim i As Integer
Dim s As String
Dim sSQL As String
sSQL = "INSERT INTO tblNamesArchive ( txtGivenName, txtSurname )" _
& "VALUES ( '" & txtGivenName & "', '" & txtSurname & "');"
Debug.Print sSQL
Set conn = CurrentProject.Connection
conn.Execute sSQL
GoTo ThatsIt
ErrorHandler:
Select Case Err.Number
Case -2147217908 'command text not set
Case -2147217865 'cannot find table
Case 3021 'no records
Case Else
MsgBox "Problem with cmdArchive_Click()" & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description
End Select
ThatsIt:
conn.Close
End Sub
I have no problems making the following code work except if there is an apostrophe " ' " in either the txtGivenName or txtSurname . For example O'Keefe it shows an error but not O"Keefe! How do I fix this?
Thanks
Raghu Prabhu
Melbourne.
View 2 Replies
View Related
Dec 14, 2007
sql = "SELECT Table1.* INTO Table1 IN '" & sExpFileName & "' FROM Table1"
CustDB.Execute sql
where the sExpFileName contains the path of The Target MS Access DB
The Query works fine. But recently I discovered that if my target File name contains apostrophe It gives error as Query Input must contain at least one table or query
Is there any way to solve this problem. Please suggest me some way
View 7 Replies
View Related
Apr 27, 2005
Hey, I have a problem with my application......when the user trying to insert string with apostrophe into txtDesc (text box), the code returns error...
Run-time error '3057'
the database inserts any records excellently, but not with the apostrophe....
here is my code:
Code:sql = "INSERT INTO M_Stock ([StockRef], [StockGroup], [Desc], [Location], [Category], [UOM], " & _ "[RQ], [Remark0], [Remark1], [Remark2]) " & _ "VALUES ('" & txtStockRef & "', '" & cmbSG & "', '" & txtDesc & "', '" & cmbLoc & "', " & _ "'" & cmbCat & "', '" & cmbUOM & "', '" & txtRQ & "', '" & txtRemark0 & "', " & _ "'" & txtRemark1 & "', '" & txtRemark2 & "');"Set dbs = CurrentDbdbs.Execute sql
Can someone help me with this problem? Thanks in advance
View 1 Replies
View Related
Mar 3, 2014
I have a query written in Visual Basic as follows
UA1 = Forms(ParName).Form.NOM
UA2 = Forms(ParName).Form.PRENOM
UA3 = Forms(ParName).Form.CARTE
Forms(ForName).Recordset.FindFirst "[NOM] = '" & UA1 & "'" & " And " & _
"[PRENOM] = '" & UA2 & "'" & " And " & _
"[CARTE] = '" & UAE & "'"
This query works and I can spot the record based on 3 fields and display it. Now I have discovered that we have a person who has an apostrophe in his name like O'Brian. The above code gives a syntax error in this case.
View 3 Replies
View Related
Jan 9, 2014
I am combining fields one that has apostrophe's and the other does not. exp "if this field is null use data from this field (which does not have an apostrophe) otherwise use current field (which has apostrophe)
"red"
blue
orange
"yellow"
How can I change that in the query without changing the previous query to
"red"
"blue"
"orange"
"yellow"
View 2 Replies
View Related
Oct 28, 2014
I have a Dell keyboard at work and I am trying to find the keycode to the apostrophe/quotation key. I want to disable this when pushed with the ctrl key. I've searched online but could not find anything that shows that specific key. I only see apostrophe keys or quotation keys but not the combination of both which is what I have.
View 4 Replies
View Related
Jul 20, 2006
hi,
i am getting an error in my coding for a INSERT statement becasue the text of the field contain an apostrophe.
that is my code is
DoCmd.RunSQL "INSERT INTO tbl_StartEndDates_OVERHEAD ( Last_Name)" _ & "VALUES ('" & Forms!OverheadEmployeeDates!lblLast_Name & "');"
but the text in Forms!OverheadEmployeeDates!lblLast_Name is "D'Amereo".
I know that this works for all other Last_Names without an apostrophe in it
does any one know the correct coding for this?
thansk
tuk
View 3 Replies
View Related
Mar 17, 2014
I have a button in one form that opens a different form and want it to show specific data to display. I choose this option in the command button wizard and set it up to have the name from my main form be the same name as my new form to be opened. Everything works perfectly until I choose a name that has an apostrophe in it. So if I choose the name "Ender's Game" it gives me the alert:
"Syntax error (missing operator) in query expression '[bookName]='Ender's Game".
I think I've found where the problem is, I just can't figure out how to fix it. When I looked at the onClick embedded macro it has the Where Condition as:
=="[bookName]= & "'" &[BookName] & "'"
Is there a way to get Access to accept that an apostrophe in the name is meant to be part of the string itself?
View 7 Replies
View Related
Feb 17, 2014
I use the follwing code to filter a report based on the listbox selection on a form. Below is the code I use, the problem it will error if the results have an apostrophe in the string.
Private Sub FilterDesc_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.ListCarrier.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Carrier"
[Code] ....
View 7 Replies
View Related
