Cannot run report based on date range?

  • Thread starter Thread starter JK
  • Start date Start date
J

JK

Would anyone be able to tell me why I cannot run the following query based on
a date range? I want the user to be able to enter a date range into a form
and then have the form pull the report based that date range. For some
reason, it's not working. I'm using access 2003; if you need more
information, please let me know.

Thanks!
Jason

SELECT qryDepreciationCartesian_RPT.DepreciationID,
qryDepreciationCartesian_RPT.InstanceID,
IIf(DepreciationException.DepreciationID Is
Null,IIf((qryDepreciationCartesian_RPT.PeriodTypeID Is Null) Or
(qryDepreciationCartesian_RPT.PeriodFreq Is Null) Or
(qryDepreciationCartesian_RPT.InstanceID Is
Null),qryDepreciationCartesian_RPT.PurchaseDate,DateAdd(qryDepreciationCartesian_RPT.PeriodTypeID,qryDepreciationCartesian_RPT.InstanceID*qryDepreciationCartesian_RPT.PeriodFreq,qryDepreciationCartesian_RPT.PurchaseDate)),IIf(DepreciationException.IsCanned,Null,DepreciationException.InstanceDate))
AS DepDate, qryDepreciationCartesian_RPT.PurchaseDate,
qryDepreciationCartesian_RPT.DepreciableLife, DepreciationException.IsCanned,
qryDepreciationCartesian_RPT.AssetID,
qryDepreciationCartesian_RPT.PurchasePrice,
qryDepreciationCartesian_RPT.SalvageValue,
qryDepreciationCartesian_RPT.DepreciationDate,
qryDepreciationCartesian_RPT.DepreciationAmount,
qryDepreciationCartesian_RPT.PeriodFreq, DepPeriodType.PeriodType,
DDB([PurchasePrice],[SalvageValue],[DepreciableLife],[InstanceID]) AS myDDB,
qryDepreciationCartesian_RPT.Comment, DepreciationException.InstanceComment,
qryDepreciationAssets.AssetName, qryDepreciationAssets.AssetDescription,
qryDepreciationAssets.Category, qryDepreciationAssets.DateSold
FROM ((qryDepreciationCartesian_RPT LEFT JOIN DepreciationException ON
(qryDepreciationCartesian_RPT.DepreciationID =
DepreciationException.DepreciationID) AND
(qryDepreciationCartesian_RPT.InstanceID = DepreciationException.InstanceID))
LEFT JOIN DepPeriodType ON qryDepreciationCartesian_RPT.PeriodTypeID =
DepPeriodType.PeriodTypeId) INNER JOIN qryDepreciationAssets ON
qryDepreciationCartesian_RPT.AssetID = qryDepreciationAssets.ID
WHERE ((Not (qryDepreciationCartesian_RPT.DepreciationID) Is Null) AND
((IIf([DepreciationException].[DepreciationID] Is
Null,IIf(([qryDepreciationCartesian_RPT].[PeriodTypeID] Is Null) Or
([qryDepreciationCartesian_RPT].[PeriodFreq] Is Null) Or
([qryDepreciationCartesian_RPT].[InstanceID] Is
Null),[qryDepreciationCartesian_RPT].[PurchaseDate],DateAdd([qryDepreciationCartesian_RPT].[PeriodTypeID],[qryDepreciationCartesian_RPT].[InstanceID]*[qryDepreciationCartesian_RPT].[PeriodFreq],[qryDepreciationCartesian_RPT].[PurchaseDate])),IIf([DepreciationException].[IsCanned],Null,[DepreciationException].[InstanceDate])))
Between [Forms]![frmReport_EventsByAsset]![txtBeginning] And
[Forms]![frmReport_EventsByAsset]![txtEnding]))
ORDER BY qryDepreciationCartesian_RPT.DepreciationID,
qryDepreciationCartesian_RPT.InstanceID;
 
"not working" isn't very informative...

Do you get an error message? What does it say?

You might want to take a look at how date values are delimited. It may be
that you need to concatentate the "#" symbol into your SQL expression to
delimit the date values found in the form's controls...

Regards

Jeff Boyce
Microsoft Office/Access MVP


