Parameter Based on Form Field

[mderby]

Hi,

I have created a parameter query and below is the SQL statement:

PARAMETERS BD DateTime, ED DateTime;
SELECT TM0050.Description, TM0002.[Actual Hour], TM0002.BU, TM0002.Country, TM0002.[Task Owner],

IIf(IsNull([Forms]![frmDataSelection]![txtSdate]),"01/01/03",[Forms]![frmDataSelection]![txtSdate]) AS BD,
IIf(IsNull(BD),DateAdd("Y",1,BD),[Forms]![frmDataSelection]![txtEdate]) AS ED

FROM TM0002 INNER JOIN TM0050 ON TM0002.TYPS = TM0050.TYPS
WHERE (((TM0002.Country)<>"All countries" And (TM0002.Country) Is Not Null) AND (TM0002.[Task Start Date]) Between [BD] And [ED]));

But when I run the query it will prompt the parameter box. I thought that it will reference the field in the frmDataSelection form.

Please highlight to me which part of the SQL is wrong.

Thanks

 

[Fuga]

Is your form open?

Fuga.

 

[mderby]

Yep the form is still open.

 

[Rich]

You have to enclose literal dates with pounds not quotes #01/01/03#
PARAMETERS Forms!frmDataSelection!txtSdate DateTime etc

 

[mderby]

Thanks all.

what if I replace the #01/01/03# to DATE(). I have tried that and it will still prompt for parameter for BD and ED.

I was just wondering whether this is the correct way to handle a parameter in a SQL statement in a query.


mderby

 

[Rich]

please read the previous response again