Need help with dates in RecordQuery (MBS)

Products, Plugins & FM hosting/cloud services MBS Plugins
1

I hope this is the right place to ask this question. If not just delete this post.
I´m using the "FM.InsertRecordQuery" function from the MBS-Plugin to fetch some data from 1 Filemaker-Table to another one.
This is the query
"SELECT \"pr_gruppe\",\"artnr\",\"aname\",\"aname2\",\"einheit\",\"hkl\",\"herkunft\",\"leergut\",\"gr_nr\",\"inhalt\",\"per\",\"preis_ko\",\"preis_me\",\"preis_anbr\",\"tagespreis\",\"hat_info\","&$$param_kdnr&" as \"bkdnr\" FROM SHOPPRL where \"pr_gruppe\"="&$$param_prgrupp&" AND "&$$param_date&" >= gueltig_von AND "&$$param_date&" <= gueltig_bis"
It does work if i omit the last part with $$param_date. Otherwise i get this error:
[MBS] ERROR: FQL0001/(1:218): There is an error in the syntax of the query.
I have tried changing several things but i cant figure out what to do.
What i try to do is selecting records that have a valid price. It is valid if $$param_date is BETWEEN gueltig_von and gueltig_bis. They are in date-format. (dd.mm.yyyy).

Any help would be greatly appreciated

Wibenji

2

not sure, if $$Vars are working in the ‘select’

as for parameters: Why not use the filemaker executeSQL function with parameters? There, the conversion of dates might be easier.
(MBS sql also allows external parameters)

executeSQL
(
“SELECT field1, field2, field3 FROM TheTable WHERE xx=? AND yy=?”;
“”;
“”;
myTO::thisField; myOtherTO::thatField
)

3 Likes
3

Thank you so much Markus. External Parameters solved my problem. Somehow the $$Vars don´t work when they are dates. I have put them into the external parameters now and it works fine. You saved my day

2 Likes
4

Using external parameters solves many problems.

I have seen this form fail:

executeSQL( “SELECT field FROM table WHERE x=” & $VAR ;“”;“” )

while this form is successful:

executeSQL( “SELECT field FROM table WHERE x=?” ;“”;“”; $VAR )
5

With FM.InsertRecordQuery, you can also use parameters there.

e.g.
MBS( "FM.InsertRecordQuery"; ""; "Test"; "FirstName¶LastName¶CompanyName"; ""; "SELECT "First", "Last", "Company" FROM Contacts WHERE City=?"; "Hamburg" )

6

That is actually what i´m using now Christian.

1 Like