and and or usage

[Frank]

If you use multiple criteria inan Access 97 DB do you need to add the or to
each cell or is it a given after the first one?
ex.
criteria <800
or between 4000 and 4999
or between 900 and 999
Would this be correct? No quotes and the word "or" added each time?
Thanks
Frank

 

[Ken Snell]

Your snippet looks correct...you must use a logical operator between each
criterion.

 

[Van T. Dinh]

If you meant the Query Grid (officially Query-By-Example) and you enter

< 800 in the first criteria row

then don't use "OR" in subsequent row as Access automatically interpret each
separate criteria as OR to the previous criteria row. For example, you only
enter

Between 4000 And 4999 in the second criteria row and

Between 900 And 999 in the 3rd criteria row.

 

[GRCC]

Why does access 97 insist on putting quotes around the numbers. I have been
told that you do not use quotes with numbers? And even just using <800 (with
the quotes put in by access) still pulls all of the records (addresses).
Frank

 

[Van T. Dinh]

It sound to me that they are digit characters in Text Field rather than
Numeric Field. In this case, and since Text Field values can only be
compared with Text, Access interprets your criteria value as Text and put
the quotes around the value to indicate Text / String.

What type of Field is it?

 

[Frank]

I am at home now. I go in to work later today. I will check. This is a
poorly designed accounting program it seem to me. If the field with the
members certificate number is general or something, I hesitate to change the
format , worried that it will affect the program itself? This field is only
used for the members number as far as I know. So how can i select a range(s)
of numbers for mailings , etc? Is the only way to change the field to
numeric?
Frank

 

[Van T. Dinh]

If it is Numeric, you don't have any problems.

If it is Text, post a small set of sample data (as varied as possible) and
what you want to select.

 

[Frank]

I really appreciate the help, but if it were numeric, why am I having all
these problems and why does it put the Quotes in? Please explain? I get no
error messages. And if I add , say 4 ranges, one in each cell going down in
the number field starting at criteria cell , when I click save all the
ranges are in one cell(criteria cell?As I stated even a simple<800 does not
do anything. The field I put this in is number (name of field) it is in the
same table as the rest of the fields. It contains numbers only from 4 to
10,000. all I want to select adresses in the range of numbers I put in. ?
Thanks
Frank L

 

[Frank]

SELECT Member.Number, Member.FormlName,
Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member
]![Address2]) AS Expr1,
Switch([Member]![Billing]=1,[Member]![City1],[Member]![Billing]=2,[Member]![
City2]) AS Expr2,
Switch([Member]![Billing]=1,[Member]![State1],[Member]![Billing]=2,[Member]!
[State2]) AS Expr3,
Switch([Member]![Billing]=1,[Member]![Zip1],[Member]![Billing]=2,[Member]![Z
ip2]) AS Expr4FROM Member
WHERE (((Member.Number) Between "700" And "800"))ORDER BY Member.Number;
*Everything works above,
but the criteria does not, pulls all records no matter what I do.


The query is from one big table: I do not know how to post a few examples of
the table. I do not want to mess with the accounting program. The table has
45 fields in it, the "number" field is the member certificate number (not
ID) Feilds have address 1, city 1,state 1, etc. 6 addresses in all phone
numbers, etc IAs I stated the query works great , pulling the proper address
(1 or 2) (florida or up north). I just cannot get the criteria to do
anything at all.
Thanks
Frank

 

[Van T. Dinh]

I can't see YOUR Table but the symptoms you described indicates it is a Text
Field.

If [Number] is a Text Field, are you aware that

Between "700" and "800"

will select the following Members, for example:

"7010"
"7999"
etc ...?

Remember comparing TEXT is different from comparing numbers.

Perhaps, you should try:

.... WHERE CLng(Members.Number) Between 700 And 800

But the above will give error if the Text Field value cannot be converted to
Long Integer.

 

[Frank]

