syntax for <= criteria comparing parameter dates to date field 1

[topwaya]

I have searched and searched these forums but can't find an answer to this. If it's already here somewhere - please point me in the right direction!

I have the following query that works fine until I add the WHERE statement to filter the results by the parameters. Using the WHERE statement below I get no results to my query.

I am trying to only pick members who's start date (FCSD) is less than or equal to the [Enter Start Date] parameter AND who's cancelled date (XLD) is null or greater than the [Enter End Date].

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count(qryFCMembers.FullName) AS CountOfFullName
SELECT qryFCMembers.Mtype, Count(qryFCMembers.PID) AS [Total FCMembers]
FROM qryFCMembers
WHERE (((qryFCMembers.FCSD)<=[Enter Start Date]) AND ((qryFCMembers.XLD)>=NZ([Enter Start Date])))
GROUP BY qryFCMembers.Mtype, qryFCMembers.FCSD, qryFCMembers.XLD
PIVOT qryFCMembers.LocW In ("AGR","BB","BTW","FC","FGW","FM","FST","FTG","GB","HP","HPL","KNG","LSM","MIN","NOR","NW","P","PAR","RVW","SAV","SFM","SP","UNC","WDM","WG");

Any help would be appreciated!!

 

[PHV]

Replace this:
(((qryFCMembers.FCSD)<=[Enter Start Date])
By this:
(((qryFCMembers.FCSD)<=[Enter End Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thank you so much for trying to help - unfortunately I still get no results.

I only have 6 test FCSD fields:
5/5/2000
4/4/2004
4/4/2001
3/3/1998
2/3/2000
1/1/1992

I am entering 1/1/2005 in the [Enter Start Date] parameter and 6/1/2005 in the [Enter End Date] parameter. I even tried entering 1/1/1992 for the start date, still no results. Thank you for any help!

 

[PHV]

Is qryFCMembers.FCSD a true DateTime field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

now, you did change the query like PHV said, right? The query you posted above doesn't prompt for the End Date at all:

WHERE (((qryFCMembers.FCSD)<=[b][Enter Start Date][/b]) AND ((qryFCMembers.XLD)>=NZ([b][Enter Start Date][/b])))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[topwaya]

Still getting no results. Thanks so much for all your help - I thought I was going crazy 8)

I checked the tblPersonal where the original FCSD and XLD filed are in, and they are designated Date/Time with a SHORT DATE format.

I checked the qryFCMember and made the FCSD and XLD fields SHORT DATE format.

I checked also checked this query and made sure that the FCSD And XLD fields are SHORT DATE format, and did the input mask for SHORT DATE.

Is there something else that is supposed to designate the Date/Time for these fields?

My current WHERE statement is:

WHERE (((qryFCMembers.FCSD)>=[Enter End Date]) AND ((qryFCMembers.XLD)>=[Enter Start Date]))

My entire code is:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count(qryFCMembers.FullName) AS CountOfFullName
SELECT qryFCMembers.Mtype, Count(qryFCMembers.PID) AS [Total FCMembers]
FROM qryFCMembers
WHERE (((qryFCMembers.FCSD)>=[Enter End Date]) AND ((qryFCMembers.XLD)>=[Enter Start Date]))
GROUP BY qryFCMembers.Mtype, qryFCMembers.FCSD, qryFCMembers.XLD
PIVOT qryFCMembers.LocW In ("AGR","BB","BTW","FC","FGW","FM","FST","FTG","GB","HP","HPL","KNG","LSM","MIN","NOR","NW","P","PAR","RVW","SAV","SFM","SP","UNC","WDM","WG");

Any Help is Appreciated - Thank You!!

 

[PHV]

Your where is still wrong:
WHERE (((qryFCMembers.FCSD)[highlight]<[/highlight]=[Enter End Date]) AND ((qryFCMembers.XLD)>=[Enter Start Date]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[topwaya]

Thank you - is that why I was supposed to pay attention in third grade to logical expressions?

Unfortunately, I still got no results, but I figured out why.

If I take out AND ((qryFCMembers.XLD)>=[Enter Start Date])) it works. That's because most of the XLD fields are null - (brain fart on my part for not mentioning that small detail) so I tried the following code and it appears to work - if the code is wrong, will you please let me know?

WHERE (((qryFCMembers.FCSD)<=[Enter End Date]) AND ((qryFCMembers.XLD Is Null) OR ((qryFCMembers.XLD)>=[Enter Start Date])))

Thank you SOOO MUCH to everyone here!!!! You guys are invaluable!