JK said:
Would anyone be able to tell me why I cannot run the following query based
on
a date range? I want the user to be able to enter a date range into a form
and then have the form pull the report based that date range. For some
reason, it's not working. I'm using access 2003; if you need more
information, please let me know.

Thanks!
Jason

SELECT qryDepreciationCartesian_RPT.DepreciationID,
qryDepreciationCartesian_RPT.InstanceID,
IIf(DepreciationException.DepreciationID Is
Null,IIf((qryDepreciationCartesian_RPT.PeriodTypeID Is Null) Or
(qryDepreciationCartesian_RPT.PeriodFreq Is Null) Or
(qryDepreciationCartesian_RPT.InstanceID Is
Null),qryDepreciationCartesian_RPT.PurchaseDate,DateAdd(qryDepreciationCartesian_RPT.PeriodTypeID,qryDepreciationCartesian_RPT.InstanceID*qryDepreciationCartesian_RPT.PeriodFreq,qryDepreciationCartesian_RPT.PurchaseDate)),IIf(DepreciationException.IsCanned,Null,DepreciationException.InstanceDate))
AS DepDate, qryDepreciationCartesian_RPT.PurchaseDate,
qryDepreciationCartesian_RPT.DepreciableLife,
DepreciationException.IsCanned,
qryDepreciationCartesian_RPT.AssetID,
qryDepreciationCartesian_RPT.PurchasePrice,
qryDepreciationCartesian_RPT.SalvageValue,
qryDepreciationCartesian_RPT.DepreciationDate,
qryDepreciationCartesian_RPT.DepreciationAmount,
qryDepreciationCartesian_RPT.PeriodFreq, DepPeriodType.PeriodType,
DDB([PurchasePrice],[SalvageValue],[DepreciableLife],[InstanceID]) AS
myDDB,
qryDepreciationCartesian_RPT.Comment,
DepreciationException.InstanceComment,
qryDepreciationAssets.AssetName, qryDepreciationAssets.AssetDescription,
qryDepreciationAssets.Category, qryDepreciationAssets.DateSold
FROM ((qryDepreciationCartesian_RPT LEFT JOIN DepreciationException ON
(qryDepreciationCartesian_RPT.DepreciationID =
DepreciationException.DepreciationID) AND
(qryDepreciationCartesian_RPT.InstanceID =
DepreciationException.InstanceID))
LEFT JOIN DepPeriodType ON qryDepreciationCartesian_RPT.PeriodTypeID =
DepPeriodType.PeriodTypeId) INNER JOIN qryDepreciationAssets ON
qryDepreciationCartesian_RPT.AssetID = qryDepreciationAssets.ID
WHERE ((Not (qryDepreciationCartesian_RPT.DepreciationID) Is Null) AND
((IIf([DepreciationException].[DepreciationID] Is
Null,IIf(([qryDepreciationCartesian_RPT].[PeriodTypeID] Is Null) Or
([qryDepreciationCartesian_RPT].[PeriodFreq] Is Null) Or
([qryDepreciationCartesian_RPT].[InstanceID] Is
Null),[qryDepreciationCartesian_RPT].[PurchaseDate],DateAdd([qryDepreciationCartesian_RPT].[PeriodTypeID],[qryDepreciationCartesian_RPT].[InstanceID]*[qryDepreciationCartesian_RPT].[PeriodFreq],[qryDepreciationCartesian_RPT].[PurchaseDate])),IIf([DepreciationException].[IsCanned],Null,[DepreciationException].[InstanceDate])))
Between [Forms]![frmReport_EventsByAsset]![txtBeginning] And
[Forms]![frmReport_EventsByAsset]![txtEnding]))
ORDER BY qryDepreciationCartesian_RPT.DepreciationID,
qryDepreciationCartesian_RPT.InstanceID;
Click to expand...
 
I agree with Jeff, my first guess is that the query does not recognize the
controls on your form as containing date values. As Jeff indicates, you can
pre and post-pend a # to these like:

Between "#" & [Forms]![frmReport_EventsByAsset]![txtBeginning] & "#"
And "#" & [Forms]![frmReport_EventsByAsset]![txtEnding] & "#"

