Between Dates Parameter Query - Extra Records Generated??

CarolW

Registered User.
Local time
Today, 14:40
Joined
Mar 24, 2006
Messages
58
Good Evening Everybody,
I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:

ID: Auto Number
Area: Text
Equipment:Text
EquipSrlNo:Text
EquipLocalNo:Text
MOPNo:Number
DateDone:Date/Time
MOPPeriodicity:Text


I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???

SQL
SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity
FROM [Main Data]
WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));

Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.

Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?


Look forward to your response(s)


Best Regards

CarolW
 
The between dates parameter should apply to the DateDone field, not the nested IIF() expression for TestDueNext.


Note:
In an IIF() expression, besides dealing with the situation when the condition is true, we also need to deal with the situation when the condition is untrue.

So the TestDueNext should be:-
IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),
IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),
IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),
IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]), xxxx )))) AS TestDueNext

where xxxx is the date (or a Null value) to be returned when none of the IIF conditions are true.


On the other hand, if [MOPPeriodicity] contains only the four options of 1,3,6 and WEEKLY, you can simplify the IIF expression for TestDueNext like this:-
IIf([MOPPeriodicity]="WEEKLY", DateAdd("ww",1,[DateDone]), DateAdd("m",[MOPPeriodicity],[DateDone])) AS TestDueNext
.
 
Last edited:
The between dates parameter should apply to the DateDone field, not the nested IIF() expression for TestDueNext.


Note:
In an IIF() expression, besides dealing with the situation when the condition is true, we also need to deal with the situation when the condition is untrue.

So the TestDueNext should be:-
IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),
IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),
IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),
IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]), xxxx )))) AS TestDueNext

where xxxx is the date (or a Null value) to be returned when none of the IIF conditions are true.


On the other hand, if [MOPPeriodicity] contains only the four options of 1,3,6 and WEEKLY, you can simplify the IIF expression for TestDueNext like this:-
IIf([MOPPeriodicity]="WEEKLY", DateAdd("ww",1,[DateDone]), DateAdd("m",[MOPPeriodicity],[DateDone])) AS TestDueNext
.
Jon K
Thank you ever so much for your advice, particularly with relation to the use of the IIF expression. The bit that you mentioned about the "dates parameter should apply to the DateDone field", the main problem that I have is basically that they want it done on the TestDueNext field. TestDueNext calculates when a bit of kit is due up for inspection, and what they are after is a report to be generated that will show them what item(s) of kit will need to be re-inspected during a particular period, i.e 3-Apr to 30 Apr 20 items
are due for inspection- they can then assign a person to go away and test them and once tested they then update the DateDone field, which in turn then recalculates the TestDueNext field, and so the cycle goes on. I do hope what I've said makes sense??

Regards

CarolW
 
I'm sorry I misunderstood your question.

I think the nested IIF in your original query has made Access treat the two parameters as text strings rather than two dates. Your can try declaring their data type.

In query Design View, choose menu Query, Parameters...
In the dialog box that pops up, type [Enter Date Start] in the first column and select the correct data type in the second column.
Do the same for the other parameter [Enter Date Stop].
Click OK to close the dialog box.
.
 
I'm sorry I misunderstood your question.

I think the nested IIF in your original query has made Access treat the two parameters as text strings rather than two dates. Your can try declaring their data type.

In query Design View, choose menu Query, Parameters...
In the dialog box that pops up, type [Enter Date Start] in the first column and select the correct data type in the second column.
Do the same for the other parameter [Enter Date Stop].
Click OK to close the dialog box.
.
Jon K,
Thanks for the quick response to my earlier request. I tried what you said above and 'hey presto' it worked like a treat. I am no longer getting additional records which fall outside of the Start And Stop parameter boxes which is just great news. Thank you so very much - I've learnt another interesting fact about Access and it's all down to you.

Cheers for your time and assistance.

Extremely grateful...

Best Regards

CarolW
 

Users who are viewing this thread

Back
Top Bottom