If statements in .adp files

[JRH]

I am using Access to front-end an SQL database. I am
currently trying to create a query in a .adp file. I can
get the query to run in SQL Query Builder but if I try to
cut and paste the following code into my query in Access,
I get the following error:

"This designer does not graphically support the COMPOUND
STATEMENT SQL construct."

IF (SELECT current_year
FROM current_year) = 2000 BEGIN
(SELECT tax_type
FROM dbo.tbl_tax_bill) END
SELECT dbo.tbl_business_unit.legal_entity_abbrv,
dbo.tbl_business_unit.bu#, dbo.tbl_business_unit.bu_name,
tax_type as av_per_return
FROM dbo.tbl_business_unit INNER JOIN
dbo.tbl_tax_bill ON
dbo.tbl_business_unit.bu_name = dbo.tbl_tax_bill.bu_name
INNER JOIN
dbo.tbl_legal_entity ON
dbo.tbl_business_unit.legal_entity_abbrv =
dbo.tbl_legal_entity.legal_entity_abbrv INNER JOIN
dbo.tbl_state ON
dbo.tbl_tax_bill.state_abbrv = dbo.tbl_state.state_abbrv
INNER JOIN
dbo.tbl_tax_district ON
dbo.tbl_tax_bill.tax_district_name =
dbo.tbl_tax_district.tax_district_name CROSS JOIN
[ABC\test].current_year

 

[May]

You may try to put the following statement in Stored
Procedure

May
MCP in Access and SQL Server

 

[Tom Ellison]

Dear JHinkle:

The designer built into Access for ADPs is very restricted. You would
benefit greatly from having the full SQL Server tools. Query Analyzer
is included with that, and would be a much better way to work with
your code.

I suggest you may want to try the Developer Edition of SQL Server.
Though it is licensed only for development purposes, it is otherwise
completely functional. And at only about $50 per copy, it is very
economical.

I am using Access to front-end an SQL database. I am
currently trying to create a query in a .adp file. I can
get the query to run in SQL Query Builder but if I try to
cut and paste the following code into my query in Access,
I get the following error:

"This designer does not graphically support the COMPOUND
STATEMENT SQL construct."

IF (SELECT current_year
FROM current_year) = 2000 BEGIN
(SELECT tax_type
FROM dbo.tbl_tax_bill) END
SELECT dbo.tbl_business_unit.legal_entity_abbrv,
dbo.tbl_business_unit.bu#, dbo.tbl_business_unit.bu_name,
tax_type as av_per_return
FROM dbo.tbl_business_unit INNER JOIN
dbo.tbl_tax_bill ON
dbo.tbl_business_unit.bu_name = dbo.tbl_tax_bill.bu_name
INNER JOIN
dbo.tbl_legal_entity ON
dbo.tbl_business_unit.legal_entity_abbrv =
dbo.tbl_legal_entity.legal_entity_abbrv INNER JOIN
dbo.tbl_state ON
dbo.tbl_tax_bill.state_abbrv = dbo.tbl_state.state_abbrv
INNER JOIN
dbo.tbl_tax_district ON
dbo.tbl_tax_bill.tax_district_name =
dbo.tbl_tax_district.tax_district_name CROSS JOIN
[ABC\test].current_year

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts