Advanced query on numbers stored in a text field.

G

Guest

I am working with a data base that I have no rights to change the data type.
For a specific field I need to find, via an advanced query window supplied in
the form, all the entries that are between the specified numbers, but the
field is formated as text.

For example, I need to find all entries for "Field 1" in "Table 1" that are
greater than 170, but less then 390. For this we've tried:
Field 1 > "170" And Field 1 <"390"

The result gives things like: 1770, 2202, and 2383 along with other entries
that are correct. How do I do a query on this field and only get the entries
that are truly between the "numbers" 170" and "390"?
 
V

Van T. Dinh

Use the VBA conversion function Val() like:

.... WHERE (Val([Field1]) > 170) AND (Val([Field1]) < 390)

If you want to include both 170 and 390, you can use:

.... WHERE Val([Field1]) BETWEEN 170 AND 390
 
G

Guest

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?


Van T. Dinh said:
Use the VBA conversion function Val() like:

.... WHERE (Val([Field1]) > 170) AND (Val([Field1]) < 390)

If you want to include both 170 and 390, you can use:

.... WHERE Val([Field1]) BETWEEN 170 AND 390

--
HTH
Van T. Dinh
MVP (Access)




Dub333 said:
I am working with a data base that I have no rights to change the data type.
For a specific field I need to find, via an advanced query window supplied in
the form, all the entries that are between the specified numbers, but the
field is formated as text.

For example, I need to find all entries for "Field 1" in "Table 1" that are
greater than 170, but less then 390. For this we've tried:
Field 1 > "170" And Field 1 <"390"

The result gives things like: 1770, 2202, and 2383 along with other entries
that are correct. How do I do a query on this field and only get the entries
that are truly between the "numbers" 170" and "390"?
Click to expand...
Click to expand...
 
G

Guest

It sounds to me that you didn't have a SPACE between WHERE and the opening
parenthesis.

BTW, what I posted is *for the SQL View* where "WHERE" is an SQL keyword.
If you are using the Query DesignView, you create a Calculated Column / Field:

NumField1: Val([Field1])

and in the criteria row of this Field, use:

BETWEEN 170 AND 390

(no quotations for the 2 numeric values)

HTH
Van T. Dinh
MVP (Access)
 
J

John Vinson

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?
Click to expand...

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
 
G

Guest

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 said:
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?
Click to expand...

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
Click to expand...
 
P

PC Datasheet

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 Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Dub333 said:
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 said:
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?
Click to expand...

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
Click to expand...
Click to expand...
 
G

Guest

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 spreadsheet - reformat - and then refilter.

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

PC Datasheet said:
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 Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Dub333 said:
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 said:
On Mon, 25 Oct 2004 15:37:03 -0700, Dub333

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
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

This you will have to ask the developer or whoever supports the application
as your database is restricted and when you wrote "Advanced Query", it could
mean anything the original developer allowed the user to query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Summarize Answers via a Query 3
AND on 2 fields along with other criteria in query 2
Seriously, I need ideas. Fields not transferring from table to table 3
Show form record number in query results 3
Convert Text to number 8
Linking a Number and Text field 2
number in criteria changing to text 4
Moving the last numbers of a field 3

Top