Automatically change Date in Query (1 Viewer)

[April15Hater]

Hi,

I have a query that needs to have a date entered into it, but the same date needs to be entered into different locations within the query. I know I can make a dialog box come up and ask for user input, but since it is in multiple places, I don't want to have the date dialog box come up multiple times for the same date. Please see code below. Thank you in advance.

Joe

SELECT Sum(IIf(DateDiff("d",EffectiveDate,Now())<31,1,0)) AS Under30, Sum(IIf(DateDiff("d",EffectiveDate,Now())<61 And DateDiff("d",EffectiveDate,Now())>30,1,0)) AS Under60, Sum(IIf(DateDiff("d",EffectiveDate,Now())<91 And DateDiff("d",EffectiveDate,Now())>60,1,0)) AS Under90, Sum(IIf(DateDiff("d",EffectiveDate,Now())<181 And DateDiff("d",EffectiveDate,Now())>90,1,0)) AS Under180, Sum(IIf(DateDiff("d",EffectiveDate,Now())<366 And DateDiff("d",EffectiveDate,Now())>180,1,0)) AS Under365, Sum(IIf(DateDiff("d",EffectiveDate,Now())>365,1,0)) AS Over365, Sum(IIf([tblTLP_Outstanding].[DR/CR]="dr",[Amount],[Amount]*-1)) AS SumAmount, Left(Right([TRecsAccount],Len([TRecsAccount])-InStr([TrecsAccount],"-")),Len([TRecsAccount])-InStrRev([TrecsAccount],"-")-1) AS 7Acct, DLookUp("Division","tblCenterDivisions","CenterPrefix = '" & Mid([trecsaccount],InStr(6,[TRecsAccount],"-")+1,2) & "'") AS Locat, IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=1 Or Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=9,"Asset",IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=2,"Liability",Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1))) AS AssetLiab
FROM tblTLP_Outstanding
WHERE (((tblTLP_Outstanding.TRecsAccount) Not In (SELECT Account FROM tblBankAccount)))
GROUP BY Left(Right([TRecsAccount],Len([TRecsAccount])-InStr([TrecsAccount],"-")),Len([TRecsAccount])-InStrRev([TrecsAccount],"-")-1), DLookUp("Division","tblCenterDivisions","CenterPrefix = '" & Mid([trecsaccount],InStr(6,[TRecsAccount],"-")+1,2) & "'"), IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=1 Or Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=9,"Asset",IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=2,"Liability",Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)))
UNION SELECT -Sum(IIf(DateDiff("d",EffectiveDate,#04/30/2012#)<61,1,0)) AS Under30, -Sum(IIf(DateDiff("d",EffectiveDate,#04/30/2012#)<91 And DateDiff("d",EffectiveDate,#04/30/2012#)>60,1,0)) AS Under60, -Sum(IIf(DateDiff("d",EffectiveDate,#04/30/2012#)<121 And DateDiff("d",EffectiveDate,#04/30/2012#)>90,1,0)) AS Under90, -Sum(IIf(DateDiff("d",EffectiveDate,#04/30/2012#)<211 And DateDiff("d",EffectiveDate,#04/30/2012#)>120,1,0)) AS Under180, -Sum(IIf(DateDiff("d",EffectiveDate,#04/30/2012#)<396 And DateDiff("d",EffectiveDate,#04/30/2012#)>210,1,0)) AS Under365, -Sum(IIf(DateDiff("d",EffectiveDate,#04/30/2012#)>395,1,0)) AS Over365, Sum(IIf([tblTLP_Outstanding PP].[DR/CR]="dr",[Amount],[Amount]*-1)) AS SumAmount, Left(Right([TRecsAccount],Len([TRecsAccount])-InStr([TrecsAccount],"-")),Len([TRecsAccount])-InStrRev([TrecsAccount],"-")-1) AS 7Acct, DLookUp("Division","tblCenterDivisions","CenterPrefix = '" & Mid([trecsaccount],InStr(6,[TRecsAccount],"-")+1,2) & "'") AS Locat, IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=1 Or Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=9,"Asset",IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=2,"Liability",Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1))) AS AssetLiab
FROM [tblTLP_Outstanding PP]
WHERE ((([tblTLP_Outstanding PP].TRecsAccount) Not In (SELECT Account FROM tblBankAccount)))
GROUP BY Left(Right([TRecsAccount],Len([TRecsAccount])-InStr([TrecsAccount],"-")),Len([TRecsAccount])-InStrRev([TrecsAccount],"-")-1), DLookUp("Division","tblCenterDivisions","CenterPrefix = '" & Mid([trecsaccount],InStr(6,[TRecsAccount],"-")+1,2) & "'"), IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=1 Or Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=9,"Asset",IIf(Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)=2,"Liability",Mid([trecsaccount],InStr(1,[TRecsAccount],"-")+1,1)));

 

[jdraw]

Use a Parameter query.
Here's a sample showing syntax. But you'll have to adjust for your conditions.
Sounds like you need 1 parameter, and just set up the usages within your query sql.

PARAMETERS EnterLandlordRef Text ( 255 ), EnterFirstName Text ( 255 ), EnterSurname Text ( 255 );
SELECT LandlordDetails.LandlordRef
, PropertyDetails.PropertyRef
, PropertyDetails.PropertyAddress
, PropertyDetails.County
, PropertyDetails.PostCode
, PropertyDetails.PropertyType
, PropertyDetails.Bedrooms
, PropertyDetails.Floor
, PropertyDetails.StartDate
, PropertyDetails.EndDate
, PropertyDetails.Rent
, PropertyDetails.GuaranteedRent
, PropertyDetails.Supplier
, LandlordDetails.Firstname
, LandlordDetails.Surname
, LandlordDetails.ContactAddress
, LandlordDetails.HomeNo
, LandlordDetails.WorkNo
, LandlordDetails.MobileNo
, LandlordDetails.EmailAddress
FROM LandlordDetails INNER JOIN PropertyDetails ON LandlordDetails.LandlordRef=PropertyDetails.LandlordRef
WHERE (LandlordDetails.LandlordRef=[EnterLandlordRef] Or
 IsNull(LandlordDetails.LandlordRef) Or 
LandlordDetails.LandlordRef='') And
 (((LandlordDetails.Firstname)=[EnterFirstname]) Or
 IsNull(LandlordDetails.Firstname) Or 
LandlordDetails.Firstname='') And (LandlordDetails.Surname=[EnterSurname] Or IsNull(LandlordDetails.Surname) Or LandlordDetails.Surname='');