Dear All,
I have an application written by VB.Net with connect to a
Microsoft Access database.
One of the forms is to select data using oleAdapter and fill it
into a dataset. In the oleAdapter, i have two parameter : FromNation
and ToNation.
The select statement as follow :

SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
LocCode, DeptCode, Status, RepStatus, ReqRemarks
FROM PRCR_EmpyPerMas
WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
ORDER BY EmpyName, EmpyCode

Then before i fill data into dataset, i assign the value to
parameters, if user not enter any data then system will default
FromNation = ' ' and ToNation = 'ZZZZZZ'. This works fine, it returns
all records.

However, when i enter 'IND' for FromNation and 'IND' for ToNation,
it doesn't return any record. I know my table has record with 'IND'.

Any solution?

Thanks a lot.

Regards,

Lee Han Lim

* (E-Mail Removed) (Han Lim) scripsit:
> One of the forms is to select data using oleAdapter and fill it
> into a dataset. In the oleAdapter, i have two parameter : FromNation
> and ToNation.
> The select statement as follow :
>
> SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
> LocCode, DeptCode, Status, RepStatus, ReqRemarks
> FROM PRCR_EmpyPerMas
> WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
> ORDER BY EmpyName, EmpyCode

For data(base) related questions, give this group a try:

<URL:news://news.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

Han Lim,
> However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> it doesn't return any record. I know my table has record with 'IND'.
Does you table contain IND specifically, or does it contain "INDIA"?

As INDIA is not less or equal to IND. If it contains INDIA, you may want to
consider using Like instead of a range.

Something like:

Where NationCode Like ToNation

Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.

In your original case I would consider using Between, something like:

Where NationCode Between FromNation And ToNation

The reason you should use Like instead of appending "ZZZZ" to the end of IND
is it will handle international characters correctly.

As Herfried suggested, you may want to ask this question "down the hall" in
either the adonet newsgroup he gave or one of the access specific
newsgroups.

Hope this helps
Jay

"Han Lim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Dear All,
> I have an application written by VB.Net with connect to a
> Microsoft Access database.
> One of the forms is to select data using oleAdapter and fill it
> into a dataset. In the oleAdapter, i have two parameter : FromNation
> and ToNation.
> The select statement as follow :
>
> SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
> LocCode, DeptCode, Status, RepStatus, ReqRemarks
> FROM PRCR_EmpyPerMas
> WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
> ORDER BY EmpyName, EmpyCode
>
> Then before i fill data into dataset, i assign the value to
> parameters, if user not enter any data then system will default
> FromNation = ' ' and ToNation = 'ZZZZZZ'. This works fine, it returns
> all records.
>
> However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> it doesn't return any record. I know my table has record with 'IND'.
>
> Any solution?
>
> Thanks a lot.
>
> Regards,
>
> Lee Han Lim

"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message news:<#(E-Mail Removed)>...
> Han Lim,
> > However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> > it doesn't return any record. I know my table has record with 'IND'.
> Does you table contain IND specifically, or does it contain "INDIA"?
>
> As INDIA is not less or equal to IND. If it contains INDIA, you may want to
> consider using Like instead of a range.
>
> Something like:
>
> Where NationCode Like ToNation
>
> Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.
>
> In your original case I would consider using Between, something like:
>
> Where NationCode Between FromNation And ToNation
>
> The reason you should use Like instead of appending "ZZZZ" to the end of IND
> is it will handle international characters correctly.
>
> As Herfried suggested, you may want to ask this question "down the hall" in
> either the adonet newsgroup he gave or one of the access specific
> newsgroups.
>
> Hope this helps
> Jay
>
> "Han Lim" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Dear All,
> > I have an application written by VB.Net with connect to a
> > Microsoft Access database.
> > One of the forms is to select data using oleAdapter and fill it
> > into a dataset. In the oleAdapter, i have two parameter : FromNation
> > and ToNation.
> > The select statement as follow :
> >
> > SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
> > LocCode, DeptCode, Status, RepStatus, ReqRemarks
> > FROM PRCR_EmpyPerMas
> > WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
> > ORDER BY EmpyName, EmpyCode
> >
> > Then before i fill data into dataset, i assign the value to
> > parameters, if user not enter any data then system will default
> > FromNation = ' ' and ToNation = 'ZZZZZZ'. This works fine, it returns
> > all records.
> >
> > However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> > it doesn't return any record. I know my table has record with 'IND'.
> >
> > Any solution?
> >
> > Thanks a lot.
> >
> > Regards,
> >
> > Lee Han Lim

First of all, thanks for your reply. Sorry for posting the question at
the wrong group.

Yes my table contains IND specifically, i did try to use between
statement, it did work.

I found that, if i format the lsFromNation = ' IND' and to
lsToNation = ' IND' then everything works. My column
datatype is text, length = 15. Is it a must to format my parameter to
15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
format parameter to equel length, but i'm not sure about ACCESS.

Regards,

Lee Han Lim

Han,
> I found that, if i format the lsFromNation = ' IND' and to
> lsToNation = ' IND' then everything works. My column
> datatype is text, length = 15. Is it a must to format my parameter to
> 15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
> format parameter to equel length, but i'm not sure about ACCESS.

As you found, 'IND' is not ' IND' , if it works with the spaces,
then you need the spaces.

Personally I would trim the spaces from the values going into the Access
database, unless there is a specific need to have them there.

Hope this helps
Jay

