sql dilemma

[Guest]

I have the following SELECT statement that is not giving any results. I need
to show the Weekly Rate as a Discount Rate for those whose owner is BR18. The
discount rate is weekly rate minus 15%. I know there are at least 3 units
owned by her.

SELECT [c_Unit Number], c_Bedrooms, c_Bathrooms, c_Sleeps,[c_Weekly Rate]
FROM Condo
WHERE (((o_OwnerID)="BR18")
AND (([c_Weekly Rate])=([c_Weekly Rate])-([c_Weekly Rate]*0.15)));

Can anyone lend asistance? Thanks to those who do.
*** JohnE

 

[Dirk Goldgar]

I have the following SELECT statement that is not giving any results.
I need to show the Weekly Rate as a Discount Rate for those whose
owner is BR18. The discount rate is weekly rate minus 15%. I know
there are at least 3 units owned by her.

SELECT [c_Unit Number], c_Bedrooms, c_Bathrooms, c_Sleeps,[c_Weekly
Rate] FROM Condo
WHERE (((o_OwnerID)="BR18")
AND (([c_Weekly Rate])=([c_Weekly Rate])-([c_Weekly Rate]*0.15)));

Can anyone lend asistance? Thanks to those who do.
*** JohnE

I'd say your question belongs in the Queries newsgroup, not in
Formscoding, but here's the story: your query doesn't return any
records because your WHERE clause states that the only records returned
will be those for which

[c_Weekly Rate]= [c_Weekly Rate] - ([c_Weekly Rate]*0.15)

.... and the only way that could be true is if [c_Weekly Rate] is zero.
I think you have mistakenly put what should be a calculation into the
WHERE clause as a criterion. I think you probably meant something like
this:

SELECT
[c_Unit Number],
c_Bedrooms,
c_Bathrooms,
c_Sleeps,
IIf(o_OwnerID="BR18",
[c_Weekly Rate]-([c_Weekly Rate]*0.15),
[c_Weekly Rate])
As WeeklyRate
FROM Condo;

You could probably simplify your discount calculation by substituting

[c_Weekly Rate]*0.85

for

[c_Weekly Rate]-([c_Weekly Rate]*0.15)

but it's possible that would result in a slightly different rounding, so
I left the expression alone.