Access Dash Problem

[Leo R.]

I have an Access problem that involves the lack of recognition of dashes. We have thousands of entries that include dashes, such as CJ1-22 and CJ12-2, yet Access reads them without the dashes, thus organizing these previous two entries side by side. Is there any way to make Access recognize the dashes when sorting records? Or is there an easy way to replace all the dashes with periods, which we found are recognized in a sort? I am not very computer literate, so any advice or help would be greatly appreciated

Thanks
Leo

 

[Charanjeev Singh]

Hi Leo,
You might want to lookup KB236952 - PRB: Sort Order Has
Changed with Microsoft Jet version 4.0
(http://support.microsoft.com/default.aspx?scid=kb;EN-
US;236952) which talks about exactly the same issue and
also clearly states that "This behavior is by design. You
cannot change any settings to affect this behavior."

You can replace the dashes by a period or a tilde (~) or
any other character except a dash or quotation mark (').
You could use the following Update Query to do the same.
Just replace the ~ with whatever character you wish to use
in lieu of he dash.

UPDATE Table1 SET Table1.Data = Replace([Table1]![Data],"-
","~");

Now, if you want to export your data into another software
that needs those dashes, you could build a SELECT query as
below and use this query to import data into the software.

SELECT Replace([Table1]![Data],"~","-") FROM Table1;

Hope that answers your questions.

Charanjeev Singh
Technical Consultant
Microsoft Access Developer Support

-----Original Message-----
I have an Access problem that involves the lack of

recognition of dashes. We have thousands of entries that
include dashes, such as CJ1-22 and CJ12-2, yet Access
reads them without the dashes, thus organizing these
previous two entries side by side. Is there any way to
make Access recognize the dashes when sorting records? Or
is there an easy way to replace all the dashes with
periods, which we found are recognized in a sort? I am not
very computer literate, so any advice or help would be
greatly appreciated.

 

[Leo R.]

Thanks Charanjeev for the quick help
I tried to do the update query that you suggested, but it kept saying that the "Replace" expression was undefined. I also tried using "Substitute" instead but that didn't work either. I really don't know anything about syntax or code so I didn't know what to do when the error came up. Can you help explain it to me a little better? I really appreciate it and hate to be a pain. Thanks a lot
Leo

 

[Brendan Reynolds]

Which version of Access?

--
Brendan Reynolds (MVP)

Thanks Charanjeev for the quick help,
I tried to do the update query that you suggested, but it kept saying

that the "Replace" expression was undefined. I also tried using "Substitute"
instead but that didn't work either. I really don't know anything about
syntax or code so I didn't know what to do when the error came up. Can you
help explain it to me a little better? I really appreciate it and hate to be
a pain. Thanks a lot.

 

[Leo R.]

sorry bout that. It's Access 2000

 

[Brendan Reynolds]

Some of the earlier posts in this thread are no longer visible to me, so I'm
not sure if you're using the Replace() function in code, or directly in a
query. In Access 2000, this function can't be used directly in a query, but
you can write a wrapper function in VBA that calls the Replace() function
and use your wrapper function in a query. In Access 2002 and Access 2003,
this is no longer necessary, and you can call the Replace() function
directly from the query.

Example of a wrapper function:

Public Function Rep(ByVal StringIn As String, ByVal FindString As String, _
ByVal RepString As String, Optional ByVal StartPos As Long = 1, _
Optional ByVal RepCount As Long = -1, _
Optional ByVal CompareMethod As VbCompareMethod = vbBinaryCompare) As
String

Rep = Replace(StringIn, FindString, RepString, StartPos, RepCount,
CompareMethod)

End Function

Example of a query that uses it:

SELECT Rep([SomeField],"e","*") AS Whatever
FROM SomeTable;