HI,
You appear upset, I am sorry I fI said something to offend you. That was no
my intent. No, I did not know Between "700" and "800" will select the
following Members, for example:"7010" "7999" etc ...? I also have no idea
what you mean. I really do appreciate and need your help. I would love to
post a part of the table, but as I stated before it has appx 45 feilds in
this one table. How could I possibly fit that in an email on this NG?
Thanks again for all your responses and help,
Sincerely
Frank

I can't see YOUR Table but the symptoms you described indicates it is a Text
Field.

If [Number] is a Text Field, are you aware that
Remember comparing TEXT is different from comparing numbers.

Perhaps, you should try:

... WHERE CLng(Members.Number) Between 700 And 800

But the above will give error if the Text Field value cannot be converted to
Long Integer.

--
HTH
Van T. Dinh
MVP (Access)

I really appreciate the help, but if it were numeric, why am I having all
these problems and why does it put the Quotes in? Please explain? I get no
error messages. And if I add , say 4 ranges, one in each cell going down in
the number field starting at criteria cell , when I click save all the
ranges are in one cell(criteria cell?As I stated even a simple<800 does not
do anything. The field I put this in is number (name of field) it is in the
same table as the rest of the fields. It contains numbers only from 4 to
10,000. all I want to select adresses in the range of numbers I put in. ?
Thanks
Frank L

 

[Evi]

To check if the field is a text one, open the table in Design view and look
next to the field name. Does it say Text or Number?
Evi

HI,
You appear upset, I am sorry I fI said something to offend you. That was no
my intent. No, I did not know Between "700" and "800" will select the
following Members, for example:"7010" "7999" etc ...? I also have no idea
what you mean. I really do appreciate and need your help. I would love to
post a part of the table, but as I stated before it has appx 45 feilds in
this one table. How could I possibly fit that in an email on this NG?
Thanks again for all your responses and help,
Sincerely
Frank

I can't see YOUR Table but the symptoms you described indicates it is a Text
Field.

If [Number] is a Text Field, are you aware that
Remember comparing TEXT is different from comparing numbers.

Perhaps, you should try:

... WHERE CLng(Members.Number) Between 700 And 800

But the above will give error if the Text Field value cannot be

converted

to
Long Integer.

get

no down
in does
not in
the

in.

?

 

[Frank]

It is a text field. I truly believe the only purpose of this field is the
member certificate #, which is the # they use to sign for dinner, pay dues
etc. I know how to convert it to a number field, but I think I should try
and find out from the developers if this will cause any problems. The whole
program is actually a problem. Poor design, very poor!
Thanks
Frank

To check if the field is a text one, open the table in Design view and look
next to the field name. Does it say Text or Number?
Evi

HI,
You appear upset, I am sorry I fI said something to offend you. That was no
my intent. No, I did not know Between "700" and "800" will select the
following Members, for example:"7010" "7999" etc ...? I also have no idea
what you mean. I really do appreciate and need your help. I would love to
post a part of the table, but as I stated before it has appx 45 feilds in
this one table. How could I possibly fit that in an email on this NG?
Thanks again for all your responses and help,
Sincerely
Frank

I can't see YOUR Table but the symptoms you described indicates it is

a
Text

Field.

If [Number] is a Text Field, are you aware that


Remember comparing TEXT is different from comparing numbers.

Perhaps, you should try:

... WHERE CLng(Members.Number) Between 700 And 800

But the above will give error if the Text Field value cannot be

converted

to
Long Integer.

--
HTH
Van T. Dinh
MVP (Access)



I really appreciate the help, but if it were numeric, why am I

having
all

these problems and why does it put the Quotes in? Please explain? I

get

no
error messages. And if I add , say 4 ranges, one in each cell going down
in
the number field starting at criteria cell , when I click save all the
ranges are in one cell(criteria cell?As I stated even a simple<800 does
not
do anything. The field I put this in is number (name of field) it is in
the
same table as the rest of the fields. It contains numbers only from

4

to in.

 

[John Vinson]

It is a text field. I truly believe the only purpose of this field is the
member certificate #, which is the # they use to sign for dinner, pay dues
etc. I know how to convert it to a number field, but I think I should try
and find out from the developers if this will cause any problems. The whole
program is actually a problem. Poor design, very poor!
Thanks
...

It will because Text values are handled differently than numbers.
Considered AS A TEXT STRING, the string "7010" is in fact larger than
the string "700" and less than the string "800", in precisely the same
way that the string "HABA" is alphabetically greater than the string
"HAA" and less than the string "IAA".

If you want the member number to be considered numerically, create a
Query based on your table and put a calculated field by typing

SearchCertNo: Val([Certificate #])

(or whatever the field name is). This will be a Number field, on which
you can use a criterion

BETWEEN 700 AND 800

Note - *no* quotemarks; numbers use no delimiter, Text values need
quotes.

 

[Frank]

John,
Sorry, I am confused. So my field name is Number (text field which contains
all the numbers), Where do I put this calculated field? Is the number field
in my query? would this be my calculated field - SearchMember: Val([Member
#])
I sure sound pretty dumb, but believe it or not, I am an MCP, w2000&w2000
server. I know windows very well, this stuff I do not.I am obviously lacking
the language structure. I guess it is hopeless. Thanks for trying, I guess I
will continue to research as best I can.
Sincerely confused,
Frank

It is a text field. I truly believe the only purpose of this field is the
member certificate #, which is the # they use to sign for dinner, pay dues
etc. I know how to convert it to a number field, but I think I should try
and find out from the developers if this will cause any problems. The whole
program is actually a problem. Poor design, very poor!
Thanks
...

It will because Text values are handled differently than numbers.
Considered AS A TEXT STRING, the string "7010" is in fact larger than
the string "700" and less than the string "800", in precisely the same
way that the string "HABA" is alphabetically greater than the string
"HAA" and less than the string "IAA".

If you want the member number to be considered numerically, create a
Query based on your table and put a calculated field by typing

SearchMember: Val([Member #])

(or whatever the field name is). This will be a Number field, on which
you can use a criterion

BETWEEN 700 AND 800

Note - *no* quotemarks; numbers use no delimiter, Text values need
quotes.

 

[Tom Ellison]

Dear Frank:

May I butt in?

Consider arranging these alphabetically:

AA
AAA
BB

These are already alphabetical. Well, "numbers" that are stored as
text use exactly the same rule:

11
111
22

Now you and I know 111 does NOT fall between 11 and 22 in "numerical"
sequence. But in alphabetical sequence it certainly does! Because
alphabetical sequence uses a completely different rule, comparing
character by character from left to right.

Numerical sequence actually works as though it were comparing
character by character, but it assumes sufficient leading zeros to
make all the strings the same length! In fact, text values containing
only digits can be made to sort in numeric order by changing them this
way. Thus, the above example changes to:

011
111
022

Now this is obviously NOT in order any way you look at it. But, the
first step is to add leading zeros so all the strings are the same
length. Now, this would sort alphabetically:

011
022
111

See, it works when all the number strings are the same length. But
our minds tend to automatically "add" the leading zeros, or spaces, or
whatever, before making the comparison. Just think how, when an
accountant uses his preprinted sheets, he must write the numbers in
right to left so as to leave leading spaces, so the columns line up.
Your mind is trained to do things like that automatically. But a
computer doesn't make such leaps of intellect, even though humans
pretty much presume them. Our presumptions cause us to misunderstand
the computer!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

John,
Sorry, I am confused. So my field name is Number (text field which contains
all the numbers), Where do I put this calculated field? Is the number field
in my query? would this be my calculated field - SearchMember: Val([Member
#])
I sure sound pretty dumb, but believe it or not, I am an MCP, w2000&w2000
server. I know windows very well, this stuff I do not.I am obviously lacking
the language structure. I guess it is hopeless. Thanks for trying, I guess I
will continue to research as best I can.
Sincerely confused,
Frank

It is a text field. I truly believe the only purpose of this field is the
member certificate #, which is the # they use to sign for dinner, pay dues
etc. I know how to convert it to a number field, but I think I should try
and find out from the developers if this will cause any problems. The whole
program is actually a problem. Poor design, very poor!
Thanks
...
my intent. No, I did not know Between "700" and "800" will select the
following Members, for example:"7010" "7999" etc ...?

It will because Text values are handled differently than numbers.
Considered AS A TEXT STRING, the string "7010" is in fact larger than
the string "700" and less than the string "800", in precisely the same
way that the string "HABA" is alphabetically greater than the string
"HAA" and less than the string "IAA".

If you want the member number to be considered numerically, create a
Query based on your table and put a calculated field by typing

SearchMember: Val([Member #])

(or whatever the field name is). This will be a Number field, on which
you can use a criterion

BETWEEN 700 AND 800

Note - *no* quotemarks; numbers use no delimiter, Text values need
quotes.

 

[John Vinson]

John,
Sorry, I am confused. So my field name is Number (text field which contains
all the numbers), Where do I put this calculated field? Is the number field
in my query? would this be my calculated field - SearchMember: Val([Member
#])

In a vacant Field cell in the query grid, over at the right past the
last filled field, simply type

SearchNumber: Val([Number])

Now open the query in datasheet view. You'll see the two columns,
labeled Number and SearchNumber, with what look like identical numeric
contents. They're not identical though - the column [Number] contains
text strings (which happen to consist of the characters 0 through 9),
while the column [SearchNumber] will contain Integer numeric values.

If you now put a criterion

BETWEEN 700 AND 800

on the Criteria line under [SearchNumber] I think you'll get the
result you want!

I sure sound pretty dumb, but believe it or not, I am an MCP, w2000&w2000
server. I know windows very well, this stuff I do not.I am obviously lacking
the language structure. I guess it is hopeless. Thanks for trying, I guess I
will continue to research as best I can.
Sincerely confused,
Frank

I hope that someday soon I'll be able to ask for your help on how to
set up Windows security and networking on my local library's
computers... an area that I find myself feeling very dumb indeed, but
which I rashly promised to do for them.

Lack of knowledge/experience is NOT EQUAL to "dumb" of course, and I
fully agree that Access is a very confusing environment! I'll be more
than happy to trade our areas of experience!

 

[Frank]

Hello John,
Thanks for that last bit of info, explained in terms even I can understand.
I am still working on my MCSA, 2 tests to go, If I can ever help you out I
most certainly will. If I do not know the answer, I am pretty sure I can
find it.
Thanks again, You have my email address.
Frank

John,
Sorry, I am confused. So my field name is Number (text field which contains
all the numbers), Where do I put this calculated field? Is the number field
in my query? would this be my calculated field - SearchMember: Val([Member
#])

In a vacant Field cell in the query grid, over at the right past the
last filled field, simply type

SearchNumber: Val([Number])

Now open the query in datasheet view. You'll see the two columns,
labeled Number and SearchNumber, with what look like identical numeric
contents. They're not identical though - the column [Number] contains
text strings (which happen to consist of the characters 0 through 9),
while the column [SearchNumber] will contain Integer numeric values.

If you now put a criterion

BETWEEN 700 AND 800

on the Criteria line under [SearchNumber] I think you'll get the
result you want!

I sure sound pretty dumb, but believe it or not, I am an MCP, w2000&w2000
server. I know windows very well, this stuff I do not.I am obviously lacking
the language structure. I guess it is hopeless. Thanks for trying, I guess I
will continue to research as best I can.
Sincerely confused,
Frank

I hope that someday soon I'll be able to ask for your help on how to
set up Windows security and networking on my local library's
computers... an area that I find myself feeling very dumb indeed, but
which I rashly promised to do for them.

Lack of knowledge/experience is NOT EQUAL to "dumb" of course, and I
fully agree that Access is a very confusing environment! I'll be more
than happy to trade our areas of experience!