Date Parameters in SQL Query 1

[gRegulator]

I have the following SQL Query:

SELECT qry_q31_offence_types.Offence, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_q31_offence_types
GROUP BY qry_q31_offence_types.Offence;

I have been trying to put in date parameters but I have been out of luck. I have tried putting in the following:

PARAMETERS [Start] DateTime, [End] DateTime;
SELECT qry_q31_offence_types.Offence, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_q31_offence_types
WHERE [Admit] Between [Start] AND [End]
GROUP BY qry_q31_offence_types.Offence;

[Admit] comes from qry_q31_offence_types and is a date field.

Basically, I want to have all records that were entered from this time frame be counted in the query. I think I am doing it right, but I never seem to get the right result, or I get an error saying the query is too complicated.

I am very confused right now, can anyone help?

Thanks

 

[PCLewis]

Given the information provided, there is nothing wrong with the SQL.

If you are not getting the results you expect, then either some of the dates in Admit are null or Start and End are not being entered as dates the exact way you think.

Just to be positive, change [Admit] to qry_q3_offence_types.Admit and drop a note showing the format that you enter the date parameters.

 

[gRegulator]

Hi PC,

Thanks for your reply.

I have changed Admit to qry_q31_offence_types.Admit.

I have checked all records and there are no null values.

The dates are entered in the format mm/dd/yyyy.

I am still having the same luck.

I'm very confused because as you said, the SQL looks ok.

What do you think?

 

[Zonie32]

instead of PARAMETERS, maybe try typing BETWEEN

 

[PCLewis]

Well since you enter dates in mm/dd/yyyy format, can you tell me the exact dates and an example of one record that is not picked up by your query which you think should be?

I am always dubious of mm/dd/yyyy or dd/mm/yyyy formats when entering date data. They are ambiguous. I always enter days dd-mmm-yyyy (partially because Oracle needs it like that too and as habit's go, it's not a bad one at all).

Zonie32, I can't see any issue with the Parameters line, and in the example gRegulator is using BETWEEN. I think that part of the query checks out fine. I am sure it is a data/data entry issue.

 

[gRegulator]

Here is an example of when dates are not picked up:

If I type 1/1/2005 as start and 4/1/2005 as end, the numbers are correct.

if I type 4/1/2005 as start and 3/31/2006 as end, the numbers are way off. I would expect this query to return hundreds of results, but it only returns 4!

I appreciate your help!

 

[gRegulator]

Also, I have checked the date entries and I am positive there are no errors. This seems very confusing to me....

 

[gRegulator]

It works as long as the dates selected are on of before todays date. This is weird, but I will leave my code as is. We don't need to calculate stats for the future anyway, so this should be sufficient.

Thanks for your help PC.

Greg

 

[PCLewis]

It's not that I am being pedantic because I really want to help. But when I ask for examples of the dates you enter and an example of a date that you think should have been included and isn't in the result, I want that for a reason.

You see, I have a feeling that the dates entered in the database or the ones you are entering are being misinterpreted.

For example, enter 31/3/2006 as a date. In your normal scheme of m/d/yyyy that date would not make sense. However, VB sees a 31 and thinks that it must be a day because months are 1 to 12. It then sees a 3 and thinks thats a month and so on.

Yes, I like personifying VB and ,y PC - makes me feel like I'm playing a game with an old adversary

So, what are some examples of dates that were not in your results for the second date range? Ideally, write them, and the input criteria in mmm-dd-yyyy format please. In fact, ideally, get Access to do that for you with the format string.

I assure you 150% that Access won't be getting the BETWEEN wrong.