Incremental search with numbers

  • Thread starter Thread starter Goldar
  • Start date Start date
G

Goldar

I have implemented an incremental search uses the LIKE operator in a query to
select aphabetic data from an alphabetic fields in a table based on
characters keyed in on my form. This works fine. The problem comes when I key
in numeric data on my screen and try to use the LIKE operator against a
number field in my table. I understand what's happening, but I don't know
what to do about it. It seems that I need to be able to compare the keyed
characters (left to right) against the same characters in my table, or
something like that. Does anyone have any ideas as to how to accomplish this?

Thanks...
 
Could you provide an example of what you are trying, and how it is (not)
working the way you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
For example, i use my incremental search and I key in a letter (say 'A') and
my program places it in a SQL query which uses a LIKE 'A*' to extract all
records whose key field starts with 'A'. If I then key in a 'B', my program
creates a query which uses a LIKE 'AB*' to extract all records whose key
starts with the characters 'AB'. And so on, until I find the record I want.
So far so good.

Now, if my table key field is a up to 6 digits (ex. '975321'), and I key in
the first character (ex '9'), my query will try to extract records using a
LIKE '9*' operator. Since the data in my table is numeric, it will never
match and no records will be selected. This is my problem. Do I need to start
matching from the right rather than the left? Or, is there some way I can get
the same matching effect as I do with alphabetic data?

Thanks...
 
Goldar

You've described what you're doing. Now, what's your data like?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
By the way, first are you certain that the "numeric" field is defined as a
numeric data type in the table?

Then, if you are trying to find the alphabetic character string starting
with the character "9" in a numeric field, you won't!

Take a look at wildcards (Access HELP) -- and consider something more like
(untested):

