Android Question Fill Menu With Distinct Sqlite Query Results

[BitsAndBytes]

Sub acActionBarLight_MenuItemClick (Item As ACMenuItem)
   Dim strQuery As String
   Dim cur As Cursor
   Dim value As String
     Dim acPopUp As ACPopupMenu 
     acPopUp.Initialize("acPopUp", dummyPanel)       
     strQuery = "SELECT DISTINCT(DATE) AS DT  FROM TABLE_RECORDS"
     cur =  sqlDB.ExecQuery(strQuery)   
      For i = 0 To cur.RowCount - 1
       cur.Position = i 
       value = cur.GetString("DT")
       acPopUp.AddMenuItem(i, DateTime.GetYear(value), Null)
      Next  
     acPopUp.Show
End Sub

The code above works the problem is that it loads on the menu all the years including those with 2 or more occurencies. How i will load only the distinct values of the query? Thank you!

 

[eps]

What does DATE contain? Do you mean that you are just looking to get the year part of the dates?

Does this work? https://stackoverflow.com/questions/6841230/get-all-unique-years-from-a-date-column-using-sql-mysql

or this? https://stackoverflow.com/questions...nique-months-and-years-from-a-sqlite-database

 

[BitsAndBytes]

This piece code extract the years , the DATE is a database field which stores DATE. My problem is that this code extract all the years as 2014 , 2014 , 2015 2015 , 2017 and not 2014 , 2015, 2017

 

[RauchG]

Create a query to your table.

" strftime('%Y', yourtable.yourdatefield) AS yearsort,"

yearsort shows only 2014, 2015, 2016...

Greetings
RauchG

 

[BitsAndBytes]

Sub acActionBarLight_MenuItemClick (Item As ACMenuItem)
   Dim strQuery As String
   Dim cur As Cursor
   Dim year As String
   Dim list As List 
   list.Initialize
     Dim acPopUp As ACPopupMenu 
     acPopUp.Initialize("acPopUp", dummyPanel)       
     strQuery = "SELECT strftime('%Y', TABLE_RECORDS.DATE) AS YEAR  FROM TABLE_RECORDS WHERE FARM_KEY=?"
     cur =  sqlDB.ExecQuery2(strQuery, Array As String (Module.intContactCode)) 
     For i = 0 To cur.RowCount - 1
       cur.Position = i
       year = cur.GetString("YEAR")
       acPopUp.AddMenuItem(i, year, Null)
     Next
     acPopUp.Show
End Sub

Does not work , if i have 3 records 01/01/2015, 01/01/2016, 01/01/2017 This fills the action menu with 1698,1698,1698

 

[Mahares]

Use the SUBSTR function like this:
SELECT DISTINCT SUBSTR(DATE,7) AS DT FROM TABLE_RECORDS

 

[eps]

SELECT DISTINCT strftime('%Y', startdate) y FROM event

 

[BitsAndBytes]

..again it retuns me 1698 as result ..

 

[eps]

What is the DateFormat you are using?

I use the following in SQL:

CCYY-MM-DD

and define

     DateTime.DateFormat= "yyyy-MM-dd"

 

[Mahares]

Did you try the code I have for you in post #6. It works if your dates are stored as 01/01/2015, 01/01/2016, 01/01/2017

 

[RauchG]

Can you upload your project?

Greetings
RauchG

 

[BitsAndBytes]

I am using dateformat like "dd-MM-yy"

 

[eps]

Yes, but you are storing it in SQLite in an 'invalid' way - well as far as SQLite is concerned... It likes dates to be CCYY-MM-DD.

Sorry!

You might be able to put a View in place to give you the correct date format - this is why strftime doesn't work, your dates are stored as DD/MM/CCYY, it's not expecting that.

Either store them differently or use Mahares' solution.

If you store them 'correctly' - for SQLite you would be able to extract them to DD/MM/CCYY for display purposes.

 

[RauchG]

SQLite does not recognize "dd-MM-yy" as a date. Take "yyyy-MM-dd".

Greetings
RauchG