DBRW - SQL syntax

[MaureenKLM]

2nd post here - 1st post's subject is DBR ERROR ON EMPTY. Let me try to
better phrase my situation

FP 2002 web w/Access 2002 Db

I have queries in the Access DB which all work as intended when viewed in
Access. One of the criteria in the queries is to show only those records
that match the criteria UNLESS there are no records to show in which case
show ALL records ,

I used these queries as the source for displaying records in the DBR - the
results display when there are records that match the specified criteria -
yet I get an error when the results should display ALL records.

I tried to recreate the query in the DBRW using the SQL from the Access DB
Query but I'm getting syntax errors as follows:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression
'(((tblProperties.County) Like
(IIf((DCount([[County]],[tblProperties],[[County]='Ocean'])>0),[Ocean],[*])))
AND ((tblProperties.State)=[NJ]) AND ((tblProperties.Display)=Yes))'.


HELP PLEASE! The query works in Access yet not in the DBR -
I think I need to create the SQL in the DBR but I'm running into trouble
with the SQL syntax -

 

[Stefan B Rusynko]

ASP and DB access does not support all native Access commands
- so you can't just create queries in Access and expect them to work in ASP
You can't use Dcount
And IIF is a Boolean test that you need to use w/ care and understand
By the time the test evaluates your DB recordsets have been processed

Run a std query (w/o your IIF) for the recordsets
then after the query check if you have any record
<% IF objRS.EOF Then
'...No records found - close the connection and run a new query for all
objRS.Close
Set objRS = Nothing
.... run a new qry for all records
%>
<% Else
.... display your recordsets found
%>
<% End IF %>




| 2nd post here - 1st post's subject is DBR ERROR ON EMPTY. Let me try to
| better phrase my situation
|
| FP 2002 web w/Access 2002 Db
|
| I have queries in the Access DB which all work as intended when viewed in
| Access. One of the criteria in the queries is to show only those records
| that match the criteria UNLESS there are no records to show in which case
| show ALL records ,
|
| I used these queries as the source for displaying records in the DBR - the
| results display when there are records that match the specified criteria -
| yet I get an error when the results should display ALL records.
|
| I tried to recreate the query in the DBRW using the SQL from the Access DB
| Query but I'm getting syntax errors as follows:
|
| [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression
| '(((tblProperties.County) Like
| (IIf((DCount([[County]],[tblProperties],[[County]='Ocean'])>0),[Ocean],[*])))
| AND ((tblProperties.State)=[NJ]) AND ((tblProperties.Display)=Yes))'.
|
|
| HELP PLEASE! The query works in Access yet not in the DBR -
| I think I need to create the SQL in the DBR but I'm running into trouble
| with the SQL syntax -
|