Error Message in SQL Query 1

[gRegulator]

Hi,

I have written a query and for some reason I am getting an error message.

My code is as follows:

PARAMETERS [Forms]![frm_dateparameter_q31]![txtStart] DateTime, [Forms]![frm_dateparameter_q31]![txtEnd] DateTime;
SELECT qry_q31_offence_types.Offence , -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_q31_offence_types
WHERE (((qry_q31_offence_types.Start) Between [Forms]![frm_dateparameter_q31]![txtStart] And [Forms]![frm_dateparameter_q31]![txtEnd]))
GROUP BY qry_q31_offence_types.Offence;

The query has parameters, and if the parameters aren't entered, the query will run, but will be blank. However, if the parameters are filled, i get the following error message:

"This expression is typed incorrectly, or it is too long to be evaluated. Gor example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"



When I compare this to other queries I have written, I can't see any problems, but for some reason I can't get this to work or figure it out. Does anyone know why I may be getting this problem?

Thanks!

 

[PHV]

You may try this:
WHERE qry_q31_offence_types.Start Between [Forms]![frm_dateparameter_q31]![txtStart] And [Forms]![frm_dateparameter_q31]![txtEnd]
Or this:
WHERE qry_q31_offence_types.Start >= [Forms]![frm_dateparameter_q31]![txtStart] AND qry_q31_offence_types.Start <= [Forms]![frm_dateparameter_q31]![txtEnd]

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

 

[gRegulator]

I tried both ways, and I am still getting the same error....

 

[PHV]

What happens without the WHERE clause ?

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

 

[gRegulator]

When I take out the WHERE clause the parameters don't work. Data does show up in the table, however, it is not filtered.

 

[gRegulator]

Without the Parameter clause and with the where clause, it does filter data, however, it is inaccurate.

 

[gRegulator]

I don't know if this will help but here is the query that it is based on:

SELECT 'Offence Against Person' AS Offence, Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Offence Against Person] = True

UNION SELECT 'Offence Against Property', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Offence Against Property] = True

UNION SELECT 'Impaired Driving', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Impaired Driving] = True

UNION SELECT 'Other Criminal Code', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Other CCC] = True

UNION SELECT 'Drug Offence', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Drug Offence] = True

UNION SELECT 'Other Federal Statute', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Other Federal Statute] = True

UNION SELECT 'Liquor Offence', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Liquor Offence] = True

UNION SELECT 'Other Provincial Statute', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Other Provincial Statute] = True

UNION SELECT 'Municipal By-Law Offence', Male, Female, NZ([Probation Start],[CS Start]) as [Start]
From tbl_offenders 
WHERE [Municipal By-Law Offence] = True;

 

[gRegulator]

Does anyone have an idea why this may be happening?

 

[Golom]

You could try forcing the conversion within the SQL rather than as a parameter

WHERE qry_q31_offence_types.Start Between 
      cDate([Forms]![frm_dateparameter_q31]![txtStart])
      And 
      cDate([Forms]![frm_dateparameter_q31]![txtEnd])

 

[gRegulator]

Hey Golom, Thanks for your help!

I was really confused on this one, but your suggestion straightened it right out!

Have a great day!