Got it, removed the second WHERE clause,

result=SQL.executeQuery("SELECT COUNT(""billto"") FROM ""tblMembers"" WHERE ""billto"" = true AND ""accID_fk"" = '" & iValue & "'" )

Thanks you!

Thanks,
Figured out the quotes, but still stuck with a WHERE problem as there are 2 where conditions.

Hi All, Having trouble with some validation macro receiving a syntax error message. Can not figure out whats incorrect or missing. Sub validateBilling oForm = ThisComponent.DrawPage.Forms.getByName("frmMain") iValue = oForm.Columns.getByName("groupid").Value msgbox " " ...

Thank you !

Not sure which subforum this should go under ? Win 10, Lo 7.3.2 HSQL 2.61 Hi, Just as a matter of curiosity take the following situation. Small time social or sportsclub member database with the treasurer, the president,the membership administrator possibly needing concurrent access. However being s...

Yes, checkpoint does not close the connection but I need the password prompt to come up up again

Hi Sliderule and thank you.

Well, I have a form which auto closes at x seconds and SHUTDOWN COMPACT terminates the connection. So I am looking for a way to reconnect without having to close and reopen the base file.

WIN10, LO7.3.7.3, Hsql 2.51 Hi, Not sure if this post should go to Base or here? A SHUTDOWN/COMPACT will disconnect the back end and base is still open. With base still being open how can one reconnect ie prompt for id and pw by way of a macro? Calling the original Setup macro contained in the Split...

@eremmel Thanks for the hint on the one line statement, am almost there. Here is the revised code which seems to work well that is no more errors with the SQL. Just need to fix the invalid value error in the declarations, grr SUB todate(oEvent AS OBJECT) DIM oForm AS OBJECT DIM oFeld AS OBJECT DIM i...

@eremmel Thank you again and wow just by applying suggested indexes the original code s runtime from 20 secs+ has been reducd to 5 secs. had a try with both alternative codesamples but ended up with a expected case error possibly due to not double quotting or maybe HSQL nit supporting Q1: Will work ...

@eremmel Thank you so much for that, will put into action tonight. Silly me forgetting all about the indexing. Thought about a view instead but couldn't figure it out. Must admit SQL is not my forte. Normally I find my way around but tend to get stuck with the complexity of using aliases for tables ...

WIN10, LO7.2.5.2 (x64) HSQL 2.5.1 Hi all, Am in the process of adapting a sample db to a multi purpose rental database. All coming together nicely, however I am experiencing extremely slow execution time of a couple of queries used as source for listboxes in frmbookings. The first one is run via mac...

Thanks gentlemen,
resolved by using a filtertable and listbox instead

Screen Shot 11-09-21 at 07.32 PM.PNG LO7.1.2 HSQL 2.51 Win10 Hi, Have seen several examples of how to get the value of a form field and then setting the value in another forms field. My question is when I move to a new record in one form can I place a field value of another form in the new record b...

This is marvellous! Thank you so much. Working great

cheers
Gerhard

Hi all, I am composing a macro to add extra records to a calendar table. All working well except for the INSERT INTO at the end, which will need to convert the ldate variable to a date. Tried To_Date and FORMAT but don’t seem to figure out the correct syntax. Appreciate any pointers Thanks Sub addDa...

@FJCC

Excellent, thank you- filtering before the join does it

Hello, going in circles with a basic query definition. In a query I would like to show on which dates a client is booked and on which days the client is still available. There is something wrong in my query because it does not display 1/10 for Black , likewise if the filter is set to Smith then 7/10...

@Villeroy
Thanks for that

Hi, I am using NSIS to install front/HSQL backend dbs. The connection string of the original db is jdbc:hsqldb:file:///C:\LDS\icms\database/icms;default_schema=true;shutdown=true;hsqldb.default_table_ The code setup of the split wizard normally adjust the path if the db is moved (Code as below) 'set...

Thanks, got it!

Hello Chris, Thank you for your suggestion and code. The db is about facilities - asset management, so the room designation helps to narrow down search results Is Null as single criteria will list kitchens, toilets, store rooms etc.. which is not desirable. It does work with Not 13 or <>13 as a thir...

Hi All, Got the query working with <> , did not like null SELECT "tblCategory"."ctid" "cat_pk", "tblCategory"."caregory" "Category", "tblInventory"."sub_fk", "tblItem"."item" "Item", "t...

@Villeroy
@UnclDonald418

Thanks for the pointers gentlemen, will explore shortly and report back once I get it working

Hi, Have several rooms of different designation, which contain several items, some rooms may have the same items. Can figure out how to get a list of rooms which have chairs, but where I am stuck is how to find those classrooms that do not have chairs. Have seen something similar achieved by way of ...

Thank you,

also got it to work with a pair of ticks


sUpdate = "UPDATE ""tblInventory"" SET ""sub_fk"" = " & snew & "WHERE ""sub_fk"" = '" & scurr &"'"

@Villeroy

Thanks for that, yet I still get the ... word message. It does not seem to like the second variable because if I substitute with a number it works.

Hi, I am stuck with variable not defined or insufficient rights. LO 7.1.2 HSQL 2.51 on Win10 Cant figure the error in the statement trying to run an update using 2 variables. Sub moveItem dim oForm1 as object dim scurr msgbox "Selected Item will be transferred to the location chosen" oForm...

This one works for me on 2.51

SELECT * FROM "INFORMATION_SCHEMA"."VIEWS"

Indeed there is next to nothing on the topic. All I found was this, https://ask.libreoffice.org/en/question/57719/reassigning-a-dynamic-url-for-a-forms-push-button/#57940 which looks like some html solution, (never got the download to open the sample db of the author. However if familiar with html, ...