Translate a query to vba/module

[hyp527]

Hi I have a query that I would like to get it converted to a module or vba but doesn't know how. Can someone help me?

SELECT VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd, (SELECT SpeedoEnd FROM VehicleMiles AS Alias WHERE DorDate = (SELECT Max(DorDate) FROM VehicleMiles AS Alias2 WHERE Alias2.DorDate < VehicleMiles.DorDate AND Alias2.CarNum = VehicleMiles.CarNum) AND Alias.CarNum = VehicleMiles.CarNum) AS PrevEnd, [SpeedoEnd]-[PrevEnd] AS MilesDriven
FROM VehicleMiles
ORDER BY VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd;

Thanks.

 

[JANR]

Allen Browne has an exelent converter here: http://allenbrowne.com/ser-71.html

JR

 

[MSAccessRookie]

Hi I have a query that I would like to get it converted to a module or vba but doesn't know how. Can someone help me?

SELECT VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd, (SELECT SpeedoEnd FROM VehicleMiles AS Alias WHERE DorDate = (SELECT Max(DorDate) FROM VehicleMiles AS Alias2 WHERE Alias2.DorDate < VehicleMiles.DorDate AND Alias2.CarNum = VehicleMiles.CarNum) AND Alias.CarNum = VehicleMiles.CarNum) AS PrevEnd, [SpeedoEnd]-[PrevEnd] AS MilesDriven
FROM VehicleMiles
ORDER BY VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd;

Thanks.

This is a straightforward SQL statement that contains no unusual complications. To make this statement into a procedure, you can just copy it into a module similar to the one below:

    Dim strSQL
 
    strSQL="SELECT VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd, (SELECT SpeedoEnd FROM VehicleMiles AS Alias WHERE DorDate = (SELECT Max(DorDate) FROM VehicleMiles AS Alias2   WHERE Alias2.DorDate < VehicleMiles.DorDate AND Alias2.CarNum = VehicleMiles.CarNum) AND Alias.CarNum = VehicleMiles.CarNum) AS PrevEnd, [SpeedoEnd]-[PrevEnd] AS MilesDriven FROM VehicleMiles ORDER BY VehicleMiles.CarNum, VehicleMiles.DorDate, VehicleMiles.SpeedoEnd;"

As you can see, this gets very messy and is hard to read, so most VBA programmers will break it up for readability sake like this:

    Dim strSQL
 
    strSQL="SELECT VehicleMiles.CarNum, VehicleMiles.DorDate, "
    strSQL=strSQL+"VehicleMiles.SpeedoEnd, (SELECT SpeedoEnd FROM "
    strSQL=strSQL+"VehicleMiles AS Alias WHERE DorDate = (SELECT Max(DorDate) "
    strSQL=strSQL+"FROM VehicleMiles AS Alias2 WHERE Alias2.DorDate < "
    strSQL=strSQL+"VehicleMiles.DorDate AND Alias2.CarNum = VehicleMiles.CarNum)  "
    strSQL=strSQL+"AND Alias.CarNum = VehicleMiles.CarNum) AS PrevEnd, " 
    strSQL=strSQL+"[SpeedoEnd]-[PrevEnd] AS MilesDriven "
    strSQL=strSQL+"FROM VehicleMiles "
    strSQL=strSQL+"ORDER BY VehicleMiles.CarNum, VehicleMiles.DorDate, "
    strSQL=strSQL+"VehicleMiles.SpeedoEnd;"

Note: As JANR points out, Alan Browne's Method is a great thing to know, but it would not be needed in this instance.

 

[hyp527]

Thank you JANR and MSAccess. Visual aids are so great for beginners like me.

Is there another way to rewrite this query to produce faster results?

 

[raskew]

Hi -

JANR pointed you towards an excellent tool for converting from SQL to VBA.

It provides easy to understand, step-by-step, instructions.

Spend a couple of minutes implementing it.

Bob

P.S. Keep in mind, the folks who respond to your requests for assistance spend
more than a 'couple of minutes' doing so.

 

[hyp527]

sorry raskew. i didn't mean to sound unappreciative. i'm very greatful for everyone's help.

I apologize for being so repetitive. I'm very new to access and heard that vba does a better job than building queries.

I used the above but I keep running into problems. Any suggestion would be helpful.

Private Sub Command0_Click() <-- compile error:ambiguous name detected: s_runMe
s_runMe
End Sub

Module has this:
Public Sub s_runMe()
Dim strSQL

strSQL = "SELECT VehicleMiles.CarNum, VehicleMiles.DorDate, "
strSQL = strSQL + "VehicleMiles.SpeedoEnd, (SELECT SpeedoEnd FROM "
strSQL = strSQL + "VehicleMiles AS Alias WHERE DorDate = (SELECT Max(DorDate) "
strSQL = strSQL + "FROM VehicleMiles AS Alias2 WHERE Alias2.DorDate < "
strSQL = strSQL + "VehicleMiles.DorDate AND Alias2.CarNum = VehicleMiles.CarNum) "
strSQL = strSQL + "AND Alias.CarNum = VehicleMiles.CarNum) AS PrevEnd, "
strSQL = strSQL + "[SpeedoEnd]-[PrevEnd] AS MilesDriven "
strSQL = strSQL + "FROM VehicleMiles "
strSQL = strSQL + "ORDER BY VehicleMiles.CarNum, VehicleMiles.DorDate, "
strSQL = strSQL + "VehicleMiles.SpeedoEnd;"

MsgBox "Done!"
End Sub

Thanks.

 

[jal]

I just wanted to add that if you have saved the query under a name such as qryCustomers, you can access it from VBA like this (instead of building the SQL string):

Dim qDef as QueryDef
Set qDef = CurrentDb.queryDefs("qryCustomers")
Msgbox qDef.SQL

And I'm not sure why you think VBA is "better than queries". The JET engine is what runs the query whether from VBA or from the object pane so the performance/speed should be identical. What VBA offers is more flexibility, for example
(1) the ability to alter the queries dynamically according to the user's requests
(2) forms, buttons, textboxes, and other controls for interacting with the user.
(3) programming constructs such as loops for repetitive tasks. These constructs are often slower than queries but are easier for the programmer to work with due to the flexibility.