Ignoring Punctuation

[khomsley]

Is there a way to make access ignore punctuation at the beginning of
an item, such as "Field and Stream" so that it will arrange all
information alphabetically instead of arranging items in quotes
alphabetically, and then arranging items not in quotes
alphabetically?

Thanks

[Queezy]

I'm not sure if you want to use a query, but this query will do what
you want.
SELECT Table.name
FROM Table
ORDER BY IIf(Left(Table. name,1)='"',Rig ht(Table.name,L en(Table1.name)-
InStr(Table.nam e,'"')),Table.n ame);

[Don Leverton]

Function StripQuotes(MyS tr As Variant) As Variant
On Error GoTo StripQuotesErro r

Dim strChar As String, strHoldString As String
Dim i As Integer

' Exit if the passed value is null.
If IsNull(MyStr) Then Exit Function

' Exit if the passed value is not a string.

If varType(MyStr) <8 Then Exit Function

' Check each value for invalid characters.
For i = 1 To Len(MyStr)
strChar = Mid$(MyStr, i, 1)
Select Case strChar
Case """"
' Do nothing
Case Else
strHoldString = strHoldString & strChar
End Select
Next i

' Pass back corrected string.
StripQuotes = strHoldString

StripQuotesEnd:
Exit Function

StripQuotesErro r:
MsgBox Error$
Resume StripQuotesEnd
End Function
=============== =====
In a query:
(AirCode)
SELECT tblMagazines.*, StripQuotes([MagazineTitle]) AS MyTitle
FROM tblMagazines
ORDER BY StripQuotes([MagazineTitle]);

--
--
HTH,
Don
=============== ==============
E-Mail (if you must) My*****@Telus.n et

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

=============== =============== =============== =============== ==============

<kh******@yahoo .comwrote in message
news:11******** ************@p1 5g2000hsd.googl egroups.com...

Is there a way to make access ignore punctuation at the beginning of
an item, such as "Field and Stream" so that it will arrange all
information alphabetically instead of arranging items in quotes
alphabetically, and then arranging items not in quotes
alphabetically?

Thanks

[Don Leverton]

Oops ... forgot to pay the Devil his dues :)
This is simply an adaptation of the StripString() function from
http://support.microsoft.com/default...b;en-us;138774
"Don Leverton" <le************ ****@telusplane t.netwrote in message
news:qNUOh.2822 6$x9.11584@edtn ps89...

Function StripQuotes(MyS tr As Variant) As Variant
On Error GoTo StripQuotesErro r

Dim strChar As String, strHoldString As String
Dim i As Integer

' Exit if the passed value is null.
If IsNull(MyStr) Then Exit Function

' Exit if the passed value is not a string.

If varType(MyStr) <8 Then Exit Function

' Check each value for invalid characters.
For i = 1 To Len(MyStr)
strChar = Mid$(MyStr, i, 1)
Select Case strChar
Case """"
' Do nothing
Case Else
strHoldString = strHoldString & strChar
End Select
Next i

' Pass back corrected string.
StripQuotes = strHoldString

StripQuotesEnd:
Exit Function

StripQuotesErro r:
MsgBox Error$
Resume StripQuotesEnd
End Function
=============== =====
In a query:
(AirCode)
SELECT tblMagazines.*, StripQuotes([MagazineTitle]) AS MyTitle
FROM tblMagazines
ORDER BY StripQuotes([MagazineTitle]);

--
--
HTH,
Don
=============== ==============
E-Mail (if you must) My*****@Telus.n et

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

=============== =============== =============== =============== ==============

<kh******@yahoo .comwrote in message
news:11******** ************@p1 5g2000hsd.googl egroups.com...

Is there a way to make access ignore punctuation at the beginning of
an item, such as "Field and Stream" so that it will arrange all
information alphabetically instead of arranging items in quotes
alphabetically, and then arranging items not in quotes
alphabetically?

Thanks

[Don Leverton]

Aaaack! One more thought ...
You could actually design an update query, using that function to "Find and
replace" all of the titles that had the quotation marks.

If you decide to do that, though ... please make a backup copy of your table
FIRST, so that I don't have to feel bad for telling you something that
results in disaster if it doesn't work correctly. :)