Solved

Looking for a damn-handy SQL parser in VBA

Posted on 2004-09-23
5
1,202 Views
Last Modified: 2008-02-01
Does anyone have any code from a prior project that edits SQL statements?  
------
I have a graph that's table-driven, where one of the fields is the SQL string.  An example is...

SELECT tbl_time.txt_time_month_year AS [Month], tbl_indicators_data.sng_idata_value_1 AS [ALCOA]
FROM tbl_time INNER JOIN tbl_indicators_data ON tbl_time.lng_id = tbl_indicators_data.lng_month_id
WHERE tbl_indicators_data.lng_indicator_id = 32
ORDER BY tbl_time.lng_month_ID ASC;

The above SQL displays a graph with all values.  

I have a business need to allow the user to edit the above to allow FROM and TO dates, like (note the WHERE-BETWEEN CLAUSE)...

SELECT tbl_time.txt_time_month_year AS [Month], tbl_indicators_data.sng_idata_value_1 AS [ALCOA]
FROM tbl_time INNER JOIN tbl_indicators_data ON tbl_time.lng_id = tbl_indicators_data.lng_month_id
WHERE tbl_indicators_data.lng_indicator_id = 32 AND tbl_time.lng_month_ID BETWEEN 200201 AND 200312
ORDER BY tbl_time.lng_month_ID ASC;

I can hack the big components of SQL okay, but getting the 'AND tbl_time.lng_month_ID BETWEEN 200201 AND 200312' clause is proving to be a pain.

Points given generously for anything I can build on.  

TIA -Jim
0
Comment
Question by:Jim Horn
[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
  • 3
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12133859
Can't you get the user to input the from and to from a form and format the result accordingly, i.e.:
BETWEEN Format(Forms!MyForm!txtFrom,"yyyymm") AND Format(Forms!MyForm!txtTo,"yyyymm")

?
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 12133931
(1)  I have my dates formatted as longs in yyyymm format, since this is accounting summary stuff and everything is a month.
(2)  In SQL code I'm reading the data, populating FROM and TO combobox.rowsources, and displaying the first and last months as the values.

Pretty damn slick if I do say so myself.

My problem is, every time I alter the SQL I need to read the existing SQL, determine if there's an existing BETWEEN clause (somewhat tough), if so wipe it out, and write a replacement (fairly easy).  

Perhaps I'll include the BETWEEN clause in every SQL statement, even if it's redundant, just to make user changes like this easier.

0
 
LVL 66

Author Comment

by:Jim Horn
ID: 12136458
Resolved it by re-writing the entire WHERE clause every time, instead of trying to hack multiple WHERE critera.  

Wow.  <sniff>  You were the only one that cared...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12136484
You should have asked an easier question, like "How do I make my form go beep"...:-)

Still not entirely sure why you're dynamically change the WHERE clause though - there must be an easier way?
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 12136500
Don't tempt me... if there's a mad push for points, I can ask all sorts of silly questions...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

696 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