Filter Form 2

[cneill]

Hi, I am trying to filter a Pop up remainder form and just can't seams to show the right records that I want it to display.
I have tried lots of permutations so any help would be greatly received.

The criteria in the query returns all the records
Between Date() And [RentPeriodTo]
I also have a PolicyRenewed tick box on the form, so in the query this field have a criteria of 0
What I would like is to return all records that are dew to be renewed within the next 30 days and if the policy has not been renewed i.e. the tick box on the form has not been ticked to continue to show those records as well until the tick box is ticked.
Many Thanks

CNEILL

 

[PWise]

how about
Where (fieldname Between Date() And [RentPeriodTo]) or (PolicyRenewed =0)

 

[cneill]

Hi PWise

Access adds " before and after the filename like so
Where ("RenewalDate" Between Date() And [RentPeriodTo]) or (PolicyRenewed =0)

But the query is telling me
Undefined Function 'Where'in expression

I presume that you still wanted me to put this in the query criteria?

Any further thoughts?

cneill

 

[PWise]

manulally add brakets

Where ([RenewalDate] Between Date() And [RentPeriodTo]) or (PolicyRenewed =0)

 

[cneill]

Hi PWise,

Still get the same message
Undefined Function 'Where'in expression

any thoughts?

so I think I will change the RenewalDate field criteria to
Between Date()-120 And Date()+30

with the PolicyRenewed field criteria as 0
not ideal because if the policy is not renewed within 120 days it will disappear from the filtered list, any thoughts on how to keep showing the policy is the PolicyRenewed field criteria is 0?
thanks
cneill

 

[PWise]

please post your whole where statment

 

[lameid]

cneill,

It sounds like you are trying to enter the criteria in the QBE (query designer) on the criteria line where as PHV is telling you how to modify the Where clause in SQL view....

Equivalently put the following in the first criteria line under your RenewalDate field...
Between Date() And [RentPeriodTo]
Next Put =0 on the SECOND criteria line under PolicyRenewed

Almost everyone posts the SQL text here as it posts well wheras pictures not so much.

 

[cneill]

Hi PWise and Lameid

I am using the standard QBE(query designer)this query currently works to return the correct number of records, but it is not ideal because if a policy has not been renewed after 120 days it will not be included in the list, what I would like ideally like is for the query to show me the policies that are coming up for renewal within the next 30 days, these policies need to remain in view on the pop up form until the user puts a tick in the PolicyRenewed box

This is the full query in SQL view

SELECT TblContactTypes.ContactTypesID, TblSiteData.Organisation AS [Letting Agent], TblHouseData.Address AS [House Address], TblDescriptionList.Description AS [Policy Type], TblFinance.RentPeriodTo AS [Renewal Date], TblContractorList.Organisation, TblFinance.PolicyRenewed
FROM TblSiteData INNER JOIN (TblHouseData INNER JOIN (TblDescriptionList INNER JOIN ((TblContactTypes INNER JOIN TblContractorList ON TblContactTypes.ContactTypesID = TblContractorList.ContactTypesID) INNER JOIN TblFinance ON TblContractorList.ContractorID = TblFinance.ContractorID) ON TblDescriptionList.DescriptionID = TblFinance.DescriptionID) ON TblHouseData.HID = TblFinance.HID) ON TblSiteData.LAID = TblHouseData.LAID
WHERE (((TblContactTypes.ContactTypesID) Like 3 Or (TblContactTypes.ContactTypesID) Like 7 Or (TblContactTypes.ContactTypesID) Like 8) AND ((TblFinance.RentPeriodTo) Between Date()-120 And Date()+30) AND ((TblFinance.PolicyRenewed)=0))
ORDER BY TblFinance.RentPeriodTo;

Many thanks for your help

CNEILL

 

[lameid]

I think you are saying you want to see all items that have not been renewed that have renewal dates up to 30 days into the future.

You want to use an expression like
>=Date()+30
instead of
Between Date()-120 And Date()+30

 

[cneill]

Hi lameid,

Thanks for pointing me in the right direction, sometimes you just can't see for looking.
Many Thanks

CNEILL