Table to Display range of Days

H

harry5568

Member
Hi..Can somebody tell me the code for table to display say 7 days data prior to selected value on calendar.

Table1.Filter("Dt= '" & Date(DateAdd(Cal.Value,0,0,-7)) To Date(Cal.Value) & "'")
is not working..Thx..Harry
PS; Dt is column name
 
H

harry5568

Member
I also tried:
Table1.Filter("Dt= '" & Date(DateAdd(Cal.Value,0,0,-1 To -7)) & "'")

They both return an error:
"The Expression Contains an Invalid String Constant: '10-Feb-2010"
 
klaus

klaus

Expert
Licensed User
Longtime User
As your dates are strings you cannot make the test.
I suggest you to add an invisible column with the ticks of the dates and work with numbers.

B4X: 
[FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][/LEFT]
Sub [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]Globals
[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]'Declare the global variables here.
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]  CalDate0=[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]  CalDate1=[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]End Sub
 
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Sub [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]App_Start
[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]  DateFormat[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"mm/dd/yyyy"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Table1.AddCol([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]cNumber[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"DateTicks"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Table1.AddCol([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]cString[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Date"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]100[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Table1.AddCol([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]cString[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Test"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]20[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Table1.AddRow([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]DateParse[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"02/01/2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]),[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"01.02.2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"1"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Table1.AddRow([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]DateParse[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"01/28/2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]),[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"28.01.2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"2"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Table1.AddRow([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]DateParse[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"01/25/2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]),[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"25.01.2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"3"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Table1.AddRow([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]DateParse[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"01/21/2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]),[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"21.01.2010"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"4"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
  Form1.Show
[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]End Sub
 
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Sub [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]btnFilter_Click
  Filter=[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"DateTicks > "[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]&CalDate0&[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]" AND DateTicks <= "[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]&CalDate1
  Table1.Filter(Filter)
[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]End Sub
 
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Sub [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]Calendar1_ValueChanged
  CalDate1=Calendar1.Value
  CalDate0=[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]DateAdd[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2](CalDate1,[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],-[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]7[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])
[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]End Sub[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

Attached a small text program. You should perhaps need to adapt the date format.

Best regards.​
 

Attachments

  • TableFilterDate.sbp
    1.2 KB · Views: 278
H

harry5568

Member
Thanx Klaus,

Table1.Filter("DateTicks>"& DateAdd(Calendar1.Value,0,0,-7)&" AND DateTicks<="&Calendar1.Value)

works fine for me..
the calendar format needn't be changed..dd/mm/yy,mm/dd/yy,dd-mmm-yyyy and other formats all return the same DateTicks.

My .dat file contains data as per Dates. I want to add a DateTicks column to each respective row for the above filter to work.Can you please tell me how to do that? Thanx..Harry
 
klaus

klaus

Expert
Licensed User
Longtime User
Table1.Filter("DateTicks>"& DateAdd(Calendar1.Value,0,0,-7)&" AND DateTicks<="&Calendar1.Value)
Click to expand...
works of course too, I am used to use variables.
Your code is more compact, less easy to follow.

I suggested to look at the date format thinking of the format your dates are stored.

How many lines does your DAT file have? According to the number of lines the solution would be more or less complex or 'boring'.
Is it a text file or a database file.
For just a few lines, converting 'manually by hand' would be the easiest way.
For many lines it is probably more useful to write a small program to convert the file, or just add a temporary converting routine in your program.

Best regards.
 
H

harry5568

Member
Thanx Klaus,
My database is a .dat file with item in each line separated by commas,the first item in each line being date in dd-mm-yy format . I have approx 600 lines like this.
I'm actually looking at a temporary converting routine for the moment I need this Filter to work,otherwise database size will increase with a DateTick added to each line!!
Thanx so much fr all yr help Klaus..Harry
 
H

harry5568

Member
Hi Klaus,
I devised a temporary converting routine which adds an extra column while loading the csv and then fittting in the ticks corresponding to Dates in this column,and then let the filter work.After the filter's job is over,the column is deleted.This maintains the db size as original.But,this substantially slows down the processing speed!!
So,adding an extra column into the db was preferred.It does increase the db size a little,but does not affect the processing speed much.
Juss fr yr info...
Thx and Rgds..Harry
 
You must log in or register to reply here.
Top