1 of 1 people found this helpful
You may need to escape-quote the field
underscore shouldn't be used in field & table names that will be used with SQL (or other "external" methods).
however we have a work-around for ExecuteSQL() function:
This may help you debug your eSQL:
EvaluationError( ... your calc here ... ) // returns error code
and/or this if you have FileMaker Pro Advanced (to use the Data Viewer):
Thank you Beverly,
i think i need some more work as it doesn't work yet. i have noticed that the reference fields only seem to work with number fields? i have combination between text and numbers for my primary keys. could that be a problem? i have looked at the error codes. code is either 8909 or 8910.
i have tried the example from filemaker and that works so it is not my settings.
cheers and thank you for your help.
kpToursPricesTable) = TourPrice0441
Is the "value" you are trying to match this combination of text and numbers or is this another field?
Generally you use the ? parameter
_kp_ToursPricesTable) = ?
then in the parameters:
"TourPrice0441" for the value or
TourPrice0441 for the field (which probably should have the table name
Magic with the question mark I get the value I was looking for. This works however when there is one value not a range of values with different dates. Now I can try to refine the formula.
what I was thinking of is to add the following:
after the where statement
and SS_travel_date (this is the date I need the price for)
between ToursPricesTable::StartDate and ToursPricesTable::EndDate (every tour price has a range where the date is valid)
all date fields are formatted as date field.
somehow this is not working again.
Thank you so much for your help Beverly.
repost you query (as changed) if you need further assistance!