It shouldn't crash, but LIKE is text operator, so using against date doesn't make sence.
Increase the memory for FileMaker in FileMaker > Settings , that could help
Can you define the crash? Does FM the app report a crash, if so, can you post the crash log?
Is dateCREATED a date field?
LIKE can be a hog, whether in FM with ExecuteSQL() or even in SQL dbs. and with SQL being case-sensitive in searches, it can be complex. BTW: DO NOT quote numbers when passing as parameters or used as constants in SQL statements.
As for using DATE field, I'd never use LIKE. Use the SQL function YEAR()
WHERE a.\"dateCREATED\" = YEAR(?)
; 2016 // or make this dynamic to be this year with FM functions
// for example: Year ( Get ( CurrentDate ) )
I don't know why it is crashing, but it certainly could be timing out.
Toss out the LIKE.
2 of 2 people found this helpful
It is so easy to crash, also FM15 on Win7 I got
Application: FileMaker Pro.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: exception code c0000005, exception address 000007FEE0673893
And sorry this is Japanese
障害が発生しているアプリケーション名: FileMaker Pro.exe、バージョン: 184.108.40.206、タイム スタンプ: 0x5706f6ea
障害が発生しているモジュール名: DBEngine.dll、バージョン: 220.127.116.11139、タイム スタンプ: 0x5706faf4
I need to find n records that were created on x month of y year so YEAR only goes see far. Would it be
WHERE a.\"dateCREATED\" = YEAR(?) AND a.\"dateCREATED\" = MONTH(?)
Yes! then supply the values for the parameters. Providing the field really is of date type, of course.
Sent from miPhone
if the above suggestions don't work for you, you might try the BETWEEN statement ...
WHERE a.\"dateCREATED\" BETWEEN ? AND ?
then use Date ( MM; 1; YYYY ) and Date ( MM + 1; 0; YYYY )
example: Date(3; 0; 2016) will yield 2/29/2016
and: Date(13; 0; 2016) will yield 12/31/2016
p.s. the zero is for the first day of next month minus one
good catch, okramis!
makes the query as un-indexed search, results slow.
There was(is?) FM specific issue that BETWEEN is very slower than "< AND >" syntax.
1 of 1 people found this helpful
Agreed there! I like to have a text field called yr_mon where I have auto-entered (TEXT):
= Year(mydatefield) & "_" & Right("0" & Month(mydatefield) ; 2 )
it's very sort-able, able to group for charts and in this query would actually BE a text search
WHERE table.yr_mon = '2016_09' // or use parameter (quoted text to match)
no need for SQL functions or the BETWEEN...AND