Formula/SQL Optimisation options for a form or report

[Hayton]

Hi Guys,
I need some advice.

Scenario 1: I have a textbox on a form which has this formula as its control source '=DateSerial(Year([txtBegindate]),Month([txtBegindate])+1,Day([txtBegindate])-1)'.

My question is from a performance perspective would be better to leave the formula as is or convert it to an SQL staement.

Scenario 2: I have a report that contains in excess of a hundred formulas similar to '=nz((DCount("[FailRsn]","qry1Data","[FailRsn] = 'Inner liner seperation'")))'.

The same question applies here in order to optimize the report performance does one leave as is or convert it to sql type statement.

Could some one please write an sql statement for the above. I am not a programer so please excuse me for my request.

Kind Regards

Hayton McGregor

 

[lameid]

In Scenario 2, if at all possible then yes, putting the data in a query will be much faster. The catch with writing the SQL or making the query in design view for that matter is understanding the tables and how they are related. Without that information noone will be able to help.

As for Scenario 1. It would probably work better in the query. The thing is txtBegindate is probably a control. In order to do the same thing in a query you would have to replace txtBegindate with whatever the control source is. Then you would just add a column to the query with the entire expression without the equal sign (=). Noteworthy about queries is that you can give an expression a name or rename a column by using an alias. To use an alias type the alias (new field name) followed by a colon ) followed by the field or expression being aliased.

Also regarding Scenario 1 I do not think it does what you expect as far as all cases. Try making sure txtBeginDate is December sometime, i.e. 12/15/2007 and see if you get what you expect. I think what you want is to do is add a month so that December 2007 becomes January 2008 and subtract a day. Check out the dateadd function if that is what you are really after.