how to convert query to vb code (1 Viewer)

[sandanet]

Hello everybody,

I'm looking for your help to convert query to vb code, please kindly look at the attached database.

Thank you in advance,

 

[Ranman256]

you shouldnt, queries are better.
but,
in the query, click the design,SQL button
copy sql
put in vb code
sql = "select * from table"

 

[Uncle Gizmo]

See Colin's Sample dB here:-

SQL to VBA and back again

 

[sandanet]

you shouldnt, queries are better.
but,
in the query, click the design,SQL button
copy sql
put in vb code
sql = "select * from table"

thank you for replay, when i do that i get error when i copy this sql code

sql = "SELECT Format(Int((108120-[Value])/3600),"00") AS Total FROM tbl1 WHERE (((tbl1.Property)="100"));"

i don't know why??

 

[Uncle Gizmo]

Have a look at this excellent video from Phillip should sort out most of your formatting problems there...

VBA SQL Strings - Tutorial for Beginner

 

[sandanet]

Mr. Uncle Gizmo thank you so much ,, i already tried using this tool but i got an error when i put the syntax in vb

 

[sandanet]

sorry guyz, but i couldn't do it correctly
the attached file is my implementation

 

[isladogs]

You can't use double quotes (") in SQL statements.
You either need to use single quotes (') or double double quotes ("")

So for example, your query should probably be

sql = "SELECT Format(Int((108120-[Value])/3600),'00') AS Total FROM tbl1 WHERE (((tbl1.Property)='100'));"

I say probably as I haven't tested the section Format .... AS.

I would also recommend my SQL to VBA & back again utility as a way of learning how to do such conversions. Its easy & it works!

To convert query SQL to VBA, do the following:
a) Change your query from design view to SQL view. Copy it to the clipboard
b) Open the utility. Click the SQL tab and paste in the query SQL
c) Click the VBA tab and the converted VBA code will be shown.
You can then copy this into a VBA procedure

 

[sandanet]

You can't use double quotes (") in SQL statements.
You either need to use single quotes (') or double double quotes ("")

So for example, your query should probably be

sql = "SELECT Format(Int((108120-[Value])/3600),'00') AS Total FROM tbl1 WHERE (((tbl1.Property)='100'));"

I say probably as I haven't tested the section Format .... AS.

I would also recommend my SQL to VBA & back again utility as a way of learning how to do such conversions. Its easy & it works!

To convert query SQL to VBA, do the following:
a) Change your query from design view to SQL view. Copy it to the clipboard
b) Open the utility. Click the SQL tab and paste in the query SQL
c) Click the VBA tab and the converted VBA code will be shown.
You can then copy this into a VBA procedure

i did that and i got this

DoCmd.RunSQL " SELECT Format(Int((108120-[Value])/3600),""00"") AS Total "  & _
" FROM tbl1 "  & _
" WHERE (((tbl1.Property)=""100""));"

now how can i show the result on the form in the unbound textbox ?

 

[isladogs]

i did that and i got this

DoCmd.RunSQL " SELECT Format(Int((108120-[Value])/3600),""00"") AS Total "  & _
" FROM tbl1 "  & _
" WHERE (((tbl1.Property)=""100""));"

now how can i show the result on the form in the unbound textbox ?

What you asked for was VBA code to enter into a procedure.
That's what you've got above

If you want to enter the result of a query into a form textbox, you wouldn't do that.
Instead do one of the following:

a) create a form with your query q1 as the record source.
Then add a BOUND textbox with control source = Total field from query
OR
b) create a textbox on your form and set the control source to:

=DLookUp("Total","q1")

OR
c) create an UNBOUND textbox - I'll call it Text0
Then add code to your form_load event

Private Sub Form_Load()

    Me.Text0.Value = DLookup("Total", "q1")
        
End Sub

OR
d) create a label (Label0) and set its caption in the Form_Load event

Private Sub Form_Load()

    Me.Label0.Caption = DLookup("Total", "q1")
        
End Sub

I've done all 4 on a form so you can compare & decide which you want to use.
All work but there is a noticeable delay using method b)

 

[sandanet]

thank you so much

 

[Ranman256]

