Datetime setfilter() 1

[ultimategc3]

Hello All...

I've a problem filtering the datetime column, i've tried many trials:

li_cur = ddlb_filtercol.finditem(ddlb_filtercol.text,0)
ls_col_type = dw_1.Describe(ls_column_name+".ColType")
If ls_col_type = "datetime" then
ls_column_name = Trim(dw_1.Describe("#"+string(li_cur)+".Name"))
ls_match_data = String(ddlb_data.text)
// the problem exists here:
ls_filter = ls_column_name + ddlb_op.text + "~"" + ls_match_data + "~""

dw_1.setfilter(ls_filter)
dw_1.filter()

// Extra information
// column name = "ilc_dat"
// ls_filter = ilc_dat="10/16/2010 11:25:23"
// DATAWINDOW TYPE MISMATCH


Thank You

 

[mbalent]

What is the return from the setfilter?

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[ultimategc3]

The Return is :

ilc_dat = "10/16/2010 11:25:23"


where ilc_dat is the column name with datetime datatype


//Messagebox("Datawindow","Types of Expressions must match",Exclamation!)

 

[mbalent]

Have you tried without the quotes around the datetime value?

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[ultimategc3]

When I remove the quotes The Return is :

dw_1.setfilter(ilc_dat = 10/16/2010 11:25:23)


//Messagebox("Datawindow","Types of Expressions must match",Exclamation!)

The full script Here after modification:

string ls_quote, ls_column_name, ls_match_data, ls_filter, ls_col_type
Integer li_cur

//ddlb_filtercol contains all column names
li_cur = ddlb_filtercol.finditem(ddlb_filtercol.text,0)
ls_column_name = dw_1.Describe("#"+string(li_cur)+".Name")

ls_col_type = dw_1.Describe(ls_column_name+".ColType")

//where ddlb_op returns Arithmetic Operator. ex "="

If ls_col_type = "number" or ls_col_type = "decimal" then
ls_quote = ""
ls_column_name = Trim(ls_column_name)
ls_match_data = ddlb_data.text
ls_filter = ls_column_name + ddlb_op.text + ls_quote + ls_match_data + ls_quote
Else
If ls_col_type = "datetime" then
ls_quote = "~""
ls_column_name = Trim(ls_column_name)
ls_match_data = String(ddlb_data.text)
ls_filter = ls_column_name + ddlb_op.text + ls_match_data
// I've tried this method to setfilter but there is no return
// ls_filter = "Year( ls_column_name ) = 2010 AND Month( ls_column_name ) = 1"
Else
If left(ls_col_type,4) = "char" then
ls_quote = "'"
ls_column_name = "upper("+Trim(ls_column_name)+")"
ls_match_data = Upper(ddlb_data.text)
ls_filter = ls_column_name + ddlb_op.text + ls_quote + ls_match_data + ls_quote
End if
End if
End if

// All works fine except the "datetime" coltype

dw_1.Setfilter(ls_filter)
dw_1.filter( )

 

[mbalent]

How about something like:

ls_filter = "string(" + ls_column_name + ", 'mm/dd/yyyy hh:mm:ss)' + ddlb_op.text + ls_quote + ls_match_data + ls_quote

Basically you are converting the datetime column data to a string of a specified format.

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[ultimategc3]

Thank you...

you've give me the right way...

In addition, i've just arranged the quotes...

ls_filter = "string(" + ls_column_name + ", 'mm/dd/yyyy hh:mm:ss') " + ddlb_op.text + ls_quote + ls_match_data + ls_quote