List box in data sheet style form

[maxi]

Anyone out there can help me. I am at my wits end and cannot see what to do next. I converted successfully from MS access a patient database which is in forms in datasheet view . There is only one table that searches the patient usually by a listbox of the days of the week and/or which GP. In MS access I could apply filter form and click on list box of day and/or gp.
I tried creating a listbox in a datasheet style form right clicking on column and replace with listbox, right click again and chose column and in data tab clicked properties list box and tried several different sql statements ( I am definately an amateur at sql

Select Day, Name, PPS Number, Phone No. Condition, GP, start date, review date,
from WKLY
where day = "mon"
or day = "tues"
or day = "wed"
or day + "thurs"
or day = "fri"

also tried the where and distinct statements, also tried in, and operators

Also created a macro : from search control to a form from tutorial in NeoWiki,
Sub SearchByDay

dim ofilter as object
dim oformWKLY as object

oformWKLY = ThisComponent.Drawpage.Forms.getByName ("Standard")
oFilter = oFormWKLY.getByName ("DaySearchListBox")[/color]if
oFilter.CurrentValue <> "" then
oFormWKLY.Filter = "Day LIKE " + "'"+oFilter.CurrnetValue+"'"
oFormWKLY.ApplyFilter = True
else
oFormWKLY.ApplyFilter = False
end if

oFormWKLY.Reload
End Sub

I think the line in red is wrong for my form??

Probably something very simple but as clear as mud now to me. Would it be be better to create 3tables 1. with days of the week 2. list of patient details and 3. for GPs names? and then convert them to form. It is a simple database I created in ms access using form wizard that allows us to prepare certs in advance for days of week cert is due and which GP signs.

I have managed to get the listbox to show the arrow and side or to click down but with no data in it.

Hoping someone out there can give me a clue to what I am doing!!

[Villeroy]

Assuming a HSQL database (see status bar):
http://hsqldb.org/doc/guide/ch09.html#stored-section

DAYOFWEEK(date)

returns the day of the week (1 means Sunday)

Code: Select all

SELECT .... WHERE DAYOFWEEK("some date") BETWEEN 2 AND 6 

Short hint on form editing:
Open your form in edit-mode. It is a Writer document opened in read-only mode normally, but for write-access in edit mode. A document has a collection of forms with one or more members. Each form can have linked subforms and controls.
You see the whole hierarchy in the "form navigator" (do not confuse with the "normal" navigator for the document). Hit the 5th button of toolbar "Form Design" in order to get the form navigator.
The form navigator allows you to select single controls easily (even grouped ones) and it shows the hierarchy of all objects (even the invisible, abstract forms).
If you don't feel comfortable with macros, you may simply use the built-in filter tools on the navigation toolbar or try a macro-less combo box filter I outlined recently in a example document: http://user.services.openoffice.org/en/ ... 281#p47281

[maxi]

Thanks Villeroy for your reply and all the info.

Been trying but still getting error. Decided to stay away from macros and decided to filter I have the list box in although the listbox is appearing in the day field in the form before I filter and the days of the week in all the rows are missing. When I go to filter by form the listbox is there and I can click on the day to filter but then I get an error "data content could not be updated". I used the following statement:

SELECT `DAY`, `NAME`, `PPS NUMBER`, `PHONE No`, `CONDITION`, `GP`, `START DATE`, `REVIEW DATE` FROM `WEEKLY ` WHERE 'DAYOFWEEK( ''2007-09-01'' ) BETWEEN 2 AND 6 '

I know I am missing the big picture here and I know it must be so simple. Struggled and getting totally confused

[Villeroy]

WHERE 'DAYOFWEEK( ''2007-09-01'' ) BETWEEN 2 AND 6

This filters by day numbers from all dates in a column named "2007-09-01".

Is this ...

Code: Select all

WHERE ("Date_Field" BETWEEN '2007-09-01' AND '2007-10-01')AND (DAYOFWEEK("Date_Field") BETWEEN 2 AND 6)

... what you want?
I'd prefer this one:

Code: Select all

WHERE (Year("Date_Field")=2007)AND(Month("Date_Field"=9) AND (DAYOFWEEK("Date_Field") BETWEEN 2 AND 6)