Need help with select where date.

[#1]

I am creating a subset where I am using Select Where :Login Date == 01/18/2019;  I cannot get it to work.  I also tried opening the subset and running the query builder.  I can add all of the fields and then filter by login date.  The login dates show in the filter.  If I choose one of the dates and hit update, no records show.  Below is my code and I have attached the screen shot of the query builder.  Any help would be appreciated.

Regards,

Wayne

[#2]

JMP Date values are numeric values.  If you wanted to compare a value in "Login Date" to a date value, you either need to convert the value of "Login Date" to a character string and compre it to "01/07/2018", or keep it as a numeric and convert "01/07/2018" to a date time numeric value.  The script below does the selection using the former methodolgy

Column( "Login Date" ) << data type( numeric ) << Format( "m/d/y" );
MyList = {"HCDS", "HCDS;LENNY", "HCDS; WACKER", "HCDS; GOLDEN SAMPLE", "HCDS; ALJ", "HCDS; LENNY C",
"HCDS; WACKER; GOLDEN SAMPLE", "HCDS; WACKER; PRE-MOVE"};
dt6 << Select where( (Contains( MyList, :Site ID )) & :Analysis == "G4" );
dt6 << Select where( format(Column( "Login Date" )[Row()], "M/D/Y" ) == "01/07/2018", current selection( "extend" ) );
dt7 = dt6 << Subset( Output Table( "Aloha Lenny Assay Subset" ), Selected Rows( 1 ), Selected Columns( 0 ) );
dt7 << select where( Is Missing( :Label ID ) ) << delete rows;

selrows = dt7 << get rows where( :Formatted Entry == "ND" );
dt7:Formatted Entry[selRows] = "0";
dt7:Formatted Entry << data type( Numeric );

[#3]

Jim,

I tried your suggestion and still the data did not filter.  Below is what I used.

Regards,

Wayne

[#4]

Here is a Select Where example, in the same structure as your noted Select Where.  It works.  So I believe you need to possibly use the JSL Debugger to step through your code and find out where the issue(s) is/are.

names default to here(1);
dt= open("$SAMPLE_DATA/Aircraft Incidents.jmp");
MyList = {"Accident", "Incident" };
dt << Select where( (Contains( MyList, :Investigation Type) & :Location == "Eden, UT"));
dt << Select where( format(Column( "Event Date" )[Row()], "M/D/Y" ) == "01/05/2001", current selection("extend"));

I checked out the code back to JMP 11.  The "current selection("extend")", was added in JMP 12, so the JSL does not work properly in JMP 11, but it does work after that release. 

[#5]

Jim,

I am using 14.1.  I have tried so many different ways to format my dates and have not figured out the right format.  Below is more of my code.  I am passing a variable from a date box.  My excel import Login Date looks like 

1/2/2019 9:11

 Please take a look if I am passing the ALstartDT correctly and any recommendation on how to properly format the date to get the select to work.  

Thank you, 

Wayne

Clear Log();

Names Default to Here (1);

runScript = Expr(
TempstartDT = start << get;
TempendDT = end << get;
ALstartDT = Uppercase(Format(TempstartDT, "mm/dd/yyyy"));
ALendDT = Uppercase(Format(TempendDT, "mm/dd/yyyy"));

);

// Written by Vince Faller
// Makes a calendar come up on a numeditbox (defaults to now)
// Note, this only works on JMP 11 or above
dateBox = Function(
{timething = Today()},
{newbox},
newbox = Eval(
Substitute(
Name Expr(
Number Edit Box(
timething, // Eval(Sub()) because of JMP quirk
10, // Just need this because JMP doesn't default a width
<<Set function(
Function( {self},
{},
If( Is Missing( self << get ),
self << Set( Eval( DUMMY ) ) // Sets the date to the timething argument
)
)
),
<< Set Format(Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar)
)
),
Expr( DUMMY ), timething
)
);
newbox;
);

nw =
New Window( "SQL Data Table Extraction",
LineUpBox(NCol(2),
TextBox("Start DateTime"), start = dateBox(Today()- InDays(12)),
TextBox("End DateTime"), end = dateBox(),
ButtonBox("Cancel", ButtonPressed = "Cancel"), ButtonBox("Get Data", ButtonPressed = "Get Data") //stores users button press input into variable

));

wait (15);
//If statements to take action based on which button was pressed on the date prompt
If ( ButtonPressed == "Cancel", nw << Close Window);
If ( ButtonPressed == "Cancel", Stop());
if ( ButtonPressed == "Get Data", runScript);
If ( ButtonPressed == "Get Data", runScript2);
If ( ButtonPressed == "Get Data", nw << Close Window);

Caption("Query is running");
wait(0);
Names Default to Here (1);

Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
);

);
//Column ("Login Date") << data type (numeric) << format("m/d/y");


dt6 << Select where (:Login Date >= ALstartDT,current selection("extend"));

dt7 << select where(Is Missing (:Label ID)) << delete rows;

selrows=dt7 << get rows where (:Formatted Entry == "ND");
dt7:Formatted Entry[selRows]="0";
dt7:Formatted Entry << data type(Numeric);

[#6]

Because you have set your variable "ALstartDT" to a string

ALstartDT = Uppercase( Format( TempstartDT, "mm/dd/yyyy" ) );

(The Format() function returns a string value)

You then need to convert your ":Login Date" to a string also....the ":Login Date" I am assuming is a JMP Date Column.

dt6 << Select where( Format(:Login Date[row()], "mm/dd/yyyy") >= ALstartDT, current selection( "extend" ) );

[#7]

Jim,

When I try to convert :Login Date to a string, I recieve the error that argument should be numeric{1} in access or evaluation of 'Format', Format/*###*/:Login Date [Row()], "mm/dd/yyyy")

Regards,

Wayne

[#8]

Oops, my error.  The [Row()] is the problem.  Use

dt << select where( Format( :Login Date, "mm/dd/yyyy" ) <= ALstartDT );

[#9]

Jim,

I still get argument should be numeric{1} in access or evaluation of 'Format' , Format/*###*/(:Login Date, "mm/dd/yyyy")

[#10]

I am pretty sure the issue is a mismatch in whether or not the column is character vs. the comparison variable is character.  Can you attach a sample data table?