INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA to parse SQL into [FieldName] and [Source]

VBA to parse SQL into [FieldName] and [Source]

(OP)
Just wondering if there is some pre-built code to handle a querydef and parse out the SQL into [FieldName] and [Source]?

For instance, given the SQL of:

CODE

SELECT tblTable1.WidgetID, tblTable1.WidgetName, IIF(ISNULL(tblTable1.WidgetPrice),2.99, tblTable1.WidgetPrice) AS Price, tblTable1.WidgetReOrderQty AS OrderAmt, (Price * OrderAmt) AS WidgetPricePerOrder
FROM tblTable1; 

It would produce something along the lines of:

CODE

QueryField		Source
WidgetID		tblTable1.WidgetID
WidgeName		tblTable1.WidgetName
Price			IIF(ISNULL(tblTable1.WidgetPrice),2.99, tblTable1.WidgetPrice)
OrderAmt		tblTable1.WidgetReOrderQty
WidgetPricePerOrder	(Price * OrderAmt) 

Obviously the all fields selector (i.e., "*") would be passing a bunch of fields that we wouldn't be able to parse except for by opening the query and accessing the fields, but I think for these purposes I don't think I would need it, as the source and query field would be the same (that is, the field name in the query would be the same in the table, so it doesn't matter to me). Mostly I want to snag the fields that are constructed on the fly. It doesn't have to do it in any particular way... array, dumping to a table via DAO... just so long as I can get at the list.

I'm hoping this has been done already, as parsing for commas will be problematic given their presence in various formulas (vba function argument lists, IIF() statements, etc.).

Thanks for any suggestions.

RE: VBA to parse SQL into [FieldName] and [Source]

Hi,

Check out the Split() function. You'll need to parse in steps.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA to parse SQL into [FieldName] and [Source]

I doubt you could split this very easily and reliably. I think I would try to use the DAO object model. Obviously getting all the field names is trivial. The ones that are not aliased would have the same source as the name and I would not worry about them, because that would be column 2 for those fields. Determing if the Alias and field name are not the same is done by comparing the field.name to the field.sourcefield property. That would give you a list of those fields that are aliased as a starting point. Then I think I would write the code only for those aliased fields. Search for each of those field names in the the string and try to determine the expression. There is an "expression" property of the fields collection, but it does not same to return the expected expression for the calculated fields. You would think somewhere in the dao model you can find IIF(ISNULL(tblTable1.WidgetPrice),2.99, tblTable1.WidgetPrice)" for the field Price, but I did not see anywhere.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close