DBRW Issue - Syntax Error

[Randy Van Sickle]

Using FP 2002, Embedded Access Database

I am trying to get results from a table in the Access DB, and I am getting
the following error on the ASP:

Database Results Error
Description: Syntax error (missing operator) in query expression 'RentalFee
<= AND MaxOccupancy >= AND Active = Yes'.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

Here is the Sequel Statement I used in the Custom Query of the DBRW window:

SELECT * FROM Properties WHERE RentalFee <= ::RentalFee:: and MaxOccupancy

= ::MaxOccupancy:: and Active = Yes ORDER BY RentalFee DESC

Can anyone tell me what my syntax error is here?

Thanks in advance for any replies or direction to the proper forum to pose
this question.

 

[Jon Spivey]

Hi,
It looks the dbrw isn't picking up the values from your form to search
against. As a first step try hard coding some values in the custom query I'd
also use Active <>0 rather than active = yes. for example
RentalFee <= 100000 and MaxOccupancy >= 0 and Active <> 0

This will probably pull up every record in your db. Does it? if so we know
the problem is with the form fields not passing to the sql statement and at
least know which direction to go.

Jon

 

[Randy Van Sickle]

Thanks, Jon -

Here is what I changed it to:

SELECT * FROM Properties WHERE RentalFee <= 100000 and MaxOccupancy >= 0 and
Active <> 0 ORDER BY RentalFee DESC

And these are the results I am getting from the asp when I open the page on
the web:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine

I'm not sure what that means now.

 

[Jon Spivey]

Are the 2 fields (RentalFee and MaxOccupancy) numeric in your database?
Usually this error is caused by trying to search for a numeric value against
a text field.

Jon

 

[Randy Van Sickle]

The RentalFee is a Currency field (which I believe is numeric) and the
MaxOccupancy is Numeric. The Active is a text field.

 

[Jon Spivey]

OK, I see what's happened. Assuming the Active field contains either yes or
no you'd need
SELECT * FROM Properties
WHERE RentalFee <= ::RentalFee:: and MaxOccupancy >= ::MaxOccupancy::
and Active = 'Yes'
ORDER BY RentalFee DESC

I've put quotes around Yes. Try that, it should work - if it fails try hard
coding the RentalFee and MaxOccupancy fields as you did before.

Jon

 

[Randy Van Sickle]

I tried it with the Active as you suggested, which gave the same results. I
then removed the active altogether, and was left with this:

SELECT * FROM Properties WHERE RentalFee <= 100000 and MaxOccupancy >= 0
ORDER BY RentalFee DESC

and I still got this error mesage in both cases:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine

Thanks.

 

[Kathleen Anderson [MVP - FrontPage]]

Try putting parentheses around the compares:

SELECT * FROM Properties WHERE (RentalFee <= 100000) and (MaxOccupancy >= 0)
ORDER BY RentalFee DESC


--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

 

[David Berry]

In your database your fields are called RentalFee, MaxOccupancy and Active
correct? What happens if you open up access and create an Access Query,
switch to SQL view and paste in your SQL Statement? Does it return results?

 

[Randy Van Sickle]

That was a good suggestion. It is now a rather large query, but it worked.
Thanks a bunch for the workaround.

 

[Randy Van Sickle]

Thanks Kathleen, I tried that but still received a syntax error message.
See below for the fix I used.

 

[David Berry]

Glad to help. I use Access a lot to test out queries, especially if they're
complicated ones, like joining two tables. Once I get it working in Access
then I can copy and paste it into my code.