Conditional Date Search 3

[marcit]

I need some help with the following:
I have 3 date fields [Proj Date], [Proj Date 2] and [Proj Date 3]. These allow for users to project up to 3 dates for an event to take place. I have set up a parameter query that asks for a [Start Date] and [End Date]. I have the following entered in the QBE grid for each of the projected fields:
Is Not Null And Between [Start Date] And [End Date].

If a record has 30/7/2001, 30/8/2001 and 31/12/2002 for the [Proj Date], [Proj Date 2] and [Proj Date 3], and I enter 1/4/2001 and 30/9/2001 for the [Start Date] and [End Date], I do not want the record to come up as it has been re-projected to 31/12/2002. I need to look at what is projected to come in ONLY between the [Start Date] and [End Date], NOT anything with a later or earlier date. I am not a programmer, but a prepared to have a go at any suggestions that anyone has! Thanks

 

[dneufarth]

Provided your app is maintaining the dates properly (dates must be greater than the previous date and prev dates can't be null if a subsequent date is specified), you might try an OR relationship between criteria:

projdate projdate2 projdate3
criteria 1 between... null null
criteria 2 between... null
criteria 3 between...

If any are true, it's valid.


Dave

 

[Lightning]

Hi Marcit

Is there always a value in each of the ProjDate fields for each record? If not, the "Is Not Null" statement will filter out those records that do not have values in all three fields.

Can you post the SQL code for your query here? You can get this code by viewing your query in SQLView (VIEW|SQL VIEW on the Menu bar). This would help us to help you.

Lightning

 

[marcit]

Thanks very much!

Swapped the Nulls around and it all works great!

Here's the SQL though as requested in case it can be further improved upon!

PARAMETERS [Enter Adviser Name] Text ( 255 ), [Start Date] DateTime, [End Date] DateTime;
SELECT [Trainee Table].[Trainee ID], [Training Advisor].[Training Advisor Name], [Trainee Table].Forenames, [Trainee Table].Surname, [Joining Table].[Proj Date], [Joining Table].[Proj Date 2], [Joining Table].[Proj Date 3], [Joining Table].Comments, [Outcome Table].Scheme, [Outcome Table].Outcome, [Outcome Table].Rate, [Outcome Table].Value, [Joining Table].[Period Claimed], [Joining Table].[Claim Date], [Joining Table].[Not Achievable]
FROM ([Training Advisor] INNER JOIN [Trainee Table] ON [Training Advisor].[Training Advisor ID] = [Trainee Table].[Training Advisor]) INNER JOIN ([Outcome Table] INNER JOIN [Joining Table] ON [Outcome Table].[Outcome Number] = [Joining Table].[Outcome ID]) ON [Trainee Table].[Trainee ID] = [Joining Table].[Trainee ID]
WHERE ((([Training Advisor].[Training Advisor Name]) Like "*" & [Enter Adviser Name] & "*&quot AND (([Joining Table].[Proj Date]) Is Not Null And ([Joining Table].[Proj Date]) Between [Start Date] And [End Date]) AND (([Joining Table].[Period Claimed]) Is Null) AND (([Joining Table].[Claim Date]) Is Null) AND (([Joining Table].[Not Achievable])=No)) OR ((([Training Advisor].[Training Advisor Name]) Like "*" & [Enter Adviser Name] & "*&quot AND (([Joining Table].[Proj Date 2]) Is Not Null And ([Joining Table].[Proj Date 2]) Between [Start Date] And [End Date]) AND (([Joining Table].[Period Claimed]) Is Null) AND (([Joining Table].[Claim Date]) Is Null) AND (([Joining Table].[Not Achievable])=No)) OR ((([Training Advisor].[Training Advisor Name]) Like "*" & [Enter Adviser Name] & "*&quot AND (([Joining Table].[Proj Date 3]) Is Not Null And ([Joining Table].[Proj Date 3]) Between [Start Date] And [End Date]) AND (([Joining Table].[Period Claimed]) Is Null) AND (([Joining Table].[Claim Date]) Is Null) AND (([Joining Table].[Not Achievable])=No))
ORDER BY [Training Advisor].[Training Advisor Name];

 

[Lightning]

Glad I could help.

Now that it is working, I wouldn't fiddle around with the SQL code. If it works, it's good.

Lightning