Steve,
Here is the base query:
sqlStr = "SELECT tblFedFunds.CFDAAgencyNo, tblFedFunds.CFDAFunctionNo,
tblStateFunctions.StateFunctionName, " & _
"tblFedFunds.StatePurpose, tblOPOCategories.OPOCategory,
tblFedFunds.GrantType, " & _
"tblDiscretionaryTypes.DiscretionaryType,
tblFedFunds.AllocationMethod, tblFedFunds.StatuteRequirement, " & _
"tblPrimaryBeneficiaries.PrimaryBene,
tblBeneficiaryDistTypes.BeneDistType, tblFedFunds.OtherBeneficiary, " & _
"tblFedFunds.FedMatch, tblFedFunds.FedPct,
tblFedFunds.StateLocalPct, tblFedFunds.OtherPct, " & _
"tblFedFunds.MatchFactors, tblFedFunds.FedMOE,
tblFedFunds.MOEAmount, tblFedFunds.MOEFactors, " & _
"tblFedFunds.FedRepReq, tblFedFunds.RepReqFactors,
tblFedFunds.FedPerformanceReq, " & _
"tblFedFunds.PerformanceReqFactors,
tblFedFunds.FundingLvl1,
tblFedFunds.FundingLvl2, " & _
"tblFedFunds.MatchRate, tblFedFunds.OutlookMOE,
tblFedFunds.OutlookPerformanceReq, tblFedFunds.Comments, " & _
"Summary_Table.Agency, Summary_Table.Entity,
Summary_Table.Project, Summary_Table.Year, Summary_Table.Current " & _
"FROM (tblStateFunctions INNER JOIN (tblOPOCategories INNER
JOIN (tblPrimaryBeneficiaries INNER JOIN " & _
"(tblDiscretionaryTypes INNER JOIN
(tblBeneficiaryDistTypes INNER JOIN tblFedFunds ON " & _
"tblBeneficiaryDistTypes.BeneDistID =
tblFedFunds.DistributionTypeID) ON " & _
"tblDiscretionaryTypes.DiscretionaryTypeID =
tblFedFunds.DiscretionaryTypeID) ON " & _
"tblPrimaryBeneficiaries.PrimaryBeneID =
tblFedFunds.PrimaryBeneID) ON " & _
"tblOPOCategories.OPOID = tblFedFunds.OPOID) ON " & _
"tblStateFunctions.StateFunctionID =
tblFedFunds.StateFunctionID) INNER JOIN " & _
"(tblStateFunctionDetails INNER JOIN Summary_Table ON "
& _
"tblStateFunctionDetails.StateFundNumber =
Summary_Table.Entity) ON " & _
"tblStateFunctions.StateFunctionID =
tblStateFunctionDetails.StateFunctionID " & _
"WHERE ((([tblStateFunctionDetails]![StateFundNumber] &
[tblStateFunctionDetails]![StateProjectNumber])=[Summary_Table]![Entity] &
[Summary_Table]![Project])) " & _
"AND "
To this base query, I add the filter property from the form when the user
clicks a button. Say the user filters records, using filter by form, for
"StatuteRequirement = Federal"... The filter property of the form contains
this: "((tblFedFunds.StatuteRequirement="Federal"))"
So, I concatenate the filter to the base query like so:
sqlStr = "SELECT tblFedFunds.CFDAAgencyNo, tblFedFunds.CFDAFunctionNo,
tblStateFunctions.StateFunctionName, " & _
"tblFedFunds.StatePurpose, tblOPOCategories.OPOCategory,
tblFedFunds.GrantType, " & _
"tblDiscretionaryTypes.DiscretionaryType,
tblFedFunds.AllocationMethod, tblFedFunds.StatuteRequirement, " & _
"tblPrimaryBeneficiaries.PrimaryBene,
tblBeneficiaryDistTypes.BeneDistType, tblFedFunds.OtherBeneficiary, " & _
"tblFedFunds.FedMatch, tblFedFunds.FedPct,
tblFedFunds.StateLocalPct, tblFedFunds.OtherPct, " & _
"tblFedFunds.MatchFactors, tblFedFunds.FedMOE,
tblFedFunds.MOEAmount, tblFedFunds.MOEFactors, " & _
"tblFedFunds.FedRepReq, tblFedFunds.RepReqFactors,
tblFedFunds.FedPerformanceReq, " & _
"tblFedFunds.PerformanceReqFactors,
tblFedFunds.FundingLvl1,
tblFedFunds.FundingLvl2, " & _
"tblFedFunds.MatchRate, tblFedFunds.OutlookMOE,
tblFedFunds.OutlookPerformanceReq, tblFedFunds.Comments, " & _
"Summary_Table.Agency, Summary_Table.Entity,
Summary_Table.Project, Summary_Table.Year, Summary_Table.Current " & _
"FROM (tblStateFunctions INNER JOIN (tblOPOCategories INNER
JOIN (tblPrimaryBeneficiaries INNER JOIN " & _
"(tblDiscretionaryTypes INNER JOIN
(tblBeneficiaryDistTypes INNER JOIN tblFedFunds ON " & _
"tblBeneficiaryDistTypes.BeneDistID =
tblFedFunds.DistributionTypeID) ON " & _
"tblDiscretionaryTypes.DiscretionaryTypeID =
tblFedFunds.DiscretionaryTypeID) ON " & _
"tblPrimaryBeneficiaries.PrimaryBeneID =
tblFedFunds.PrimaryBeneID) ON " & _
"tblOPOCategories.OPOID = tblFedFunds.OPOID) ON " & _
"tblStateFunctions.StateFunctionID =
tblFedFunds.StateFunctionID) INNER JOIN " & _
"(tblStateFunctionDetails INNER JOIN Summary_Table ON "
& _
"tblStateFunctionDetails.StateFundNumber =
Summary_Table.Entity) ON " & _
"tblStateFunctions.StateFunctionID =
tblStateFunctionDetails.StateFunctionID " & _
"WHERE ((([tblStateFunctionDetails]![StateFundNumber] &
[tblStateFunctionDetails]![StateProjectNumber])=[Summary_Table]![Entity] &
[Summary_Table]![Project])) " & _
"AND ((tblFedFunds.StatuteRequirement="Federal"))"
Then I change the definition of a stored query (that I use for such
things)
with the query I just wrote:
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qryDynamic")
qdf.SQL = strSQL
qdf.Close
Then, I can output the SAME EXACT records (i.e., the FILTERED records) to
Excel like so:
DoCmd.OutputTo acOutputQuery, "qryDynamic", acFormatXLS,
"c:\temp\SpreadSheetName.xls", False
Like I said in my first post, this works great until the user filters
records with one of the fields that are a Combo Box type, where the source
is
a lookup to another table. When they do this the filter looks like this:
"((Lookup_StateFunctionID.StateFunctionName="Abandoned Mine Lands"))". I
think this is related to the lookup on the combo box, the StateFunctionID
is
the bound column, but I'm displaying the StateFunctionName.
What I need it to say is "tblFedFunds.StateFunctionID=1" (1 is the ID of
the
name in this example).
I have limited, sporadic experience with Access. There is probably a much
better way, but I'm not aware of it with my limited experience. Any
suggestions would help.
--
Jim
[MVP] S.Clark said:
Post your exact SQL.
--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips:
http://www.fmsinc.com/free/tips.html
Jim Gordon said:
I'm trying to use the filter property on a form to output the filtered
(selected) records to an excel spreadsheet. The fields of the form are
bound
to a table.
I can easily do this by grabbing the filter property on the form (after
the
user has applied a filter), and using it to change the QueryDef of a
query.
Then using that query as the source for a DoCmd.OutputTo method.
It works great unless the filter includes a lookup field (combo box).
Rather
than [field]=recordID, the filter says "lookup_
.[field]="text of
the
lookup field"," then the user gets prompted when the query runs.
Am I not using this properly? Is there a simple way around this?
