 |
|
|
|
Next: Declare 'End Type' with an early-bound data type
|
| Author |
Message |
External
Since: Nov 24, 2003
Posts: 9
|
(Msg. 1) Posted: Mon Nov 24, 2003 8:28 am
Post subject: Problems with hyphens
Archived from groups: microsoft>public>vb>database>dao (more info?)
|
|
|
Can you help?
I have run into two problems with accessing a simple
recordset from VB (DAO 3.6). Both problems involve hyphen
("-") chanracters and the FindFirst method applied to the
same recordset.
Problem #1 is best illustrated by the contents of the
immediate window included below. The record returned by
the FindFirst method does not meet the criteria specified
to the method. The StrComp results suggest that the
recordset is sorted according to vbBinaryCompare, but the
FindFirst method is selecting records according to
vbTextCompare. How can I resolve this inconsistency?
Problem #2 occurs when the hyphen character appears in the
FindFirst criteria string. In this case the hyphen
appears to be ignored. e.g.
mrstData.FindFirst "Word1 >= 'a-k'"
results in a record with Word1 = "Akashi" being selected,
even though there are earlier records that should match
the criterion. Is there something "magical" about the
hyphen character, and some way to force it to be
recognized and included in the criteria string?
Thanks for any help you can give.
Cheers,
Randy
==vvvvvv=== copied from immediate window ==vvvvvv===
? mrstdata.Name
SELECT * FROM tblWordList ORDER BY Word1, Word2;
? mrstdata.Type
2
mrstData.FindFirst "Word1 >= 'ab'"
? mrstdata.NoMatch, mrstdata("word1")
False A-Faza
? mrstdata("word1") >= "ab"
False
? StrComp(mrstData(strField), "ab", vbTextCompare)
1
? StrComp(mrstData(strField), "ab", vbBinaryCompare)
-1
==^^^^^^=== end of copy from immediate window ==^^^^^^===
mrstData("Word1") field values from next few records:
Ab-az
Ababovic
Abaz
Abaz
Abaz
Abaz
ABC"DEF"GHI |
|
| Back to top |
|
 | |
