Conditional Query

[Ken Snell [MVP]]

Add a calculated field to your query:

DoDontUse: [YN]=[Y] And (x2/x1)<=0.85

Set the Criteria: cell for this calculated field to True.

 

[Steve Freeman, SuperRealtor]

I know, aren't they all conditional...
I want to pull records from a table, and contained in the table are two
numeric value fields; x1 and x2, and a Yes/No field. YN.
What I want to do is when YN=yes (or = to a specific state I specify)
when YN=yes, I want the query to perform the calculation where
if x2/x1<=0.85, then retrieve the records in the query
if x2/x1>0.85, then don't retrieve the records for the query.
I am only interested in those records I've marked with the YN and where
x2/x1<=0.85

Here is about the best way to describe what I need.

if (YN=Y,((x2/x1)<=0.85, use record, don't use record),nothing)
I want the query to determine the YN first, then compare the values of x.

Insights always appreciated more than you know!
-Steve

 

[John Vinson]

Ok, I opened my query in design view and added a calculated field. (First
time I've added a calculated field to a query btw) I suppose I named the
field "oops" which stands for Originally Overpriced PropertieS. What I am
trying to do is generate a report of available homes that were either way
overpriced from the beginning (Orig List) and/or are selling at a discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]

 

[Steve Freeman, SuperRealtor]

Ok, I opened my query in design view and added a calculated field. (First
time I've added a calculated field to a query btw) I suppose I named the
field "oops" which stands for Originally Overpriced PropertieS. What I am
trying to do is generate a report of available homes that were either way
overpriced from the beginning (Orig List) and/or are selling at a discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Elsewhere in the query I set the criteria to pull the selected group of
records (status is Available; (or status = A) all the Y records so to speak)
When I run the query, I get all the Y records, and for each record I get the
result of the [Now List]/[Orig List] calculation.
What I can't seem to do is get the query to only return those records where
([Now List]/[Orig List])<=0.85; where the property is now at 85% or less of
original list price. This gets me half way to where I want to go.

I've done all of my query building using the Design View and have almost
zero experience with SQL.

I don't understand the "DoDontUse" in your answer, but I just tried this in
design view: (I took out my oops field creation described above)

Field: DoDontUse: ([Status]=[a]) And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Group By
Sort: blank
Criteria: True

and when I run the query, I get prompted for a value for "a". When I insert
"a" in the enter parameter value prompt, the query returns the error that
begins "The expression is typed incorrectly..." When I put a number in
instead of "a" and run it again, using 4444, it returns the grid with no
records shown.
I use "a" as a marker for available properties, "e" for expired, "s" for
sold; etc. I only want to retrieve the records that have their status field
occupied by an "a".
In the first column of my query I've got the "status" field set with
Criteria of "a" Second column is a property type identifier, third is Orig
List, fourth is Now List, and fifth is the new DoDontUse which I can't seem
to get a grip on.

I think I may be missing something fundamental. Any clues?
Sincerely,
Steve

Add a calculated field to your query:

DoDontUse: [YN]=[Y] And (x2/x1)<=0.85

Set the Criteria: cell for this calculated field to True.

--

Ken Snell
<MS ACCESS MVP>

I know, aren't they all conditional...
I want to pull records from a table, and contained in the table are two
numeric value fields; x1 and x2, and a Yes/No field. YN.
What I want to do is when YN=yes (or = to a specific state I specify)
when YN=yes, I want the query to perform the calculation where
if x2/x1<=0.85, then retrieve the records in the query
if x2/x1>0.85, then don't retrieve the records for the query.
I am only interested in those records I've marked with the YN and where
x2/x1<=0.85

Here is about the best way to describe what I need.

if (YN=Y,((x2/x1)<=0.85, use record, don't use record),nothing)
I want the query to determine the YN first, then compare the values of x.

Insights always appreciated more than you know!
-Steve

 

[Ken Snell [MVP]]

And change the [a] in the calculated field to "a":

Field: DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Where
Sort: blank
Criteria: True

--

Ken Snell
<MS ACCESS MVP>

Ok, I opened my query in design view and added a calculated field. (First
time I've added a calculated field to a query btw) I suppose I named the
field "oops" which stands for Originally Overpriced PropertieS. What I am
trying to do is generate a report of available homes that were either way
overpriced from the beginning (Orig List) and/or are selling at a discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]

 

[Ken Snell [MVP]]

Any chance that a record can have a Null value for Orig List? That will
cause an error.

--

Ken Snell
<MS ACCESS MVP>

Good Morning Ken and John,
Thanks for your suggestions, but neither seem to be working. Both of these
suggestions generated Overflow errors. (I've since removed the other
fields
from my query and now I only have "status" "Ptype = res" "now list" "orig
list" and the calculated field. I did this in case something else in the
query might have bolluxed this up.
For all queries, the Ptype criteria: res. (pulls residential property
only, not lots/land or mobile homes, or commercial etc.)

Status is a separate field in the query, which I can easily set=a to pull
only available properties. Status is the first criteria field in the
query
(far left in design view). I've placed my calculated field "last" in the
far right column of the design view grid, if that matters.

Here is what I've experimented with, and the results
#1
field: oops: (([Now List]/[Orig List])<=0.85)
total: where
criteria: true
and set the "status field" criteria to "a"
which yeilds an Overflow error

#2 Next I tried this:
Field: oops: ([Now List]/[Orig List])
total: where
criteria: <.84999
and set the "status field" criteria to "a"
this generated an overflow error

#3 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to "a"
this generated an overflow error.

#4 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to ""
this generated an overflow error.

I am very confused now. I've checked the table containing both Orig and
Now and both fields are set up as NUMBER data types, field size "double"
type "currency" and for some reason Orig is set to 2 decimal places and
Now
is set to 0 decimal places. ..if this means anything.

I know this is asking a lot, but would you consider this? I can create an
excel file containing these five fields and X records (10-20 should be
plenty) and attach/send it to you? I have a feeling you guys could import
this info into an access table and write a query that would achieve what I
need.
I've accessed Access help and tried to find what an Overflow is/does and
it
doesn't seem to make sense to me when used in a query context.

Any clues or suggestions would be appreciated! Thanks for you attention
and
advice so far, too!
-Steve

And change the [a] in the calculated field to "a":

Field: DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Where
Sort: blank
Criteria: True

--

Ken Snell
<MS ACCESS MVP>

On Fri, 28 Jan 2005 23:29:25 -0600, "Steve Freeman, SuperRealtor"

Ok, I opened my query in design view and added a calculated field. (First
time I've added a calculated field to a query btw) I suppose I named the
field "oops" which stands for Originally Overpriced PropertieS. What I am
trying to do is generate a report of available homes that were either way
overpriced from the beginning (Orig List) and/or are selling at a discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]

 

[Steve Freeman, SuperRealtor]

Good Morning Ken and John,
Thanks for your suggestions, but neither seem to be working. Both of these
suggestions generated Overflow errors. (I've since removed the other fields
from my query and now I only have "status" "Ptype = res" "now list" "orig
list" and the calculated field. I did this in case something else in the
query might have bolluxed this up.
For all queries, the Ptype criteria: res. (pulls residential property
only, not lots/land or mobile homes, or commercial etc.)

Status is a separate field in the query, which I can easily set=a to pull
only available properties. Status is the first criteria field in the query
(far left in design view). I've placed my calculated field "last" in the
far right column of the design view grid, if that matters.

Here is what I've experimented with, and the results
#1
field: oops: (([Now List]/[Orig List])<=0.85)
total: where
criteria: true
and set the "status field" criteria to "a"
which yeilds an Overflow error

#2 Next I tried this:
Field: oops: ([Now List]/[Orig List])
total: where
criteria: <.84999
and set the "status field" criteria to "a"
this generated an overflow error

#3 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to "a"
this generated an overflow error.

#4 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to ""
this generated an overflow error.

I am very confused now. I've checked the table containing both Orig and
Now and both fields are set up as NUMBER data types, field size "double"
type "currency" and for some reason Orig is set to 2 decimal places and Now
is set to 0 decimal places. ..if this means anything.

I know this is asking a lot, but would you consider this? I can create an
excel file containing these five fields and X records (10-20 should be
plenty) and attach/send it to you? I have a feeling you guys could import
this info into an access table and write a query that would achieve what I
need.
I've accessed Access help and tried to find what an Overflow is/does and it
doesn't seem to make sense to me when used in a query context.

Any clues or suggestions would be appreciated! Thanks for you attention and
advice so far, too!
-Steve

And change the [a] in the calculated field to "a":

Field: DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Where
Sort: blank
Criteria: True

--

Ken Snell
<MS ACCESS MVP>

Ok, I opened my query in design view and added a calculated field. (First
time I've added a calculated field to a query btw) I suppose I named the
field "oops" which stands for Originally Overpriced PropertieS. What I am
trying to do is generate a report of available homes that were either way
overpriced from the beginning (Orig List) and/or are selling at a discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]

 

[Steve Freeman, SuperRealtor]

When I run the query w/o the calc field, none of the values for either Orig
or Now were empty.
I looked into the table containing the all the data (9300 records) and did
an a-z sort on both Orig and Now and found a few empty fields, which I
placed a value of zero into.
I re-ran options 1-4 below and got overflow errors with each instance.

I could go back and delete the zero values in these records and replace with
something like 0.0001 which will not affect anything really, and have
something in Now and Orig >0; but I don't think this is relevant and I don't
really want to do that unless absolutely necessary.
I am going to "compact/repair database" just in case something is amiss;
heck it can't hurt.
Any other ideas would be welcomed, and I do appreciate your help so far.
-Steve

Any chance that a record can have a Null value for Orig List? That will
cause an error.

--

Ken Snell
<MS ACCESS MVP>

Good Morning Ken and John,
Thanks for your suggestions, but neither seem to be working. Both of these
suggestions generated Overflow errors. (I've since removed the other
fields
from my query and now I only have "status" "Ptype = res" "now list" "orig
list" and the calculated field. I did this in case something else in the
query might have bolluxed this up.
For all queries, the Ptype criteria: res. (pulls residential property
only, not lots/land or mobile homes, or commercial etc.)

Status is a separate field in the query, which I can easily set=a to pull
only available properties. Status is the first criteria field in the
query
(far left in design view). I've placed my calculated field "last" in the
far right column of the design view grid, if that matters.

Here is what I've experimented with, and the results
#1
field: oops: (([Now List]/[Orig List])<=0.85)
total: where
criteria: true
and set the "status field" criteria to "a"
which yeilds an Overflow error

#2 Next I tried this:
Field: oops: ([Now List]/[Orig List])
total: where
criteria: <.84999
and set the "status field" criteria to "a"
this generated an overflow error

#3 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to "a"
this generated an overflow error.

#4 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to ""
this generated an overflow error.

I am very confused now. I've checked the table containing both Orig and
Now and both fields are set up as NUMBER data types, field size "double"
type "currency" and for some reason Orig is set to 2 decimal places and
Now
is set to 0 decimal places. ..if this means anything.

I know this is asking a lot, but would you consider this? I can create an
excel file containing these five fields and X records (10-20 should be
plenty) and attach/send it to you? I have a feeling you guys could import
this info into an access table and write a query that would achieve what I
need.
I've accessed Access help and tried to find what an Overflow is/does and
it
doesn't seem to make sense to me when used in a query context.

Any clues or suggestions would be appreciated! Thanks for you attention
and
advice so far, too!
-Steve

And change the [a] in the calculated field to "a":

Field: DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Where
Sort: blank
Criteria: True

--

Ken Snell
<MS ACCESS MVP>

On Fri, 28 Jan 2005 23:29:25 -0600, "Steve Freeman, SuperRealtor"

Ok, I opened my query in design view and added a calculated field. (First
time I've added a calculated field to a query btw) I suppose I named the
field "oops" which stands for Originally Overpriced PropertieS. What

I
am

trying to do is generate a report of available homes that were either way
overpriced from the beginning (Orig List) and/or are selling at a discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]

 

[Ken Snell [MVP]]

Putting a zero in for an empty or Null Orig List field isn't good, as you
then are trying to divide by zero in your test.

Try this for a calculated field:

field: oops: IIf(Len([Orig List] & "")=0 Or [Orig List]=0, False,([Now
List]/[Orig List])<=0.85)
total: where
criteria: true

This will set the result to False for this test if Orig List is empty or
zero, otherwise, the division will be done and the result will be tested
against 0.85.
--

Ken Snell
<MS ACCESS MVP>

When I run the query w/o the calc field, none of the values for either
Orig
or Now were empty.
I looked into the table containing the all the data (9300 records) and did
an a-z sort on both Orig and Now and found a few empty fields, which I
placed a value of zero into.
I re-ran options 1-4 below and got overflow errors with each instance.

I could go back and delete the zero values in these records and replace
with
something like 0.0001 which will not affect anything really, and have
something in Now and Orig >0; but I don't think this is relevant and I
don't
really want to do that unless absolutely necessary.
I am going to "compact/repair database" just in case something is amiss;
heck it can't hurt.
Any other ideas would be welcomed, and I do appreciate your help so far.
-Steve

Any chance that a record can have a Null value for Orig List? That will
cause an error.

--

Ken Snell
<MS ACCESS MVP>

Good Morning Ken and John,
Thanks for your suggestions, but neither seem to be working. Both of these
suggestions generated Overflow errors. (I've since removed the other
fields
from my query and now I only have "status" "Ptype = res" "now list" "orig
list" and the calculated field. I did this in case something else in the
query might have bolluxed this up.
For all queries, the Ptype criteria: res. (pulls residential
property
only, not lots/land or mobile homes, or commercial etc.)

Status is a separate field in the query, which I can easily set=a to pull
only available properties. Status is the first criteria field in the
query
(far left in design view). I've placed my calculated field "last" in the
far right column of the design view grid, if that matters.

Here is what I've experimented with, and the results
#1
field: oops: (([Now List]/[Orig List])<=0.85)
total: where
criteria: true
and set the "status field" criteria to "a"
which yeilds an Overflow error

#2 Next I tried this:
Field: oops: ([Now List]/[Orig List])
total: where
criteria: <.84999
and set the "status field" criteria to "a"
this generated an overflow error

#3 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to "a"
this generated an overflow error.

#4 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to ""
this generated an overflow error.

I am very confused now. I've checked the table containing both Orig and
Now and both fields are set up as NUMBER data types, field size
"double"
type "currency" and for some reason Orig is set to 2 decimal places and
Now
is set to 0 decimal places. ..if this means anything.

I know this is asking a lot, but would you consider this? I can create an
excel file containing these five fields and X records (10-20 should be
plenty) and attach/send it to you? I have a feeling you guys could import
this info into an access table and write a query that would achieve
what I
need.
I've accessed Access help and tried to find what an Overflow is/does
and
it
doesn't seem to make sense to me when used in a query context.

Any clues or suggestions would be appreciated! Thanks for you
attention
and
advice so far, too!
-Steve

And change the [a] in the calculated field to "a":

Field: DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Where
Sort: blank
Criteria: True

--

Ken Snell
<MS ACCESS MVP>

On Fri, 28 Jan 2005 23:29:25 -0600, "Steve Freeman, SuperRealtor"

Ok, I opened my query in design view and added a calculated field.
(First
time I've added a calculated field to a query btw) I suppose I
named
the
field "oops" which stands for Originally Overpriced PropertieS.
What I
am
trying to do is generate a report of available homes that were
either
way
overpriced from the beginning (Orig List) and/or are selling at a
discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Yee haww!! That one did it! Thanks so much for your help, I truly
appreciate it.
Sincerely,
-Steve

Putting a zero in for an empty or Null Orig List field isn't good, as you
then are trying to divide by zero in your test.

Try this for a calculated field:

field: oops: IIf(Len([Orig List] & "")=0 Or [Orig List]=0, False,([Now
List]/[Orig List])<=0.85)
total: where
criteria: true

This will set the result to False for this test if Orig List is empty or
zero, otherwise, the division will be done and the result will be tested
against 0.85.
--

Ken Snell
<MS ACCESS MVP>

When I run the query w/o the calc field, none of the values for either
Orig
or Now were empty.
I looked into the table containing the all the data (9300 records) and did
an a-z sort on both Orig and Now and found a few empty fields, which I
placed a value of zero into.
I re-ran options 1-4 below and got overflow errors with each instance.

I could go back and delete the zero values in these records and replace
with
something like 0.0001 which will not affect anything really, and have
something in Now and Orig >0; but I don't think this is relevant and I
don't
really want to do that unless absolutely necessary.
I am going to "compact/repair database" just in case something is
amiss;
heck it can't hurt.
Any other ideas would be welcomed, and I do appreciate your help so
far.
-Steve

Any chance that a record can have a Null value for Orig List? That
will
cause an error.

--

Ken Snell
<MS ACCESS MVP>

message
Good Morning Ken and John,
Thanks for your suggestions, but neither seem to be working. Both of
these
suggestions generated Overflow errors. (I've since removed the
other
fields
from my query and now I only have "status" "Ptype = res" "now list"
"orig
list" and the calculated field. I did this in case something else
in
the
query might have bolluxed this up.
For all queries, the Ptype criteria: res. (pulls residential
property
only, not lots/land or mobile homes, or commercial etc.)

Status is a separate field in the query, which I can easily set=a to
pull
only available properties. Status is the first criteria field in
the
query
(far left in design view). I've placed my calculated field "last"
in
the
far right column of the design view grid, if that matters.

Here is what I've experimented with, and the results
#1
field: oops: (([Now List]/[Orig List])<=0.85)
total: where
criteria: true
and set the "status field" criteria to "a"
which yeilds an Overflow error

#2 Next I tried this:
Field: oops: ([Now List]/[Orig List])
total: where
criteria: <.84999
and set the "status field" criteria to "a"
this generated an overflow error

#3 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to "a"
this generated an overflow error.

#4 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to ""
this generated an overflow error.

I am very confused now. I've checked the table containing both
Orig
and
Now and both fields are set up as NUMBER data types, field size
"double"
type "currency" and for some reason Orig is set to 2 decimal places and
Now
is set to 0 decimal places. ..if this means anything.

I know this is asking a lot, but would you consider this? I can create
an
excel file containing these five fields and X records (10-20 should be
plenty) and attach/send it to you? I have a feeling you guys could
import
this info into an access table and write a query that would achieve
what
I
need.
I've accessed Access help and tried to find what an Overflow is/does
and
it
doesn't seem to make sense to me when used in a query context.

Any clues or suggestions would be appreciated! Thanks for you
attention
and
advice so far, too!
-Steve

message
And change the [a] in the calculated field to "a":

Field: DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Where
Sort: blank
Criteria: True

--

Ken Snell
<MS ACCESS MVP>

On Fri, 28 Jan 2005 23:29:25 -0600, "Steve Freeman, SuperRealtor"

Ok, I opened my query in design view and added a calculated
field.
(First
time I've added a calculated field to a query btw) I suppose I
named
the
field "oops" which stands for Originally Overpriced PropertieS.
What
I
am
trying to do is generate a report of available homes that were
either
way
overpriced from the beginning (Orig List) and/or are selling at a
discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]

 

[Steve Freeman, SuperRealtor]

Yee haww!! That one did it! Thanks so much for your help, I truly
appreciate it.
Sincerely,
-Steve

Putting a zero in for an empty or Null Orig List field isn't good, as you
then are trying to divide by zero in your test.

Try this for a calculated field:

field: oops: IIf(Len([Orig List] & "")=0 Or [Orig List]=0, False,([Now
List]/[Orig List])<=0.85)
total: where
criteria: true

This will set the result to False for this test if Orig List is empty or
zero, otherwise, the division will be done and the result will be tested
against 0.85.
--

Ken Snell
<MS ACCESS MVP>

When I run the query w/o the calc field, none of the values for either
Orig
or Now were empty.
I looked into the table containing the all the data (9300 records) and did
an a-z sort on both Orig and Now and found a few empty fields, which I
placed a value of zero into.
I re-ran options 1-4 below and got overflow errors with each instance.

I could go back and delete the zero values in these records and replace
with
something like 0.0001 which will not affect anything really, and have
something in Now and Orig >0; but I don't think this is relevant and I
don't
really want to do that unless absolutely necessary.
I am going to "compact/repair database" just in case something is amiss;
heck it can't hurt.
Any other ideas would be welcomed, and I do appreciate your help so far.
-Steve

Any chance that a record can have a Null value for Orig List? That will
cause an error.

--

Ken Snell
<MS ACCESS MVP>

Good Morning Ken and John,
Thanks for your suggestions, but neither seem to be working. Both of these
suggestions generated Overflow errors. (I've since removed the other
fields
from my query and now I only have "status" "Ptype = res" "now list" "orig
list" and the calculated field. I did this in case something else in the
query might have bolluxed this up.
For all queries, the Ptype criteria: res. (pulls residential
property
only, not lots/land or mobile homes, or commercial etc.)

Status is a separate field in the query, which I can easily set=a to pull
only available properties. Status is the first criteria field in the
query
(far left in design view). I've placed my calculated field "last" in the
far right column of the design view grid, if that matters.

Here is what I've experimented with, and the results
#1
field: oops: (([Now List]/[Orig List])<=0.85)
total: where
criteria: true
and set the "status field" criteria to "a"
which yeilds an Overflow error

#2 Next I tried this:
Field: oops: ([Now List]/[Orig List])
total: where
criteria: <.84999
and set the "status field" criteria to "a"
this generated an overflow error

#3 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to "a"
this generated an overflow error.

#4 Next I tried this:
field DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
total: where
criteria: True
and set the "status field" criteria to ""
this generated an overflow error.

I am very confused now. I've checked the table containing both Orig and
Now and both fields are set up as NUMBER data types, field size
"double"
type "currency" and for some reason Orig is set to 2 decimal places and
Now
is set to 0 decimal places. ..if this means anything.

I know this is asking a lot, but would you consider this? I can

create
an

excel file containing these five fields and X records (10-20 should be
plenty) and attach/send it to you? I have a feeling you guys could import
this info into an access table and write a query that would achieve
what I
need.
I've accessed Access help and tried to find what an Overflow is/does
and
it
doesn't seem to make sense to me when used in a query context.

Any clues or suggestions would be appreciated! Thanks for you
attention
and
advice so far, too!
-Steve

And change the [a] in the calculated field to "a":

Field: DoDontUse: ([Status]="a") And (([Now List]/[Orig List])<=0.85)
Table: blank
Total: Where
Sort: blank
Criteria: True

--

Ken Snell
<MS ACCESS MVP>

On Fri, 28 Jan 2005 23:29:25 -0600, "Steve Freeman, SuperRealtor"

Ok, I opened my query in design view and added a calculated field.
(First
time I've added a calculated field to a query btw) I suppose I
named
the
field "oops" which stands for Originally Overpriced PropertieS.
What I
am
trying to do is generate a report of available homes that were
either
way
overpriced from the beginning (Orig List) and/or are selling at a
discount
price today (Now List).

Here is my new calculated field:

Field: oops: [Now List]/[Orig List]
Table: blank
Total: Group By
Sort: blank
Criteria: True

Try changing the Total operator to Where, and the Criteria to

< 0.85


John W. Vinson[MVP]