Howdy everyone,

I am trying to tackle a new Execute SQL calc field and getting the love ?. Things look right to me but the data viewer says I have an error in the syntax of my query. I thought maybe some trained eyes could spot this really quickly.

Two tables are involved here. The first where the calculation field resides is Properties. The table being queried is AIRGMS. In both Properties and AIRGMS I have a matching field pCode. The other fields involved in AIRGMS are From Date and Number Of Nights.

What I am trying to do is run a 30/60/90 day occupancy rate both forward and back from any given day. So to do occupancy rate not on a set month I needed to sum Number of Nights rented and divide that by the number of days between what ever start end dates. I used a custom function to set variables for those two dates.

This is what my query looks like for the Plus 30 calc field, so today to one month from today.

Let (

[ $pCode = Properties::pCode;

$vtoday = Get ( CurrentDate );

$vplus30 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate ) ; Get ( CurrentDate ) ; 1 ); ///Custom Fxn

$vplus60 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate ) ; Get ( CurrentDate ) ; 2 );

$vplus90 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate ) ; Get ( CurrentDate ) ; 3 );

$vminus30 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate ) ; Get ( CurrentDate ) ; -1 );

$vminus60 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate ) ; Get ( CurrentDate ) ; -2 );

$vminus90 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate ) ; Get ( CurrentDate ) ; -3 );

$vdays = $vplus30- $vtoday;

$query =

"SELECT Sum(\”Number Of Nights\”) FROM AIRGMS

WHERE (pCode = ?)

And

(\”From Date\” >= ?)

And

(\”From Date\” <= ?)";

$result = ExecuteSQL ( $query; "" ; "" ; $pCode ; $vtoday ; $vplus30)

]; $result

)

Now once this is fixed to return the sum of nights rented l need to divide by $vdays. I assume I can just add /$vdays right after $result for the calculation to work?

Thanks for any help you can provide!

Replacing your smart/curly quotes in your calc in the new file resulted in actual values in the calc field, so I am pretty sure that is it.