Solved

Create a table based on VBA based SQL

Posted on 2011-09-09
12
274 Views
Last Modified: 2012-05-12
SELECT INTER.TCOMBI, INTER.Service, INTER.Min, INTER.LTL, INTER.Class, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO BTER
FROM INTER
WHERE (((INTER.TCOMBI)=(GetO([Forms]![Point2Point]![ocitytxt] & [Forms]![Point2Point]![oprovcombo])) & (GetO([Forms]![Point2Point]![dcitytxt] & [Forms]![Point2Point]![dprovcombo]))) AND ((INTER.Service)=[Forms]![Point2Point]![servicecombo]))
ORDER BY INTER.Class;

Open in new window


Hi, I have the above code in a query's sql view. For the ease of changing values inbetween, I want to out put the above from a nested if statement; hence changing some values here and there

How do I do this? I am new to combining SQL with VBA :/

Thanks in advance!

Regards,
Shanan
0
Comment
Question by:Shanan212
  • 7
  • 5
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36512521
To turn a basic SELECT query into a Make table query, the syntax will be something like this:

SELECT ...Fld1, Fld2, ...etc INTO NewTable
FROM OriginalTable
WHERE ...
OrderBy ...
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36512628
Yes but how do I execute it?

I ve done further research using this
http://www.databasejournal.com/features/msaccess/article.php/10895_3505836_2/Executing-SQL-Statements-in-VBA-Code.htm

But I am getting sytax errors. Does this means I have to rewrite my SQL? Does this means the SQL in SQL-pane and SQL and VBA are different (by some ways)

Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36512669

Then get rid of the line breaks in the SQL and you can "Run" the query (that creates the table) using this basic syntax:

CurrentDb.Execute "SELECT......INTO......FROM......WHERE......ORDER BY......", dbFailOnError
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36512686
...Note,
...be sure that the form(s) that hold the query's parameters are open when this is run...
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36524452
Hi,

So I have this code from SQL window

SELECT OUT.CITY, OUT.PROVINCE, OUT.[ROUTING TERMINAL], OUT.TCOMBI, OUT.ZONE, OUT.Min, OUT.LTL, OUT.[500], OUT.[1M], OUT.[2M], OUT.[5M], OUT.[10M], OUT.[20M] INTO OB
FROM OUT
WHERE (((OUT.CITY)=[Forms]![Point2Point]![ocitytxt]) AND ((OUT.PROVINCE)=[Forms]![Point2Point]![oprovcombo]));

Open in new window


Now I want it to be run on VBA so that I can change where the sql is getting data from (ie: I want to change the text box sources based on an IF statement.

Would that be like this?

rentDb.Execute "SELECT OUT.CITY, OUT.PROVINCE, OUT.[ROUTING TERMINAL], OUT.TCOMBI, OUT.ZONE, OUT.Min, OUT.LTL, OUT.[500], OUT.[1M], OUT.[2M], OUT.[5M], OUT.[10M], OUT.[20M] INTO OB
FROM OUT
WHERE (((OUT.CITY)=[Forms]![Point2Point]![ocitytxt]) AND ((OUT.PROVINCE)=[Forms]![Point2Point]![oprovcombo]))"

Open in new window


Still giving me syntax errors though :/
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36524586
"Still giving me syntax errors though :/"
What errors?

What is "rentDB"?
(Try it as CurrentDB, if it is the same DB that you are in)
How is it declared?
...etc
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36524631
It is currentDB as it is in the same database. Sorry that was a copying error.

What do you mean by "how it is declared"

The error I am getting now is "Expected End of statment"  

at this point
INTO OB" FROM OUT WHERE

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36524980
<What do you mean by "how it is declared">
Well if it is the currentDB, then use CurrentDB.

If you are going to give it a different name, then it has to be declared...


<I am getting now is "Expected End of statment"  >
<INTO OB" FROM OUT WHERE>
...odd, because in none of the snippets you posted above have that clause...?

Where is that double quote coming form?
Remember what I posted...
A basic Insert will look something like this:
CurrentDb.Execute "SELECT......INTO......FROM......WHERE......ORDER BY......", dbFailOnError


JeffCoachman


0
 
LVL 13

Author Comment

by:Shanan212
ID: 36526016
CurrentDb.Execute "SELECT OUT.CITY, OUT.PROVINCE, OUT.[ROUTING TERMINAL], OUT.TCOMBI, OUT.ZONE, OUT.Min, OUT.LTL, OUT.[500], OUT.[1M], OUT.[2M], OUT.[5M], OUT.[10M], OUT.[20M] INTO NEW1 FROM OUT WHERE (((OUT.CITY)=[Forms]![Point2Point]![ocitytxt]) AND ((OUT.PROVINCE)=[Forms]![Point2Point]![oprovcombo]))"

Open in new window


Ok using your example and some online research I was able to correct that error. Now I am getting 'Too few Parameters. Expected 2'

The main question I have appart from the error is that (I haven't tried it yet) is it possible to put a calculation in the middle of above table?

Eg: (partial coding being this)

(((OUT.CITY)=[Forms]![Point2Point]![ocitytxt])[b] *0.65[/b] 

Open in new window


Thank you for your efforts so far
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 36527035
<'Too few Parameters. Expected 2'>
The form that provides your parameters must be open, and have the parameters available when you run the code.
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36530905
Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36531284
ok
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question