you cannot use dbl-quotes inside dbl-quotes:
sql = "SELECT Format(Int((108120-[Value])/3600),'00') AS Total FROM tbl1 WHERE (((tbl1.Property)='100'));"

 

[Uncle Gizmo]

you cannot use dbl-quotes inside dbl-quotes:
sql = "SELECT Format(Int((108120-[Value])/3600),'00') AS Total FROM tbl1 WHERE (((tbl1.Property)='100'));"

I was thinking the same Ranman and I had to read Collins thread twice to realise that he actually put in double double quotes... So you can't use them individually but you can use them doubled up...

Also Philip shows exactly the same in his excellent YouTube video here:-

Have a look at this excellent video from Phillip should sort out most of your formatting problems there...

VBA SQL Strings - Tutorial for Beginner

 

[isladogs]

Hi Ranman / Tony

If you look back to post #8, I also used single quotes:

sql = "SELECT Format(Int((108120-[Value])/3600),'00') AS Total FROM tbl1 WHERE (((tbl1.Property)='100'));"

However, when I created my utility based on a similar idea by Allen Browne, I decided to retain his convention of using double double quotes (rather than convert to single quotes) as it solved various issues during conversion.
Harder to read but it always works.

It can get even more complex...
a) triple double quotes (""") at the end of a text string

=DLookup("City", "Customers", "CompanyName = ""MendipDataSystems""")