"Han Lim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message
news:<#(E-Mail Removed)>...
> > Han Lim,
> > > However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> > > it doesn't return any record. I know my table has record with 'IND'.
> > Does you table contain IND specifically, or does it contain "INDIA"?
> >
> > As INDIA is not less or equal to IND. If it contains INDIA, you may want
to
> > consider using Like instead of a range.
> >
> > Something like:
> >
> > Where NationCode Like ToNation
> >
> > Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.
> >
> > In your original case I would consider using Between, something like:
> >
> > Where NationCode Between FromNation And ToNation
> >
> > The reason you should use Like instead of appending "ZZZZ" to the end of
IND
> > is it will handle international characters correctly.
> >
> > As Herfried suggested, you may want to ask this question "down the hall"
in
> > either the adonet newsgroup he gave or one of the access specific
> > newsgroups.
> >
> > Hope this helps
> > Jay
> >
> > "Han Lim" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Dear All,
> > > I have an application written by VB.Net with connect to a
> > > Microsoft Access database.
> > > One of the forms is to select data using oleAdapter and fill it
> > > into a dataset. In the oleAdapter, i have two parameter : FromNation
> > > and ToNation.
> > > The select statement as follow :
> > >
> > > SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
> > > LocCode, DeptCode, Status, RepStatus, ReqRemarks
> > > FROM PRCR_EmpyPerMas
> > > WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
> > > ORDER BY EmpyName, EmpyCode
> > >
> > > Then before i fill data into dataset, i assign the value to
> > > parameters, if user not enter any data then system will default
> > > FromNation = ' ' and ToNation = 'ZZZZZZ'. This works fine, it returns
> > > all records.
> > >
> > > However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> > > it doesn't return any record. I know my table has record with 'IND'.
> > >
> > > Any solution?
> > >
> > > Thanks a lot.
> > >
> > > Regards,
> > >
> > > Lee Han Lim
>
> First of all, thanks for your reply. Sorry for posting the question at
> the wrong group.
>
> Yes my table contains IND specifically, i did try to use between
> statement, it did work.
>
> I found that, if i format the lsFromNation = ' IND' and to
> lsToNation = ' IND' then everything works. My column
> datatype is text, length = 15. Is it a must to format my parameter to
> 15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
> format parameter to equel length, but i'm not sure about ACCESS.
>
> Regards,
>
> Lee Han Lim

Jay, thanks a lot for your suggestion. I use "Trim", and i works.

Regards,

Lee Han Lim.



"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message news:<#(E-Mail Removed)>...
> Han,
> > I found that, if i format the lsFromNation = ' IND' and to
> > lsToNation = ' IND' then everything works. My column
> > datatype is text, length = 15. Is it a must to format my parameter to
> > 15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
> > format parameter to equel length, but i'm not sure about ACCESS.
>
> As you found, 'IND' is not ' IND' , if it works with the spaces,
> then you need the spaces.
>
> Personally I would trim the spaces from the values going into the Access
> database, unless there is a specific need to have them there.
>
> Hope this helps
> Jay
>
> "Han Lim" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message
> news:<#(E-Mail Removed)>...
> > > Han Lim,
> > > > However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> > > > it doesn't return any record. I know my table has record with 'IND'.
> > > Does you table contain IND specifically, or does it contain "INDIA"?
> > >
> > > As INDIA is not less or equal to IND. If it contains INDIA, you may want
> to
> > > consider using Like instead of a range.
> > >
> > > Something like:
> > >
> > > Where NationCode Like ToNation
> > >
> > > Where ToNation contains "IND%" (verify the '%' on Access, it may be '*'.
> > >
> > > In your original case I would consider using Between, something like:
> > >
> > > Where NationCode Between FromNation And ToNation
> > >
> > > The reason you should use Like instead of appending "ZZZZ" to the end of
> IND
> > > is it will handle international characters correctly.
> > >
> > > As Herfried suggested, you may want to ask this question "down the hall"
> in
> > > either the adonet newsgroup he gave or one of the access specific
> > > newsgroups.
> > >
> > > Hope this helps
> > > Jay
> > >
> > > "Han Lim" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Dear All,
> > > > I have an application written by VB.Net with connect to a
> > > > Microsoft Access database.
> > > > One of the forms is to select data using oleAdapter and fill it
> > > > into a dataset. In the oleAdapter, i have two parameter : FromNation
> > > > and ToNation.
> > > > The select statement as follow :
> > > >
> > > > SELECT SID, EmpyCode, EmpyName, LastDayWorked, NationCode,
> > > > LocCode, DeptCode, Status, RepStatus, ReqRemarks
> > > > FROM PRCR_EmpyPerMas
> > > > WHERE (NationCode >= FromNation) AND (NationCode <= ToNation)
> > > > ORDER BY EmpyName, EmpyCode
> > > >
> > > > Then before i fill data into dataset, i assign the value to
> > > > parameters, if user not enter any data then system will default
> > > > FromNation = ' ' and ToNation = 'ZZZZZZ'. This works fine, it returns
> > > > all records.
> > > >
> > > > However, when i enter 'IND' for FromNation and 'IND' for ToNation,
> > > > it doesn't return any record. I know my table has record with 'IND'.
> > > >
> > > > Any solution?
> > > >
> > > > Thanks a lot.
> > > >
> > > > Regards,
> > > >
> > > > Lee Han Lim
> >
> > First of all, thanks for your reply. Sorry for posting the question at
> > the wrong group.
> >
> > Yes my table contains IND specifically, i did try to use between
> > statement, it did work.
> >
> > I found that, if i format the lsFromNation = ' IND' and to
> > lsToNation = ' IND' then everything works. My column
> > datatype is text, length = 15. Is it a must to format my parameter to
> > 15 chars? I know if I declare varchar(15) in MS SQL, i don't have to
> > format parameter to equel length, but i'm not sure about ACCESS.
> >
> > Regards,
> >
> > Lee Han Lim