Ask Your Question

In base Convert Query to macro

asked 2017-12-26 22:19:43 +0200

davidsharpe gravatar image

updated 2017-12-27 05:56:21 +0200

Jim K gravatar image

My PC is current.

  • Windows 10 Home (x64) Version 1607 (build 14393.1066)
  • Install Language: English (United States)
  • System Locale: English (United States)
  • Installed: 4/21/2017 3:18:25 PM
  • Servicing Branch: Current Branch (CB)
  • Boot Mode: UEFI with successful Secure Boot

My LibreOffice is is current.

  • Version: (x64)
  • Build ID: 2524958677847fb3bb44820e40380acbe820f960
  • CPU threads: 4; OS: Windows 6.19; UI render: default;
  • Locale: en-US (en_US); Calc: group

I have been doing a good deal of looking through access2base and I have effectively activated it.

Sub DBOpen(Optional poEvent As Object)
If GlobalScope.BasicLibraries.hasByName("Access2Base") Then
End If
Call Application.OpenConnection(ThisDatabaseDocument)
End Sub

I will be incrementing through a local table (My_Dates) and querying a second table (diaries) based on year, month and day of each record. In the samples provided I am just using one set of variables (year=2017, month=12 and day=5).

I have a query that works (returns the correct data).

SELECT MAX( "diaries"."steps" ) AS "steps" 
FROM "diaries" 
WHERE "diaries"."year" = '2017' 
AND "diaries"."month" = '12' 
AND "diaries"."day" = '5'


I'm attempting to implement the above simple select query into a macro, where the selected MAX gets put into a local variable (lSteps2Day).

I have apparently gotten the punctuation and concatenation correct (it doesn't generate an error anymore).

dim strAYear  as string
strAYear = "2017"
dim strAMonth as string
strAMonth ="12"
dim staADay   as string 
strADay = "5"

dim strSql as string
dim lSteps2Day as long

strSql = "SELECT MAX( 'diaries'.'steps' ) " &_
    " FROM 'diaries'" &_
    " WHERE 'diaries'.'year' = 'strAYear' " &_
    " AND 'diaries'.'month' = 'strAMonth' "&_
    " AND 'diaries'.'day' = 'strADay' "

I now need to call strSql and assign the MAX returned to my lSteps2Day.

Any guidance would be appreciated.

Thanks Dave

edit retag flag offensive close merge delete



Was there a reason in DELETING your last question on TT NorthWind.odb 'org.hsqldb.jdbcDriver' could not be loaded? The answer took effort and could be of help to others. Did not even get any response back from you on it being a help or not. Makes me wonder about providing further answers.

Ratslinger gravatar imageRatslinger ( 2017-12-26 23:20:43 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-12-27 02:36:59 +0200

Ratslinger gravatar image

updated 2017-12-27 06:56:37 +0200


Here is basic code for running SQL query in macro - click here.

Result sets (SQL return) depend upon data - see this post.

Here is code for SQL updating of a table - click here with the difference from the query being oStatement.executeUpdate( sSQL ).


This is more how your SQL need to be in the macro:

strSql = "SELECT MAX( ""diaries"".""steps"" ) FROM ""diaries"" WHERE ""diaries"".""year"" = '" & strAYear & "' AND ""diaries"".""month"" = '" & strAMonth & "' AND ""diaries"".""day"" = '" & strADay & "'"
edit flag offensive delete link more


Very informative post. Several years I was experimenting with passing data between Base and SQL but couldn't really get it to work. I went with a Firefox/PHP/Maria.db solution. So it's a bit late for me.

Steve R. gravatar imageSteve R. ( 2017-12-27 04:04:02 +0200 )edit

Thanks for the comment. It goes beyond this. It has proven helpful in standalone forms, Base to Base table copies and data transfer to all other modules. Just gotta use some imagination & research.

Ratslinger gravatar imageRatslinger ( 2017-12-27 04:34:48 +0200 )edit

answered 2017-12-27 23:03:53 +0200

davidsharpe gravatar image

Ratslinger - Regarding your "Was there a reason in DELETING your last question " I regret any inconvenience that I caused; it was unintentional. I didn't see it (I thought I would be notified when your post was made) when you posted it. When I saw it I'd sort of given up on attempting to use it and I didn't want any one else to work on it on my behalf. I deleted it to prevent others from working on something I wouldn't use. If you'd like me to, it looks like I may have the capability to reactivate it. And again I hope you feel less of me for how I acted in this matter


edit flag offensive delete link more


@davidsharpe Please reactivate if you can I was looking at it.

peterwt gravatar imagepeterwt ( 2017-12-27 23:23:14 +0200 )edit

As @peterwt has stated, yes, please undelete. The answer applies to anyone wanting to use TT Northwind. It can save some headaches.

Ratslinger gravatar imageRatslinger ( 2017-12-27 23:26:15 +0200 )edit

@davidsharpe FYI email notification has not been working for some time. You must sign in regularly to check for messages.

Ratslinger gravatar imageRatslinger ( 2017-12-27 23:27:49 +0200 )edit

Have reposted answer as a new question.

Ratslinger gravatar imageRatslinger ( 2017-12-28 00:00:18 +0200 )edit

answered 2017-12-29 12:01:18 +0200

JPL gravatar image


to format an SQL statement in Basic, please read THIS. You will see that you might use - only if you're using Access2Base - square brackets to surround table and field names instead of double quotes (that have to be doubled again due to their presence within a Basic string).

To get the results of the statement in your program, typically read THIS . The OpenRecordset method may receive your SQL as statement and GetRows will load the whole set at once in a Basic variant array.

Hoping this might help.


edit flag offensive delete link more


Thank you JPL . The links do provide me some very helpful information Dave

davidsharpe gravatar imagedavidsharpe ( 2017-12-30 03:11:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-26 22:19:43 +0200

Seen: 351 times

Last updated: Dec 29 '17