Create a SQL statement in VBA (1 Viewer)

[xzmilan]

Hello,

Now I admit my skills in VBA are novice, but I can learn fast. I know the basics, but this is beyond me right now.

We are trying to make a process more effecient, and we have queries that require us to first get a total dollar amount and then the total number of records for those dollar amounts.

We then need to get a list of the top 50 records, and put those into an excel sheet.

My first goal is to get the total count of records in the query into a summary form without running this query, but based upon the criteria in the query. I want to do this using VBA.

Query SQL Statement bellow

SELECT Daily_file_FL.HI, Sum(Daily_file_FL.[Submitted Amount]) AS [SumOfSubmitted Amount]
FROM Daily_file_FL
GROUP BY Daily_file_FL.HI
ORDER BY Sum(Daily_file_FL.[Submitted Amount]) DESC;

I have a summary form with unbound text boxes that will populate OnOpen, once I have the VBA correct.

I have played around with Dcount from the table Daily_file_FL but I'm not sure if I can use a SQL statement in the criteria section, which would be awesome. I have no experience writing SQL statements in VBA.

All help is apprecieated. If more info needed please let me know!

 

[vbaInet]

Welcome to AWF!

Sounds like you know how to design queries using the query builder. Why are you wanting to build the SQL string using code? From what you've said it seems you know how to achieve this from a query.

 

[Khalid_Afridi]

I have no experience writing SQL statements in VBA.

At-least you should have some experience using VBA in Access then your question can be answered:

do you know how to get recordset using VBA? do you know how to get Recordsets usig DAO or ADO in Ms-access?

Khalid

 

[MSAccessRookie]

Hello,

Now I admit my skills in VBA are novice, but I can learn fast. I know the basics, but this is beyond me right now.

We are trying to make a process more effecient, and we have queries that require us to first get a total dollar amount and then the total number of records for those dollar amounts.

We then need to get a list of the top 50 records, and put those into an excel sheet.

My first goal is to get the total count of records in the query into a summary form without running this query, but based upon the criteria in the query. I want to do this using VBA.

Query SQL Statement bellow

SELECT Daily_file_FL.HI, Sum(Daily_file_FL.[Submitted Amount]) AS [SumOfSubmitted Amount]
FROM Daily_file_FL
GROUP BY Daily_file_FL.HI
ORDER BY Sum(Daily_file_FL.[Submitted Amount]) DESC;

I have a summary form with unbound text boxes that will populate OnOpen, once I have the VBA correct.

I have played around with Dcount from the table Daily_file_FL but I'm not sure if I can use a SQL statement in the criteria section, which would be awesome. I have no experience writing SQL statements in VBA.

All help is apprecieated. If more info needed please let me know!

It looks like you have a successful SQL Query here. What you need to remember is that VBA will require that the query be stored in a String Variable. Surround it by double quotes ( " ) and it should work. To format it for viewing, you can surround each section by double quotes ( " ), followed by an Ampersand and an Underscore ( & _ ). You will need to remember to add any necessary spaces at the beginning (or end) of each line.

StrSQL = "SELECT Daily_file_FL.HI, Sum(Daily_file_FL.[Submitted Amount]) AS [SumOfSubmitted Amount]" & _
        " FROM Daily_file_FL" & _
        " GROUP BY Daily_file_FL.HI" & _
        " ORDER BY Sum(Daily_file_FL.[Submitted Amount]) DESC;"

 

[ghudson]

I will add that printing the SQL to the immediate window will help you when trying to build your string if it does not work as expected.

debug.print StrSQL

 

[gemma-the-husky]

xzmilan

do you realise you can probably do this directly in a visual query, without needing VBA

 

[xzmilan]

Hi Everyone!! Thank you for all the replies. You guy's are awesome!

I do realize that the query works, and that I could design a couple of queries that would fullfill what I needed.

I am using this real world opportunity to learn more about VBA and how to apply it.

If I create a string, Dim myqueryStrng as String, for instance, and make that = to the SQL in double qoutes, then can I use it as criteria in DCount?

Thanks!

 

[vbaInet]

A Dcount takes 3 arguments. In the 2nd argument (which is the Domain) you could use a table or a query. Check the help files for an explanation and google it as well.