Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

In base Convert Query to macro

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: 5.4.4.2 (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 GlobalScope.BasicLibraries.loadLibrary("Access2Base") 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 any more)

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

click to hide/show revision 2
None

In base Convert Query to macro

My PC is current current.

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

    ++++++++++++++++++++++

++++++++++++++++++++++
My LibreOffice is is current current.

  • Version: 5.4.4.2 (x64) (x64)
  • Build ID: 2524958677847fb3bb44820e40380acbe820f960 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 it.

Sub DBOpen(Optional poEvent As Object)
 If GlobalScope.BasicLibraries.hasByName("Access2Base") Then
     GlobalScope.BasicLibraries.loadLibrary("Access2Base")
 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, (year=2017, month=12 and day=5 )
day=5).

++++++++++++++++++++


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

data).

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

'5'

++++++++++++++++++++++

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

+++++++++++++++++++


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

anymore).

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

"5" dim strSql as string dim lSteps2Day as long

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 strSql and assign the "MAX" MAX returned to my lSteps2Day lSteps2Day.

+++++++++++++++++


Any guidance would be appreciatedappreciated.

Thanks Dave