Is current_table the name of a field or the value you want to find?
Is table1 the table you want to query for this data or the table on which your layout is based and that has the value you want to search for?
My best guess is that you should set it up this way:
ExecuteSQL ("SELECT \"field1\" FROM \"table1\" WHERE \"field1\" = ? " ;"";"" ; Table 2::Field )
Where you layout is based on Table 2 and the value in the current record of Table 2 that you want to search for is found in Table 2::Field.
And if there is any chance that there is more than one record in Table1 that will match Table 2::field, you may need to also add a record separator instead of "".
excuse my my fault, i just edited my post.
so its a field from a table in which layout i use this function.
yes, table1 is another table i want to query from.
Thanx that worked!!! :)
But why is it not accepting the fieldname at the "?" position? and how/why does it work at the end?
There are two problems with putting the table and field name in place of the question mark in your query:
Current_Table::FieldName is incorrect syntax for a qualified field reference. You'd use Current_Table.FieldName.
But even if you use Current_Table.FieldName, it won't work as expected as the Query does not "know" which record in Current_Table is your current record and thus won't retrieve the correct value. I suspect that the lack of context will generate a syntax error as no part of the first part of the query refers to any table except Table1.
In other DB systems, I've often had to do "text surgery" where I inserted a value from a field or variable into the middle of a text expression in order for it to be part of the WHERE clause. At least in ExecuteSQL, the ? option makes that a bit simpler.
ah now i get the problem.
i was thinking i can use the regular fm table::field synthax as placeholder to get this field, but when using executesql( i am allready in sql mode and there is no :: synthax anmore.
So that means the ? shifts it outside the boundry where it can be read?
But why is an variable instead not working?
SQL can't evaluate a variable any more than it can evaluate Table::Field.
Note: there is an alternative approach but it's more complicated than using the ? method. I'm showing it here to illustrate the point that I am making:
ExecuteSQL ("SELECT \"field1\" FROM \"table1\" WHERE \"field1\" = " & current_table::field2 ;"";"")
By taking Current_Table::field2 out side of the quoted string, it will be evaluated by FileMaker and the value inserted into the query. This kind of "String surgery" is often used in many other systems that use SQL to query a database. In ExecuteSQL, we have the option of using ? which can be a lot simpler in many queries.