Solved

Looking for a damn-handy SQL parser in VBA

Posted on 2004-09-23
5
1,108 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
  • 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 65

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 65

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 65

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now