Solved

Create a table based on VBA based SQL

Posted on 2011-09-09
12
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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