The trouble could be in the where clause. That particular structure gets
complicated quickly when it is parsed by Access. If you want to see what
I mean switch to design view and save the query, close it and reopen it in
design view or SQL view.
BY the way, BEFORE you do that MAKE a backup copy of the Query.
I would experiment by removing all the references to the value of the
control being null and see what that brings. If it works then see my
suggestion below.
WHERE (dbo_parcel_base.status="A")
AND (dbo_land_detail.status="A")
AND ([neigh_name] Like "*" & [Forms]![FLandDetailSearch]![cboHood] & "*"
Or [Forms]![FLandDetailSearch]![cboHood] Is Null)
AND ([method_desc] Like "*" & [Forms]![FLandDetailSearch]![cboMethod] &
"*"
Or [Forms]![FLandDetailSearch]![cboMethod] Is Null)
AND ([property_class]=[Forms]![FLandDetailSearch]![cboPropClass]
Or [Forms]![FLandDetailSearch]![cboPropClass] Is Null)
AND
([dbo_parcel_base].[district_number]=[Forms]![FLandDetailSearch]![cboDistrict]
Or [Forms]![FLandDetailSearch]![cboDistrict] Is Null)
AND ([soil_id] Like "*" & [Forms]![FLandDetailSearch]![cboSoilClass]
Or [Forms]![FLandDetailSearch]![cboSoilClass] Is Null)
AND ([land_type_desc] Like "*" & [Forms]![FLanddetailsearch]![cboLandType]
Or [Forms]![FLandDetailSearch]![cboLandType] Is Null)
AND ([dbo_land_detail].[eff_year] Like
[Forms]![FLandDetailSearch]![txtEffYear] & "*"
Or [Forms]![FLandDetailSearch]![txtEffYear] Is Null)
You could use VBA to build the query string and leave out the the phrases
in the where clause when the corresponding controls were null. Do you
know how to use VBA to build the query string? And if you are calling
this query from a form fLandDetailSearch you should be able to attach the
query without the where clause to the report. The base query would be
SELECT ...
FROM ...
WHERE dbo_parcel_base.status="A" AND dbo_land_detail.status="A"
Then on the form, you would have code in a button that looked something
like
Dim strWhere as String
IF IsNull(ME.CboHood)=False Then
strWhere = " AND neigh_name Like ""*" & Me.cboHood & "*"""
End If
If IsNull(Me.cboMethod)= False Then
strWhere = strWhere & " AND method_desc Like ""*" & me.cboMethod & "*"""
End If
IF IsNull(Me.cboPropClass) = False Then
'Assumption here is that Property_Class is a number field and not a text
field.
strWhere = strWhere & " AND property_class = " & Me.cboPropClass
End If
'Chop off the leading " AND " if any criteria was built.
If Len(strWhere) > 0 then strWHERE = Mid (strWhere,6)
DoCmd.OpenReport "YourReportName",,,strWhere
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Don Cossitt said:
SELECT
dbo_neigh_control.neigh_name
, dbo_parcel_base.district_number
, dbo_parcel_base.property_class
, dbo_parcel_base.parcel_id
, dbo_land_detail.soil_id
, dbo_parcel_base.legal_acreage
, dbo_land_types.land_type_desc
, dbo_land_methods.method_desc
, dbo_land_detail.true_tax_value1
FROM (((dbo_land_detail
INNER JOIN dbo_parcel_base ON dbo_land_detail.lrsn =
dbo_parcel_base.lrsn)
INNER JOIN dbo_land_types ON dbo_land_detail.land_type =
dbo_land_types.land_type)
INNER JOIN dbo_neigh_control ON dbo_parcel_base.neighborhood =
dbo_neigh_control.neighborhood)
INNER JOIN dbo_land_methods ON dbo_land_detail.lcm =
dbo_land_methods.method_number
WHERE (((dbo_parcel_base.status)="A")
AND ((dbo_land_detail.status)="A")
AND (([neigh_name] Like "*" & [Forms]![FLandDetailSearch]![cboHood] &
"*" Or [Forms]![FLandDetailSearch]![cboHood] Is Null)=True)
AND (([method_desc] Like "*" & [Forms]![FLandDetailSearch]![cboMethod]
& "*" Or [Forms]![FLandDetailSearch]![cboMethod] Is Null)=True)
AND (([property_class]=[Forms]![FLandDetailSearch]![cboPropClass] Or
[Forms]![FLandDetailSearch]![cboPropClass] Is Null)=True)
AND
(([dbo_parcel_base].[district_number]=[Forms]![FLandDetailSearch]![cboDistrict]
Or [Forms]![FLandDetailSearch]![cboDistrict] Is Null)=True)
AND (([soil_id] Like "*" & [Forms]![FLandDetailSearch]![cboSoilClass]
Or [Forms]![FLandDetailSearch]![cboSoilClass] Is Null)=True)
AND (([land_type_desc] Like "*" &
[Forms]![FLanddetailsearch]![cboLandType] Or
[Forms]![FLandDetailSearch]![cboLandType] Is Null)=True)
AND (([dbo_land_detail].[eff_year] Like
[Forms]![FLandDetailSearch]![txtEffYear] & "*" Or
[Forms]![FLandDetailSearch]![txtEffYear] Is Null)=True));
Whitespace added for readability
I will try the troubleshooting you suggested - thanks
John Spencer said:
You might post the SQL that is failing.
Trouble shooting suggestion
Break the query down and see if you can identify the source of the
problem.
You say you can query the tables individually, so the next step is to
query against two tables. Then 3, then 4.
When the query "breaks", then try removing fields (especially calculated
fields) and see what happens then.
Try building a pass-through query and see if that solves your problem.
You might find a significant increase in speed with a pass-through
query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Using Access 2003 as a report generation tool front end to a SQL Server
2005 backend. I have the latest MDAC, JET and or service packs. My
SQL Server db is remote some 110 miles away and accessed over the
internet through VPN
I have a query involving five 'linked tables' that produces
"ODBC --call failed". Tables work fine individually and refresh works
as well. If the same tables are resident as imported there is no
problem - only when linked. I have adjusted the timeout from 0 to max
allowed to no avail (I don't believe it is a time out issue - but tried
that anyway) The tables range in records from <100 to >28000
I have seen this question posted all over the internet, typically as a
question with no responses and usually dealing with connections made
with code: VB/VBA/php/ASP etc. I am not using code.
TIA
