Re: Advanced query on numbers stored in a text field.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dub333 (Dub333_at_discussions.microsoft.com)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 14:15:04 -0700

I may have missed part of what he meant, but it still doesn't help in the
long run.

The database I am interacting with is a protected db. The only way we can
query items in it is to use the form we, as users, are presented with.

We can either fill in the given blanks and click the query button - this
does not support the entry of ranges.
     OR
We can click the "Advanced query" button (on the form) and type a query
string.

We do not have blank fields, or access to the design view - only interface
is the form.

I don't know if that makes things clearer or not. We get close enough with
the query results. And if I need to do cut and paste, it's much quicker to
paste it into a spread*** - reformat - and then refilter.

Thanks for all of your input - it's been educational at least.

"PC Data***" wrote:

> I think you might have missed the point in Van's last response. Enter the
> following expression in a blank field in your query:
> MyNumbers:Val([Field 1])
> Now in the criteria of that field enter:
> BETWEEN 170 AND 390
>
> If you have any other expressions and criteria for Field 1 in your query,
> remove them.
>
> Run your query.
>
> --
> PC Data***
> Your Resource For Help With Access, Excel And Word Applications
> resource@pcdata***.com
> www.pcdata***.com
>
>
> "Dub333" <Dub333@discussions.microsoft.com> wrote in message
> news:5F8B66F5-F43C-44E5-B6CE-DDF1B7B2594E@microsoft.com...
> > I think my problem is with the data (some have even entered leading zeros)
> > that I'm trying to query. As I said, I have no permissions to change the
> > data (protected fields), so all I can do is write an advanced querey in a
> > text box in the form. What happens when I put in:
> >
> > Val([Field 1]) BETWEEN 170 AND 390
> >
> > I get "Error in query statement" when I try to run the query.
> >
> > I think there maybe some limits to what kind of queries we can do.
> > Or does it matter if we are using Access 2002??
> >
> > I may just be relegated to copy the data to Excel and changing the data
> type
> > and filter again.
> >
> > "John Vinson" wrote:
> >
> > > On Mon, 25 Oct 2004 15:37:03 -0700, Dub333
> > > <Dub333@discussions.microsoft.com> wrote:
> > >
> > > >Van -
> > > >This didn't work, I got an error stating that 'WHERE' is not an
> undefined
> > > >function.
> > > >
> > > >I did use the Val([Field 1]) Between "170" And "390" part of it and I
> still
> > > >get values I don't think I should get. For example: 1700, 1730, 1810,
> etc...
> > > >
> > > >Any ideas?
> > >
> > > Get rid of the quotemarks. Since you're comparing the field to two
> > > text string criteria, it's converting the Val() numeric result back to
> > > text - and just as "ay" is between "a" and "b", so also "1700000" is
> > > between "170" and "180".
> > >
> > > A criterion of
> > >
> > > Val([Field 1]) BETWEEN 170 AND 390
> > >
> > > will compare an integer value to two other integer values and should
> > > work correctly.
> > >
> > > John W. Vinson[MVP]
> > > Join the online Access Chats
> > > Tuesday 11am EDT - Thursday 3:30pm EDT
> > > http://community.compuserve.com/msdevapps
> > >
> > >
>
>
>

Quantcast