Help with access 97 query please

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I received help here Access 97 query for address 1 or address 2 in a member
DB. I asked about setting criteria to choose certain ranges of numbers. The
feild name for the column with numbers is number (it is the members
membership number-private country club) I was told that number should not be
used to name a fiels, butr that is the way this accounting program is built
(club Systems group) Expressions are added in the field name cell for the
choosing of the 1 or 2 address. This part works great. But even a simple
criteria in the neumber feild does nothing at all, all numbers are selected
(the address is correct) i tried <"800" for 1-799 and between "4000" and
"5999", etc. each range in its own cell in the number field starting at
criteria. Why doesn't this work? a simple <"800" should at least pull 1-799?
Am I missing something in the syntax? I am using quotes around all numbers
such as above.
Thanks,
Frank L
 
I received help here Access 97 query for address 1 or address 2 in a member
DB. I asked about setting criteria to choose certain ranges of numbers. The
feild name for the column with numbers is number (it is the members
membership number-private country club) I was told that number should not be
used to name a fiels, butr that is the way this accounting program is built
(club Systems group)
Click to expand...

The fact that the fieldname is 13 in the club's accounting program
does NOT require that the fieldname be 13 in your Access table! You
can import and export data using any alias for the fieldname that you
like.
Expressions are added in the field name cell for the
choosing of the 1 or 2 address. This part works great. But even a simple
criteria in the neumber feild does nothing at all, all numbers are selected
(the address is correct) i tried <"800" for 1-799 and between "4000" and
"5999", etc. each range in its own cell in the number field starting at
criteria. Why doesn't this work? a simple <"800" should at least pull 1-799?
Am I missing something in the syntax? I am using quotes around all numbers
such as above.
Click to expand...

If the field is of Number datatype, then you SHOULD NOT use quotes.
Quotes are used only for Text datatype fields. Could you post the
actual SQL of the query that you're running, with perhaps an example
of the type of data in the fields?
 
Hello John,
I am well versed in windows and networks, but access and programming are new
to me. The accounting program is the access DB. You are saying that the word
number used as a field name does not matter, OK, I was told it had a special
meaning in access. Thank you . I would love to post whatever is necessary to
get help. I am sorry, but I am unclear as to what you want me to post. do
you mean post the query itself and a small example from the table I am
using? If so, I can do that. John, in this program their is a place for 6
addresses. we only use 2. They are all in the same table in the DB. 1 is
florida address and 2 is northern address. Which changes of course whenever
someone goes away or comes back to fla. The original query was built with
help from this NG. I query the DB and it selects the proper address for the
mailing we are doing that day.Just wanted to give you some background.
Thanks so much.
Frank L {MCP}
 
I would love to post whatever is necessary to
get help. I am sorry, but I am unclear as to what you want me to post. do
you mean post the query itself and a small example from the table I am
using? If so, I can do that.
Click to expand...

Yes... please do. Open the Query in design view; using the tool at the
leftmost end of the toolbar as a dropdown (or using the View menu
option), get into SQL view of the query. You should see a mass of text
on the screen; at this point it may look cryptic but it contains all
the information needed to analyze your query. Post that text to a
message here, together with three or four records of typical data.
 
Thank you John, I will do so from work tomorrow morning ( wednesday) The
email address will be different (work email)I will identify my post.
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 Expr4
FROM Member
WHERE (((Member.Number) Between "700" And "800")) *Everything works above,
but the criteria does not, pulls all records no matter what I do.
ORDER BY Member.Number;

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,stse 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
 
WHERE (((Member.Number) Between "700" And "800")) *Everything works above,
but the criteria does not, pulls all records no matter what I do.
ORDER BY Member.Number;
Click to expand...

What's the data type of Member.Number? Text, or number? Could you at
least post some examples of member numbers which are being
inappropriately retrieved?
 
John,
It is a text field. with numbers ranging from 4 to 10,999.But 4-800,
4000-4999 and 5000-5999 are what we are concerned with as far as mailings.
Not all ranges all the time. But, mailings depend on the classes (limited
resident membership, gold, green sports, about 12 in all. I suppose I could
use the class field (text, I am assuming) to query to do the mailings. Just
a lot of extra work, when the numbers are (should be) so easy. And , of
course, I have no way of knowing (at the moment) if that field is designed
correctly and the info input correctly.I will investigate.
Thanks,
Frank
 
John,
It is a text field. with numbers ranging from 4 to 10,999.But 4-800,
4000-4999 and 5000-5999 are what we are concerned with as far as mailings.
Not all ranges all the time. But, mailings depend on the classes (limited
resident membership, gold, green sports, about 12 in all. I suppose I could
use the class field (text, I am assuming) to query to do the mailings. Just
a lot of extra work, when the numbers are (should be) so easy. And , of
course, I have no way of knowing (at the moment) if that field is designed
correctly and the info input correctly.I will investigate.
Thanks,
Click to expand...


As noted elsethread, you can use a calculated field Val([member#]) to
convert the text string to a number, which WILL obey ranges such as

BETWEEN 700 AND 800

If you have a Class field in the table (which I don't recall you
having mentioned anywhere in this thread, though I may have missed
it), why would it be difficult to use the class??? You could just use
a parameter query on the Class field with a criterion

[Enter class:]

Typing Gold in response to the prompt would give you all members with
Gold in their class field.
 
Back
Top