WHERE a.\"SR_Create_Date\" BETWEEN '" & $startdate & "' AND '" & $enddate & "'
the '$startdate' & '$enddate' become LITERAL strings.
the BETWEEN ... AND ... are valid in the WHERE clause
WHERE /* a.\"SR_Create_Date\" >= '$startdate' AND */ a.\"SR_Create_Date\" <= '$enddate'
Hi Beverly ... I was just reading one of your articles trying to figure this out. http://www.filemakerhacks.com/?p=6406
I tried your suggestion but the result was no records. And yes there are records in that date range. Is something off a bit?
The problem seems to be something with the date.
I turned around my original code to...
WHERE a.\"SR_Create_Date\" >= '$startdate'
Which returns nothing. The variable I bumped from -6 to -30 which equates to 9/27/2012 and I have records all the way up to 10/28. I then changed the code from >= to <= and received records. All of the reords. So records on both sides of the variable date.
Hi Paul. I took it out of the query and made the dates into arguments.
Let ( [ $startdate = (Get ( CurrentDate) - DayOfWeek (Get ( CurrentDate )) - 6 ) ; $enddate = Get ( CurrentDate ) - DayOfWeek (Get (CurrentDate )) ; $query = " SELECT a.\"SR_Create_Date\" FROM \"SR\" a WHERE a.\"SR_Create_Date\" BETWEEN ? AND ? ORDER BY a.\"SR_Create_Date\" " ; $result = ExecuteSQL ( $query ; " " ; "" ; $startdate ; $enddate ) ]; $result )
are there any null values in date field?
I have found that can cause problems sometimes so you can add ---
WHERE SR_Create_Date is not null and SR_Create_Date between ? and ?
sorry didn't add escapes so easier to read
That did it, thanks!
Now with most of executesql fields I've built (all 5) I have been able to change it to a count by...
SELECT COUNT (a.\"SR_Create_Date\")
That is not working here. Is it because I'm using a date range?
They were right when they said executesql was powerful! It is giving me a powerful headache. What am I missing Obi-Wan?
no null values. data will be coming from another DB and there should never be any null values either, hopefully!
Do you have other fields/columns in the SELECT clause? if so, you need to include them in an GROUP BY clause.
Go it! I was deleting the dbl quotes at the end of the query. Thanks again.
If you know of any training material I'd appreciate it.