Or, another method would be to declare these parameters as date values. To
do this, right click in the top of the query grid (not on a table) and
select the Parameters option. Then copy the text
"[Forms]![frmReport_EventsByAsset]![txtBeginning]" and paste it in the first
block on the top row, and then set the data type to date, do this for
"[Forms]![frmReport_EventsByAsset]![txtEnding]" as well. This will ensure
that the query interprets those textboxes as date values.


JK said:
Would anyone be able to tell me why I cannot run the following query based
on
a date range? I want the user to be able to enter a date range into a form
and then have the form pull the report based that date range. For some
reason, it's not working. I'm using access 2003; if you need more
information, please let me know.

Thanks!
Jason

SELECT qryDepreciationCartesian_RPT.DepreciationID,
qryDepreciationCartesian_RPT.InstanceID,
IIf(DepreciationException.DepreciationID Is
Null,IIf((qryDepreciationCartesian_RPT.PeriodTypeID Is Null) Or
(qryDepreciationCartesian_RPT.PeriodFreq Is Null) Or
(qryDepreciationCartesian_RPT.InstanceID Is
Null),qryDepreciationCartesian_RPT.PurchaseDate,DateAdd(qryDepreciationCartesian_RPT.PeriodTypeID,qryDepreciationCartesian_RPT.InstanceID*qryDepreciationCartesian_RPT.PeriodFreq,qryDepreciationCartesian_RPT.PurchaseDate)),IIf(DepreciationException.IsCanned,Null,DepreciationException.InstanceDate))
AS DepDate, qryDepreciationCartesian_RPT.PurchaseDate,
qryDepreciationCartesian_RPT.DepreciableLife,
DepreciationException.IsCanned,
qryDepreciationCartesian_RPT.AssetID,
qryDepreciationCartesian_RPT.PurchasePrice,
qryDepreciationCartesian_RPT.SalvageValue,
qryDepreciationCartesian_RPT.DepreciationDate,
qryDepreciationCartesian_RPT.DepreciationAmount,
qryDepreciationCartesian_RPT.PeriodFreq, DepPeriodType.PeriodType,
DDB([PurchasePrice],[SalvageValue],[DepreciableLife],[InstanceID]) AS
myDDB,
qryDepreciationCartesian_RPT.Comment,
DepreciationException.InstanceComment,
qryDepreciationAssets.AssetName, qryDepreciationAssets.AssetDescription,
qryDepreciationAssets.Category, qryDepreciationAssets.DateSold
FROM ((qryDepreciationCartesian_RPT LEFT JOIN DepreciationException ON
(qryDepreciationCartesian_RPT.DepreciationID =
DepreciationException.DepreciationID) AND
(qryDepreciationCartesian_RPT.InstanceID =
DepreciationException.InstanceID))
LEFT JOIN DepPeriodType ON qryDepreciationCartesian_RPT.PeriodTypeID =
DepPeriodType.PeriodTypeId) INNER JOIN qryDepreciationAssets ON
qryDepreciationCartesian_RPT.AssetID = qryDepreciationAssets.ID
WHERE ((Not (qryDepreciationCartesian_RPT.DepreciationID) Is Null) AND
((IIf([DepreciationException].[DepreciationID] Is
Null,IIf(([qryDepreciationCartesian_RPT].[PeriodTypeID] Is Null) Or
([qryDepreciationCartesian_RPT].[PeriodFreq] Is Null) Or
([qryDepreciationCartesian_RPT].[InstanceID] Is
Null),[qryDepreciationCartesian_RPT].[PurchaseDate],DateAdd([qryDepreciationCartesian_RPT].[PeriodTypeID],[qryDepreciationCartesian_RPT].[InstanceID]*[qryDepreciationCartesian_RPT].[PeriodFreq],[qryDepreciationCartesian_RPT].[PurchaseDate])),IIf([DepreciationException].[IsCanned],Null,[DepreciationException].[InstanceDate])))
Between [Forms]![frmReport_EventsByAsset]![txtBeginning] And
[Forms]![frmReport_EventsByAsset]![txtEnding]))
ORDER BY qryDepreciationCartesian_RPT.DepreciationID,
qryDepreciationCartesian_RPT.InstanceID;
Click to expand...
 
Back
Top