b) use quadruple double quotes ("""") where you need it to be read as ""

=DLookup("City", "Customers", "CompanyName = """ & [CompanyName] & """")

Yes you could use Chr(34) but in my view that's even harder to read...

Have a look at this article on Allen's site: Quotation marks within quotes

I'm also increasingly using Phillip's site as an excellent reference.
See: http://codekabinett.com/index.php?Lang=2

Unlike Allen he is still very much actively working in Access.
He is also a member of this site with the user name sonic8

 

[Uncle Gizmo]

An approach I use where possible is to avoid the issue of building complicated SQL statements. In other words SQL statements with quotes and delimiters in them. Statements built like that are very hard to read and understand particularly for the uninitiated...

My approach is to pass variables into the SQL statement, variables - which have had the delimiters and quote marks added. You end up with a simple SQL Statement which is easy to read, without any clutter of delimiters and quote marks...


Example:-

Public Function fINSERT_AgeData(ByVal lngRptID As Long, ByVal lngChkID As Long, ByVal lngGrpID As Long, ByVal lngSetsID As Long, ByVal strAgeRangeDesc As String, ByVal lngAgeRangeCount As Long, ByVal strAgeRangeDate As String)

Dim strSQL0 As String
Dim strInsert As String
Dim strValues As String

'Example
'How to Format Text Data
strAgeRangeDesc = Chr(34) & strAgeRangeDesc & Chr(34)
'How to Format Date Data 1
strAgeRangeDate = Chr(35) & strAgeRangeDate & Chr(35)


strInsert = "INSERT INTO tblReporting (ReportID, CheckListID, GrpID, SetsID, ItemsText, ItemsCount, ReportDate) "
strValues = "VALUES (" & lngRptID & ", " & lngChkID & ", " & lngGrpID & ", " & lngSetsID & ", " & strAgeRangeDesc & ", " & lngAgeRangeCount & ", " & strAgeRangeDate & ");"

            strSQL0 = strInsert & strValues
            CurrentDb.Execute strSQL0

 

[isladogs]

Hi Tony

I often do the same as you - building up lengthy SQL in sections. For example

strSQL = strSELECT & strWHERE & strORDERBY & ";"

As well as being easier to read/edit, it also has the advantage of solving the
'too many line breaks' issue

 

[Uncle Gizmo]

As well as being easier to read/edit, it also has the advantage of solving the
'too many line breaks' issue

Agreed!

Yes Colin, it all boils down to the basic rule of programming, that is, to build your program out of simple, small, easily debugged and managed components.

To my mind using line continuation "& _" violates that principle and I avoid it where possible.

I assign each line of my SQL statement to a string variable.

I have a template file in which I fill in the SQL statements:-

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim strSQL6 As String
Dim strSQL7 As String
Dim strSQL8 As String
Dim strSQL9 As String

strSQL1 = ""
strSQL2 = ""
strSQL3 = ""
strSQL4 = ""
strSQL5 = ""
strSQL6 = ""
strSQL7 = ""
strSQL8 = ""
strSQL9 = ""

strSQL0 = strSQL1 & strSQL2 & strSQL3 & strSQL4 & strSQL5 & strSQL6 & strSQL7 & strSQL8 & strSQL9

fSiteBy_PO = strSQL0

The advantage of this approach is as you get in to more advanced programming where you are manipulating SQL Statements, then you can easily build If Statements Case statements etc out of these basic component strings. You just couldn't do that if the string was divided up with the line continuation marks.

 

[Uncle Gizmo]

You'll never believe it but I've actually done a YouTube video on this! (I'm practicing my irony as Captain Carrot would say)

Advanced Search 7 - Nifty Access

In this video frame you can see a Case Statement where I use this principle. The different cases are created from the individual SQL units concatenated together in different patterns. It's easy to read, easy to maintain, even easy to copy and use somewhere else.

 

[isladogs]

Talking of templates, as you may know I've been creating automated function for transforming JSON files into Access tables.

To do this I use a text file as a template, read that into Access then modify individual sections using a special script which is generated automatically for each file

Hard to explain but perhaps slightly simpler with an example

This is a section of a script file

strSCase = String(12, " ") & "Dim strBase As String, dteDate As Date" & vbCrLf & vbCrLf & _
            String(12, " ") & "|get common values" & vbCrLf & _
            String(12, " ") & "strBase = result('base')" & vbCrLf & _
            String(12, " ") & "dteDate = result('date')" & vbCrLf & vbCrLf & _
            String(12, " ") & "|get string in array" & vbCrLf & _
            String(12, " ") & "lngStart = InStr(1, strJSON, '''rates''') + 9" & vbCrLf & _
            String(12, " ") & "lngEnd = InStr(lngStart, strJSON, '}')" & vbCrLf & _
            String(12, " ") & "strTemp = Mid(strJSON, lngStart, lngEnd - lngStart)" & vbCrLf & _
            String(12, " ") & "|parse string in array" & vbCrLf & _
            String(12, " ") & "arrFieldsValues = Split(strTemp, ',')" & vbCrLf & vbCrLf & _
            String(12, " ") & "For Each varitem In arrFieldsValues" & vbCrLf & _
            String(16, " ") & "varitem = Replace(varitem, '''', '')" & vbCrLf & _
            String(16, " ") & "arrTemp = Split(varitem, ':')" & vbCrLf & vbCrLf & _
            String(16, " ") & ".AddNew" & vbCrLf & _
            String(16, " ") & "!Currency = arrTemp(0)" & vbCrLf & _
            String(16, " ") & "!Rate = arrTemp(1)" & vbCrLf & _
            String(16, " ") & "!Base = strBase" & vbCrLf & _
            String(16, " ") & "!Date = dteDate" & vbCrLf & _
            String(16, " ") & ".Update" & vbCrLf & _
            String(12, " ") & "Next"

As you can see, I'm happy to use "& vbCrLf & _"

When run, this becomes the section between the 2 lines '------------

'code for table 1 - tblCurrencyExchange
    Set rst = db.OpenRecordset("tblCurrencyExchange", dbOpenDynaset, dbSeeChanges)
    With rst
        For Each result In JSON("result")
            '-------------------------------------
            Dim strBase As String, dteDate As Date

            'get common values
            strBase = result("base")
            dteDate = result("date")

            'get string in array
            lngStart = InStr(1, strJSON, """rates""") + 9
            lngEnd = InStr(lngStart, strJSON, "}")
            strTemp = Mid(strJSON, lngStart, lngEnd - lngStart)
            'parse string in array
            arrFieldsValues = Split(strTemp, ",")

            For Each varitem In arrFieldsValues
                varitem = Replace(varitem, """", "")
                arrTemp = Split(varitem, ":")

                .AddNew
                !Currency = arrTemp(0)
                !Rate = arrTemp(1)
                !Base = strBase
                !Date = dteDate
                .Update
            Next
            '-------------------------------------

        Next
        .Close
    End With

Horrendously complex to get it correct but it now works beautifully....

 

[Uncle Gizmo]

What does the "String(12, " ")" and 16 do?