Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Datetime setfilter() 1

Status
Not open for further replies.

ultimategc3

Programmer
Oct 18, 2010
14
EG
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
 
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!)

 
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( )

 
How about something like:
Code:
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"
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top