Re: Incremental search with numbers


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" <Goldar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:94B526AD-F227-4E03-9287-71FD142FAC31@xxxxxxxxxxxxxxxx
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" wrote:

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" <Goldar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AF50B7C9-3D85-44AE-AF59-0B4AC8EE60F9@xxxxxxxxxxxxxxxx
> 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...
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>> "Goldar" <Goldar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:E6D694D0-B07E-484C-8A79-1192F0496D21@xxxxxxxxxxxxxxxx
>> >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...
>>
>>
>>




.

Relevant Pages

  • Re: Truncated Memo field displayed on form
    ... 255 characters in any column that is in the combo box's RowSource query. ... My fields down to "Knowledge and Understandings" are fine. ... occurs with the "descriptor" field being truncated on my form. ...
    (microsoft.public.access.forms)
  • Re: Incremental search with numbers
    ... "Jeff Boyce" wrote: ... my program places it in a SQL query which uses a LIKE 'A*' to extract all ... starts with the characters 'AB'. ...
    (microsoft.public.access.formscoding)
  • Re: Using part of a field
    ... See the article "Finding and replacing characters using wildcards" at: ... AS400 SQL may have a different way of doing ... called i.dsn, containing the followiing text: ... sort of thing in a query. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Export to Ascii
    ... characters in a calculated field when you export a query. ... Modify your query to append its output to the temporary table; ... "John Nurick" wrote: ... that instead of concatenating with a delimiter it concatenates a fixed ...
    (microsoft.public.access.externaldata)
  • Re: SQL too long?
    ... I haven't done a thorough analysis here, but I suspect that the number of characters needed to express the SQL may not be as important as the number of aggregate functions like Sumand Countthat you invoke along the way. ... Although what you now have may be correct, assuming no simian behavior on the part of Access, splitting up the Query may also make it easier to debug if you later encounter any questions concerning its correctness, or if you need to revise it in some way. ... For example, in my copy of Access 2000, it says that the "Number of characters in an SQL statement" has a maximum value of "approximately 64,000", so you probably have some wiggle room there, even without using abbreviated aliases for some of the names. ... tblARTrn01.invno) INNER JOIN tblARMst01 ON tblDUPSFreight.invno = tblARMst01. ...
    (microsoft.public.access.queries)