Solved

Create a table based on VBA based SQL

Posted on 2011-09-09
12
276 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

726 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