Like [Enter a digit] & "*"

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
My data is defined as "number", "single","0 decimal places". These are
account numbers (6 digits). It doesn't look like there's any way to do this,
unless I can have the SQL query convert the numeric in the table for
comparison the the LIKE clause. For example, is there any way to change the
WHERE clause to read:
WHERE Cstr([table key] LIKE "73*" to find numeric data like 734210

Otherwise, I'm left with having to restructure my table and all of the
forms, reports and queries that use this table.

Thanks for your help.

Jeff Boyce said:
By the way, first are you certain that the "numeric" field is defined as a
numeric data type in the table?

Then, if you are trying to find the alphabetic character string starting
with the character "9" in a numeric field, you won't!

Take a look at wildcards (Access HELP) -- and consider something more like
(untested):

Like [Enter a digit] & "*"

Regards

Jeff Boyce
Microsoft Office/Access MVP


Goldar said:
For example, i use my incremental search and I key in a letter (say 'A')
and
my program places it in a SQL query which uses a LIKE 'A*' to extract all
records whose key field starts with 'A'. If I then key in a 'B', my
program
creates a query which uses a LIKE 'AB*' to extract all records whose key
starts with the characters 'AB'. And so on, until I find the record I
want.
So far so good.

Now, if my table key field is a up to 6 digits (ex. '975321'), and I key
in
the first character (ex '9'), my query will try to extract records using
a
LIKE '9*' operator. Since the data in my table is numeric, it will never
match and no records will be selected. This is my problem. Do I need to
start
matching from the right rather than the left? Or, is there some way I can
get
the same matching effect as I do with alphabetic data?

Thanks...
Click to expand...
Click to expand...
 
Again, take a look in Access HELP for wildcards, their syntax and examples.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Goldar said:
My data is defined as "number", "single","0 decimal places". These are
account numbers (6 digits). It doesn't look like there's any way to do
this,
unless I can have the SQL query convert the numeric in the table for
comparison the the LIKE clause. For example, is there any way to change
the
WHERE clause to read:
WHERE Cstr([table key] LIKE "73*" to find numeric data like 734210

Otherwise, I'm left with having to restructure my table and all of the
forms, reports and queries that use this table.

Thanks for your help.

Jeff Boyce said:
By the way, first are you certain that the "numeric" field is defined as
a
numeric data type in the table?

Then, if you are trying to find the alphabetic character string starting
with the character "9" in a numeric field, you won't!

Take a look at wildcards (Access HELP) -- and consider something more
like
(untested):

Like [Enter a digit] & "*"

Regards

Jeff Boyce
Microsoft Office/Access MVP


Goldar said:
For example, i use my incremental search and I key in a letter (say
'A')
and
my program places it in a SQL query which uses a LIKE 'A*' to extract
all
records whose key field starts with 'A'. If I then key in a 'B', my
program
creates a query which uses a LIKE 'AB*' to extract all records whose
key
starts with the characters 'AB'. And so on, until I find the record I
want.
So far so good.

Now, if my table key field is a up to 6 digits (ex. '975321'), and I
key
in
the first character (ex '9'), my query will try to extract records
using
a
LIKE '9*' operator. Since the data in my table is numeric, it will
never
match and no records will be selected. This is my problem. Do I need to
start
matching from the right rather than the left? Or, is there some way I
can
get
the same matching effect as I do with alphabetic data?

Thanks...

:

Could you provide an example of what you are trying, and how it is
(not)
working the way you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have implemented an incremental search uses the LIKE operator in a
query
to
select aphabetic data from an alphabetic fields in a table based on
characters keyed in on my form. This works fine. The problem comes
when
I
key
in numeric data on my screen and try to use the LIKE operator
against a
number field in my table. I understand what's happening, but I don't
know
what to do about it. It seems that I need to be able to compare the
keyed
characters (left to right) against the same characters in my table,
or
something like that. Does anyone have any ideas as to how to
accomplish
this?

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Did you try your own suggestion: WHERE Cstr([table key] LIKE "73*"? That
works for me.

If you mean that you don't know how to enter that in the graphical query
designer, the easiest way is to use the View menu and change to SQL view,
where you can directly edit the sql statement. In graphical view, you would
add a new calculated column with the Field expression being Cstr([table
key], and then enter the Criterion Like "73*".

Goldar said:
My data is defined as "number", "single","0 decimal places". These are
account numbers (6 digits). It doesn't look like there's any way to do
this,
unless I can have the SQL query convert the numeric in the table for
comparison the the LIKE clause. For example, is there any way to change
the
WHERE clause to read:
WHERE Cstr([table key] LIKE "73*" to find numeric data like 734210

Otherwise, I'm left with having to restructure my table and all of the
forms, reports and queries that use this table.

Thanks for your help.

Jeff Boyce said:
By the way, first are you certain that the "numeric" field is defined as
a
numeric data type in the table?

Then, if you are trying to find the alphabetic character string starting
with the character "9" in a numeric field, you won't!

Take a look at wildcards (Access HELP) -- and consider something more
like
(untested):

Like [Enter a digit] & "*"

Regards

Jeff Boyce
Microsoft Office/Access MVP


Goldar said:
For example, i use my incremental search and I key in a letter (say
'A')
and
my program places it in a SQL query which uses a LIKE 'A*' to extract
all
records whose key field starts with 'A'. If I then key in a 'B', my
program
creates a query which uses a LIKE 'AB*' to extract all records whose
key
starts with the characters 'AB'. And so on, until I find the record I
want.
So far so good.

Now, if my table key field is a up to 6 digits (ex. '975321'), and I
key
in
the first character (ex '9'), my query will try to extract records
using
a
LIKE '9*' operator. Since the data in my table is numeric, it will
never
match and no records will be selected. This is my problem. Do I need to
start
matching from the right rather than the left? Or, is there some way I
can
get
the same matching effect as I do with alphabetic data?

Thanks...

:

Could you provide an example of what you are trying, and how it is
(not)
working the way you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have implemented an incremental search uses the LIKE operator in a
query
to
select aphabetic data from an alphabetic fields in a table based on
characters keyed in on my form. This works fine. The problem comes
when
I
key
in numeric data on my screen and try to use the LIKE operator
against a
number field in my table. I understand what's happening, but I don't
know
what to do about it. It seems that I need to be able to compare the
keyed
characters (left to right) against the same characters in my table,
or
something like that. Does anyone have any ideas as to how to
accomplish
this?

Thanks...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top