External
Since: Nov 30, 2003
Posts: 96
|
(Msg. 2) Posted: Mon Nov 24, 2003 12:39 pm
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Randy MacDonald" <Randy.TakeThisOut@denHaag.com> wrote in message <news:090501c3b28e$d3f058f0$a401280a@phx.gbl>...
> Problem #1 is best illustrated by the contents of the
> immediate window included below. The record returned by
> the FindFirst method does not meet the criteria specified
> to the method. The StrComp results suggest that the
> recordset is sorted according to vbBinaryCompare, but the
> FindFirst method is selecting records according to
> vbTextCompare. How can I resolve this inconsistency?
DAO/Jet is quite ignorant of the current module's Option Compare
setting. It uses the database or field's CollatingOrder instead.
--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!<!-- ~MESSAGE_AFTER~ --> |
|
| Back to top |
|
| |
External
Since: Nov 24, 2003
Posts: 9
|
(Msg. 3) Posted: Tue Nov 25, 2003 4:05 am
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks for your interest, Joe. I am aware of the lack of
connection between the option compare and the database
sort order.
The problem that I am having (with problem #1) is that DAO
itself seems to be inconsistent. That is, the DAO
FindFirst method does not seem to follow the same
collating order as the database.
Suggestions for solving this inconsistency would be
greatly appreciated, as would any suggestions for dealing
with the problem of the "invisible" hyphen in the criteria
expression.
Cheers,
Randy
>-----Original Message-----
>"Randy MacDonald" <Randy.RemoveThis@denHaag.com> wrote in message
<news:090501c3b28e$d3f058f0$a401280a@phx.gbl>...
>
>> Problem #1 is best illustrated by the contents of the
>> immediate window included below. The record returned by
>> the FindFirst method does not meet the criteria
specified
>> to the method. The StrComp results suggest that the
>> recordset is sorted according to vbBinaryCompare, but
the
>> FindFirst method is selecting records according to
>> vbTextCompare. How can I resolve this inconsistency?
>
>DAO/Jet is quite ignorant of the current module's Option
Compare
>setting. It uses the database or field's CollatingOrder
instead.
>
>--
>Joe Foster <mailto:jlfoster%40znet.com> "Regged" again?
<http://www.xenu.net/>
>WARNING: I cannot be held responsible for the
above They're coming to
>because my cats have apparently learned to
type. take me away, ha ha!
>
>
>.
><!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Problems with hyphens |
|
| Back to top |
|
| |
External
Since: Nov 30, 2003
Posts: 96
|
(Msg. 4) Posted: Tue Nov 25, 2003 12:49 pm
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Randy MacDonald" <Randy.TakeThisOut@denHaag.com> wrote in message <news:098201c3b333$351776a0$a501280a@phx.gbl>...
> Thanks for your interest, Joe. I am aware of the lack of
> connection between the option compare and the database
> sort order.
>
> The problem that I am having (with problem #1) is that DAO
> itself seems to be inconsistent. That is, the DAO
> FindFirst method does not seem to follow the same
> collating order as the database.
>
> Suggestions for solving this inconsistency would be
> greatly appreciated, as would any suggestions for dealing
> with the problem of the "invisible" hyphen in the criteria
> expression.
The field could have a different CollatingOrder than the
database as a whole. Sorting and searching could well
treat some characters, such as hyphen, in different ways.
Try doing the searching with the WHERE clause of a query.
--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!<!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Problems with hyphens |
|
| Back to top |
|
| |
External
Since: Nov 24, 2003
Posts: 9
|
(Msg. 5) Posted: Wed Nov 26, 2003 5:43 am
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks for the suggestion, Joe.
Unfortunately, the query itself (and the WHERE clause) is
determined by the recordset that is being presented to the
user. The user's objective is to navigate to a specific
record within that predefined recordset, so changing the
WHERE clause does not seem to be an option.
Cheers,
Randy
>-----Original Message-----
>"Randy MacDonald" <Randy.RemoveThis@denHaag.com> wrote in message
<news:098201c3b333$351776a0$a501280a@phx.gbl>...
>
>> Thanks for your interest, Joe. I am aware of the lack
of
>> connection between the option compare and the database
>> sort order.
>>
>> The problem that I am having (with problem #1) is that
DAO
>> itself seems to be inconsistent. That is, the DAO
>> FindFirst method does not seem to follow the same
>> collating order as the database.
>>
>> Suggestions for solving this inconsistency would be
>> greatly appreciated, as would any suggestions for
dealing
>> with the problem of the "invisible" hyphen in the
criteria
>> expression.
>
>The field could have a different CollatingOrder than the
>database as a whole. Sorting and searching could well
>treat some characters, such as hyphen, in different ways.
>Try doing the searching with the WHERE clause of a query.
>
>--
>Joe Foster <mailto:jlfoster%40znet.com> "Regged" again?
<http://www.xenu.net/>
>WARNING: I cannot be held responsible for the
above They're coming to
>because my cats have apparently learned to
type. take me away, ha ha!
>
>
>.
><!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Problems with hyphens |
|
| Back to top |
|
| |
External
Since: Nov 30, 2003
Posts: 96
|
(Msg. 6) Posted: Wed Nov 26, 2003 10:38 am
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Randy MacDonald" <Randy RemoveThis @denHaag.com> wrote in message <news:722c01c3b40a$177a27a0$a601280a@phx.gbl>...
> Thanks for the suggestion, Joe.
>
> Unfortunately, the query itself (and the WHERE clause) is
> determined by the recordset that is being presented to the
> user. The user's objective is to navigate to a specific
> record within that predefined recordset, so changing the
> WHERE clause does not seem to be an option.
Bleah. I wonder if mrstData.FindFirst "Word1 LIKE 'ab*'"
would behave differently...
--
Joe Foster <mailto:jlfoster%40znet.com> Got Thetans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!<!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Problems with hyphens |
|
| Back to top |
|
| |
External
Since: Nov 30, 2003
Posts: 96
|
(Msg. 7) Posted: Wed Nov 26, 2003 4:42 pm
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Randy MacDonald" <Randy.DeleteThis@denHaag.com> wrote in message <news:722c01c3b40a$177a27a0$a601280a@phx.gbl>...
> Unfortunately, the query itself (and the WHERE clause) is
> determined by the recordset that is being presented to the
> user. The user's objective is to navigate to a specific
> record within that predefined recordset, so changing the
> WHERE clause does not seem to be an option.
Additionally, you might try .Filter. You might be able to
use Bookmarks from the filtered Recordset with the original.
--
Joe Foster <mailto:jlfoster%40znet.com> Auditine Addict <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!<!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Problems with hyphens |
|
| Back to top |
|
| |
External
Since: Nov 24, 2003
Posts: 9
|
(Msg. 8) Posted: Thu Nov 27, 2003 3:50 am
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Hello, Joe,
Thanks for the suggestions. Using Like as the criterion
results in results that are more like those anticipated.
Unfortunately, it doesn't replace the functionality of
FindFirst. (E.g. "Button" satisfies the "Word1 >= 'ab'"
criterion, but not the "Word1 Like 'ab*'" criterion.)
I will look into your ".Filter" suggestion some more,
though. Perhaps there is an opportunity for a work around
along the lines that you suggest.
Cheers,
Randy
>-----Original Message-----
>"Randy MacDonald" <Randy.DeleteThis@denHaag.com> wrote in message
<news:722c01c3b40a$177a27a0$a601280a@phx.gbl>...
>
>> Unfortunately, the query itself (and the WHERE clause)
is
>> determined by the recordset that is being presented to
the
>> user. The user's objective is to navigate to a specific
>> record within that predefined recordset, so changing the
>> WHERE clause does not seem to be an option.
>
>Additionally, you might try .Filter. You might be able to
>use Bookmarks from the filtered Recordset with the
original.
>
>--
>Joe Foster <mailto:jlfoster%40znet.com> Auditine Addict
<http://www.xenu.net/>
>WARNING: I cannot be held responsible for the
above They're coming to
>because my cats have apparently learned to
type. take me away, ha ha!
>
>
>.
><!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Problems with hyphens |
|
| Back to top |
|
| |
|
Joe "Nuke Me Xemu" Foster
|
External
Since: Oct 02, 2003
Posts: 3
|
(Msg. 9) Posted: Thu Nov 27, 2003 11:06 pm
Post subject: Re: Problems with hyphens [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Randy MacDonald" <Randy.TakeThisOut@denHaag.com> wrote in message <news:022101c3b4c3$9208f9b0$a001280a@phx.gbl>...
> Thanks for the suggestions. Using Like as the criterion
> results in results that are more like those anticipated.
> Unfortunately, it doesn't replace the functionality of
> FindFirst. (E.g. "Button" satisfies the "Word1 >= 'ab'"
> criterion, but not the "Word1 Like 'ab*'" criterion.)
>
> I will look into your ".Filter" suggestion some more,
> though. Perhaps there is an opportunity for a work around
> along the lines that you suggest.
Don't feel too badly, though -- I've seen similar oddities
with hyphens in ordinary ListBoxes when .Sorted is set.
--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha! >> Stay informed about: Problems with hyphens |
|
| Back to top |
|
| |
| Related Topics: | DAO 3.6 record locking - Hi, Is it possible to programmatically lock a single record in a VB application using DAO 3.6? I would like to lock users' records when they log on to the app. Later I can check if a user's record is locked to see if they are logged in or not. (I....
Fixes for VB4 or DAO 3.0 - I've got VB4 (32-bit) and it seems to use DAO 3.0. Is there a service pack for either of these that I can use? I'm having issues with DAO and > 2GB of RAM on the machine, and don't want to have to move to a newer version of VB if I can help it, just t...
Jet error - I've just started getting this error message on a seemingly random basis. 'The Microsoft Jet database engine does not recognize 'CID' as a valid field name or expression.' CID is a text field, and it is a valid column name. The line of code that..
DAO reference / Access 2007 - while using an Access 2007 .accdb file I can access the dao. object very easily. the following code in VBA works well : Function getrecordcount(strTableName As String) As Long Dim dbCurrent As DAO.Database Set db = CurrentDb Dim rstRecords A...
Data Source - Data Report Designer - Is there a way to change the Database Source at run time in the Microsoft Data Report Designer? If so. How? |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Profile
Private Messages
Log in
