Created:
| Last Modified:
Hi,
I am trying to get data from a Access DB via SQL Query.
I configured a filter on the column StudyID. The user should insert the value in a window text box before the query execution and such value is stored in the study_id_selected variable.
It seems that the syntax of the SQL Query filter is not correct because I get the following error:
Below you can find the whole script. Could you tell me which is the correct way to write the SQL filter condition?
Names Default To Here( 1 );
nw = New Window( "study id input box",
<<Modal,
<<Return Result,
Text Box( "study id" ),
study_id = Text Edit Box( " ", <<set width( 400 ) )
);
If( trim(nw["study_id"]) == "", //if input value is empty or the user close the form with the X, then the script exits
(New Window("WARNING",<<Modal,Text Box( "Please insert the study id. Exiting" ));throw()),
study_id_selected = num(nw["study_id"]) );
dt=New SQL Query(
Version( 130 ),
Connection(
"ODBC:DSN=DB001.00.G_FE_JMP15;DBQ=Y:\OFFICIAL_DATABASES\QA_Admin\Stability\DB001.00.G.StabilityStudies_FE_StudiesCompiler_JMP15.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
),
QueryName( "Query_DB001.00.G.StabilityStudies_FE_StudiesCompiler_10Jun2021" ),
Select(
Column( "StudyID", "t1" ),
Column( "TimePointID", "t1" ),
Column( "StabilityClimaticConditions", "t1" ),
Column( "TestMonth", "t1" ),
Column( "ProgramComments", "t1" ),
Column( "Test1", "t1" ),
Column( "Test1Specification", "t1" ),
Column( "Test1Units", "t1" ),
Column( "Test 01", "t1" ),
Column( "intTest1", "t1" ),
Column( "Test2", "t1" ),
Column( "Test2Specification", "t1" ),
Column( "Test2Units", "t1" ),
Column( "Test 02", "t1" ),
Column( "intTest2", "t1" ),
Column( "Test3", "t1" ),
Column( "Test3Specification", "t1" ),
Column( "Test3Units", "t1" ),
Column( "Test 03", "t1" ),
Column( "intTest3", "t1" ),
Column( "Test4", "t1" ),
Column( "Test4Specification", "t1" ),
Column( "Test4Units", "t1" ),
Column( "Test 04", "t1" ),
Column( "intTest4", "t1" ),
Column( "Test5", "t1" ),
Column( "Test5Specification", "t1" ),
Column( "Test5Units", "t1" ),
Column( "Test 05", "t1" ),
Column( "intTest5", "t1" ),
Column( "Test6", "t1" ),
Column( "Test6Specification", "t1" ),
Column( "Test6Units", "t1" ),
Column( "Test 06", "t1" ),
Column( "intTest6", "t1" ),
Column( "Test7", "t1" ),
Column( "Test7Specification", "t1" ),
Column( "Test7Units", "t1" ),
Column( "Test 07", "t1" ),
Column( "intTest7", "t1" ),
Column( "Test8", "t1" ),
Column( "Test8Specification", "t1" ),
Column( "Test8Units", "t1" ),
Column( "Test 08", "t1" ),
Column( "intTest8", "t1" ),
Column( "Test9", "t1" ),
Column( "Test9Specification", "t1" ),
Column( "Test9Units", "t1" ),
Column( "Test 09", "t1" ),
Column( "intTest9", "t1" ),
Column( "Test10", "t1" ),
Column( "Test10Specification", "t1" ),
Column( "Test10Units", "t1" ),
Column( "Test 10", "t1" ),
Column( "intTest10", "t1" ),
Column( "Test11", "t1" ),
Column( "Test11Specification", "t1" ),
Column( "Test11Units", "t1" ),
Column( "Test 11", "t1" ),
Column( "intTest11", "t1" ),
Column( "Test12", "t1" ),
Column( "Test12Specification", "t1" ),
Column( "Test12Units", "t1" ),
Column( "Test 12", "t1" ),
Column( "intTest12", "t1" ),
Column( "Test13", "t1" ),
Column( "Test13Specification", "t1" ),
Column( "Test13Units", "t1" ),
Column( "Test 13", "t1" ),
Column( "intTest13", "t1" ),
Column( "Test14", "t1" ),
Column( "Test14Specification", "t1" ),
Column( "Test14Units", "t1" ),
Column( "Test 14", "t1" ),
Column( "intTest14", "t1" ),
Column( "Test15", "t1" ),
Column( "Test15Specification", "t1" ),
Column( "Test15Units", "t1" ),
Column( "Test 15", "t1" ),
Column( "intTest15", "t1" ),
Column( "Test16", "t1" ),
Column( "Test16Specification", "t1" ),
Column( "Test16Units", "t1" ),
Column( "Test 16", "t1" ),
Column( "intTest16", "t1" ),
Column( "Test17", "t1" ),
Column( "Test17Specification", "t1" ),
Column( "Test17Units", "t1" ),
Column( "Test 17", "t1" ),
Column( "intTest17", "t1" ),
Column( "Test18", "t1" ),
Column( "Test18Specification", "t1" ),
Column( "Test18Units", "t1" ),
Column( "Test 18", "t1" ),
Column( "intTest18", "t1" ),
Column( "Test19", "t1" ),
Column( "Test19Specification", "t1" ),
Column( "Test19Units", "t1" ),
Column( "Test 19", "t1" ),
Column( "intTest19", "t1" ),
Column( "Test20", "t1" ),
Column( "Test20Specification", "t1" ),
Column( "Test20Units", "t1" ),
Column( "Test 20", "t1" ),
Column( "intTest20", "t1" ),
Column( "Test21", "t1" ),
Column( "Test21Specification", "t1" ),
Column( "Test21Units", "t1" ),
Column( "Test 21", "t1" ),
Column( "intTest21", "t1" ),
Column( "Test22", "t1" ),
Column( "Test22Specification", "t1" ),
Column( "Test22Units", "t1" ),
Column( "Test 22", "t1" ),
Column( "intTest22", "t1" ),
Column( "Test23", "t1" ),
Column( "Test23Specification", "t1" ),
Column( "Test23Units", "t1" ),
Column( "Test 23", "t1" ),
Column( "intTest23", "t1" ),
Column( "Test24", "t1" ),
Column( "Test24Specification", "t1" ),
Column( "Test24Units", "t1" ),
Column( "Test 24", "t1" ),
Column( "intTest24", "t1" ),
Column( "Test25", "t1" ),
Column( "Test25Specification", "t1" ),
Column( "Test25Units", "t1" ),
Column( "Test 25", "t1" ),
Column( "intTest25", "t1" ),
Column( "Test26", "t1" ),
Column( "Test26Specification", "t1" ),
Column( "Test26Units", "t1" ),
Column( "Test 26", "t1" ),
Column( "intTest26", "t1" ),
Column( "Test27", "t1" ),
Column( "Test27Specification", "t1" ),
Column( "Test27Units", "t1" ),
Column( "Test 27", "t1" ),
Column( "intTest27", "t1" ),
Column( "Test28", "t1" ),
Column( "Test28Specification", "t1" ),
Column( "Test28Units", "t1" ),
Column( "Test 28", "t1" ),
Column( "intTest28", "t1" ),
Column( "Test29", "t1" ),
Column( "Test29Specification", "t1" ),
Column( "Test29Units", "t1" ),
Column( "Test 29", "t1" ),
Column( "intTest29", "t1" ),
Column( "Test30", "t1" ),
Column( "Test30Specification", "t1" ),
Column( "Test30Units", "t1" ),
Column( "Test 30", "t1" ),
Column( "intTest30", "t1" ),
Column( "Test31", "t1" ),
Column( "Test31Specification", "t1" ),
Column( "Test31Units", "t1" ),
Column( "Test 31", "t1" ),
Column( "intTest31", "t1" ),
Column( "Test32", "t1" ),
Column( "Test32Specification", "t1" ),
Column( "Test32Units", "t1" ),
Column( "Test 32", "t1" ),
Column( "intTest32", "t1" ),
Column( "Test33", "t1" ),
Column( "Test33Specification", "t1" ),
Column( "Test33Units", "t1" ),
Column( "Test 33", "t1" ),
Column( "intTest33", "t1" ),
Column( "Test34", "t1" ),
Column( "Test34Specification", "t1" ),
Column( "Test34Units", "t1" ),
Column( "Test 34", "t1" ),
Column( "intTest34", "t1" ),
Column( "Test35", "t1" ),
Column( "Test35Specification", "t1" ),
Column( "Test35Units", "t1" ),
Column( "Test 35", "t1" ),
Column( "intTest35", "t1" )
),
From( Table( "qry_ResultsUnion01_35", Alias( "t1" ) ) ),
Where(
EQ(
Column( "StudyID", "t1" ),
_AllRows_,
study_id_selected
)
& In List(
Column( "StabilityClimaticConditions", "t1" ),
_AllRows_,
UI(
SelectListFilter(
ListBox,
Base(
"Categorical",
Prompt(
"StabilityClimaticConditions:",
Character,
PromptName( "t1.StabilityClimaticConditions_1" )
)
)
)
)
)
)
) << Run foreground(invisible); //query run
nw = New Window( "Name data table",
<<Modal,
<<Return Result,
Text Box( "What do you want to name the data table?" ),
tableName = Text Edit Box( " ", <<set width( 400 ) )
);
If( nw["button"] == 1 & nw["tableName"] != "", //insert also "Cancel" button -> if pressed script stops
dt << set name( nw["tableName"] );
dt << save as( "Y:\COMMON\QUALITY_ALL_DEPTS\JMP Data Tabels\Temp Folder\" || nw["tableName"] || "_" || Substitute(Substitute(Substitute(MDYHMS( Today() ), "/", ""), ":", ""), " ", "_") || ".jmp");
);Many thanks in advance for your support,
Fabio
