Cannot select data by range.

[Han Lim]

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

[Herfried K. Wagner [MVP]]

* hl***@lintramax.com (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/>

[Jay B. Harlow [MVP - Outlook]]

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" <hl***@lintramax.com> wrote in message
news:73*************************@posting.google.co m... 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

[Han Lim]

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<#5**************@TK2MSFTNGP12.phx.gbl>...

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" <hl***@lintramax.com> wrote in message
news:73*************************@posting.google.co m...

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 B. Harlow [MVP - Outlook]]

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" <hl***@lintramax.com> wrote in message
news:73**************************@posting.google.c om... "Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<#5**************@TK2MSFTNGP12.phx.gbl>...

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" <hl***@lintramax.com> wrote in message
news:73*************************@posting.google.co m...

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 Lim]

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

Regards,

Lee Han Lim.

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<#N**************@tk2msftngp13.phx.gbl>...

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" <hl***@lintramax.com> wrote in message
news:73**************************@posting.google.c om...

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<#5**************@TK2MSFTNGP12.phx.gbl>...

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" <hl***@lintramax.com> wrote in message
news:73*************************@posting.google.co m...
> 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