Got it, removed the second WHERE clause,
result=SQL.executeQuery("SELECT COUNT(""billto"") FROM ""tblMembers"" WHERE ""billto"" = true AND ""accID_fk"" = '" & iValue & "'" )
Thanks you!
Search found 324 matches
- Thu Sep 15, 2022 5:57 am
- Forum: Macros and UNO API
- Topic: [Solved] Help With embedded sql syntax
- Replies: 3
- Views: 2295
- Thu Sep 15, 2022 5:35 am
- Forum: Macros and UNO API
- Topic: [Solved] Help With embedded sql syntax
- Replies: 3
- Views: 2295
Re: Help With embedded sql syntac
Thanks,
Figured out the quotes, but still stuck with a WHERE problem as there are 2 where conditions.
Figured out the quotes, but still stuck with a WHERE problem as there are 2 where conditions.
- Thu Sep 15, 2022 4:21 am
- Forum: Macros and UNO API
- Topic: [Solved] Help With embedded sql syntax
- Replies: 3
- Views: 2295
[Solved] Help With embedded sql syntax
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 " " ...
- Wed Sep 07, 2022 4:59 pm
- Forum: External Data Sources
- Topic: [Solved] Database Implementation Question
- Replies: 2
- Views: 3174
Re: Database Implementation Question
Thank you !
- Tue Sep 06, 2022 11:29 pm
- Forum: External Data Sources
- Topic: [Solved] Database Implementation Question
- Replies: 2
- Views: 3174
[Solved] Database Implementation Question
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...
- Wed Jul 06, 2022 10:38 pm
- Forum: Macros and UNO API
- Topic: How to disconnect and reconnect from a HSQL back end?
- Replies: 4
- Views: 2907
Re: How to disconnect and reconnect from a HSQL back end?
Yes, checkpoint does not close the connection but I need the password prompt to come up up again
- Wed Jul 06, 2022 7:18 am
- Forum: Macros and UNO API
- Topic: How to disconnect and reconnect from a HSQL back end?
- Replies: 4
- Views: 2907
Re: How to disconnect and reconnect from a HSQL back end?
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.
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.
- Mon Jul 04, 2022 8:45 pm
- Forum: Macros and UNO API
- Topic: How to disconnect and reconnect from a HSQL back end?
- Replies: 4
- Views: 2907
How to disconnect and reconnect from a HSQL back end?
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...
- Sat Jan 15, 2022 9:24 pm
- Forum: Tables & Queries
- Topic: [Solved] Help with SQL statement optimisation
- Replies: 7
- Views: 5030
Re: [Solved] Help with SQL statement optimisation
@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...
- Sat Jan 15, 2022 1:54 am
- Forum: Tables & Queries
- Topic: [Solved] Help with SQL statement optimisation
- Replies: 7
- Views: 5030
Re: Help with SQL statement optimisation
@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 ...
- Fri Jan 14, 2022 3:46 am
- Forum: Tables & Queries
- Topic: [Solved] Help with SQL statement optimisation
- Replies: 7
- Views: 5030
Re: Help with SQL statement optimisation
@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 ...
- Wed Jan 12, 2022 5:13 pm
- Forum: Tables & Queries
- Topic: [Solved] Help with SQL statement optimisation
- Replies: 7
- Views: 5030
[Solved] Help with SQL statement optimisation
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...
- Thu Nov 11, 2021 12:40 am
- Forum: Macros and UNO API
- Topic: [Solved] Base - Setting the value of a form field
- Replies: 3
- Views: 1698
Re: Base - Setting the value of a form field
Thanks gentlemen,
resolved by using a filtertable and listbox instead
resolved by using a filtertable and listbox instead
- Wed Nov 10, 2021 12:37 am
- Forum: Macros and UNO API
- Topic: [Solved] Base - Setting the value of a form field
- Replies: 3
- Views: 1698
[Solved] Base - Setting the value of a form field
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...
- Mon Nov 08, 2021 6:35 am
- Forum: Macros and UNO API
- Topic: [Solved] Base 7.1.5 HSQL2.5 Insert Into statement
- Replies: 2
- Views: 2278
Re: Base 7.1.5 HSQL2.5 Insert Into statement
This is marvellous! Thank you so much. Working great
cheers
Gerhard
cheers
Gerhard
- Sun Oct 31, 2021 9:32 pm
- Forum: Macros and UNO API
- Topic: [Solved] Base 7.1.5 HSQL2.5 Insert Into statement
- Replies: 2
- Views: 2278
[Solved] Base 7.1.5 HSQL2.5 Insert Into statement
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...
- Fri Oct 22, 2021 5:50 am
- Forum: Tables & Queries
- Topic: [Solved] Criteria error
- Replies: 2
- Views: 4218
[Solved] Re: Criteria error
@FJCC
Excellent, thank you- filtering before the join does it
Excellent, thank you- filtering before the join does it
- Fri Oct 22, 2021 3:56 am
- Forum: Tables & Queries
- Topic: [Solved] Criteria error
- Replies: 2
- Views: 4218
[Solved] Criteria error
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...
- Sun Sep 05, 2021 4:17 am
- Forum: Macros and UNO API
- Topic: [Solved] How to adjust connection string to changed location
- Replies: 2
- Views: 2179
Re: How to adjust connection string to changed location
@Villeroy
Thanks for that
Thanks for that
- Sat Sep 04, 2021 11:20 pm
- Forum: Macros and UNO API
- Topic: [Solved] How to adjust connection string to changed location
- Replies: 2
- Views: 2179
[Solved] How to adjust connection string to changed location
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...
- Mon Aug 09, 2021 10:05 pm
- Forum: Tables & Queries
- Topic: [Solved] How to find items that do not exist in a room?
- Replies: 8
- Views: 5598
Re: How to find items that do not exist in a room?
Thanks, got it!
- Mon Aug 09, 2021 9:03 pm
- Forum: Tables & Queries
- Topic: [Solved] How to find items that do not exist in a room?
- Replies: 8
- Views: 5598
Re: How to find items that do not exist in a room?
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...
- Mon Aug 09, 2021 4:09 am
- Forum: Tables & Queries
- Topic: [Solved] How to find items that do not exist in a room?
- Replies: 8
- Views: 5598
Re: How to find items that do not exist in a room?
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...
- Sun Aug 08, 2021 9:32 pm
- Forum: Tables & Queries
- Topic: [Solved] How to find items that do not exist in a room?
- Replies: 8
- Views: 5598
Re: How to find items that do not exist in a room?
@Villeroy
@UnclDonald418
Thanks for the pointers gentlemen, will explore shortly and report back once I get it working
@UnclDonald418
Thanks for the pointers gentlemen, will explore shortly and report back once I get it working
- Sat Aug 07, 2021 9:26 pm
- Forum: Tables & Queries
- Topic: [Solved] How to find items that do not exist in a room?
- Replies: 8
- Views: 5598
[Solved] How to find items that do not exist in a room?
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 ...
- Sun Aug 01, 2021 2:50 pm
- Forum: Macros and UNO API
- Topic: [Solved] SQL error in base update macro
- Replies: 5
- Views: 3771
Re: SQL error in base update macro
Thank you,
also got it to work with a pair of ticks
sUpdate = "UPDATE ""tblInventory"" SET ""sub_fk"" = " & snew & "WHERE ""sub_fk"" = '" & scurr &"'"
also got it to work with a pair of ticks
sUpdate = "UPDATE ""tblInventory"" SET ""sub_fk"" = " & snew & "WHERE ""sub_fk"" = '" & scurr &"'"
- Sun Aug 01, 2021 4:28 am
- Forum: Macros and UNO API
- Topic: [Solved] SQL error in base update macro
- Replies: 5
- Views: 3771
Re: SQL error in base update macro
@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.
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.
- Sat Jul 31, 2021 11:59 pm
- Forum: Macros and UNO API
- Topic: [Solved] SQL error in base update macro
- Replies: 5
- Views: 3771
[Solved] SQL error in base update macro
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...
- Fri Jul 30, 2021 7:34 am
- Forum: Base
- Topic: [Solved] Lookup Source of a View, based on a Query
- Replies: 2
- Views: 2948
Re: Lookup Source of a View, based on a Query
This one works for me on 2.51
SELECT * FROM "INFORMATION_SCHEMA"."VIEWS"
SELECT * FROM "INFORMATION_SCHEMA"."VIEWS"
- Thu Jul 01, 2021 2:58 am
- Forum: Base
- Topic: [Solved] Using "Help URL"
- Replies: 6
- Views: 5247
Re: Using "Help URL"
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, ...