0

I am very new to SQL and think I have a simple problem but was unable to figure it out from other posts. I have the following code:

INSERT INTO tblShortScores ( TradeNum, FilterNum, Rank, ScoreNum )
SELECT [Forms]![frmOpenTrades]![TradeNum] AS TradeNum, tblFilters.FilterNum, tblFilters.SBBExh AS Rank, tblFilters.SBBExh AS Score
FROM tblFilters
WHERE (((tblFilters.SBBExh) Is Not Null));

but instead of using the literal "SBBExh" in tblFilters.SBBExh, I want to do something like

tblFilters.("S" & [Forms]![frmOpenTrades]![Strategy])

where something like
[Forms]![frmOpenTrades]![Strategy] contains the value "BBExh".
It's in MS Access and I seem unable to find a syntax that works

any help is appreciated

June7
  • 19,874
  • 8
  • 24
  • 34
Mike Moeller
  • 7
  • 2
  • It sounds like your tblFilters table has multiple columns like Strategy01Rank, Strategy01Score, Strategy02Rank, Strategy02Score (or similar). If so, then your table is "pivoted" and will be awkward to work with. Search for advice on how to normalize your data into something where different strategies are stored in separate rows, not separate columns. – Gord Thompson Mar 26 '21 at 22:35
  • How can SBBexh be used for two fields? Can't dynamically build field name in query object. Use VBA to construct and run action SQL. – June7 Mar 26 '21 at 22:41
  • it is "pivoted". I dont have StrategyRank01 and StrategyScore01, they are the same and now that i think about it I dont need to query the Rank, it can stay in the parent table. But I do have multiple ranks like 01, 02, etc. Its easier to understand and adjust the strategies when you can visualize that way. Sorry, I should have only used SBBExh once – Mike Moeller Mar 26 '21 at 22:54
  • can you clarify how to use VBA to create the variable and then run it in SQL? that sounds like what I am trying to do – Mike Moeller Mar 26 '21 at 22:57
  • Did I understand correctly, you want to dynamically build a field name? Exactly what is contained in [Forms]![frmOpenTrades]![Strategy]? – June7 Mar 26 '21 at 23:20
  • [Forms]![frmOpenTrades]![Strategy] contains a text string. "BBExh" in this example. I want to concatenate an "S" to the front so i can query the "SBBExh" field in another table – Mike Moeller Mar 27 '21 at 16:20

1 Answers1

1

Can't dynamically build field name in query object. Use VBA to construct and execute action SQL, like:

strField = "S" & Me.Strategy
CurrentDb.Execute "INSERT INTO tblShortScores (TradeNum, FilterNum, ScoreNum) " & _
    "SELECT " & Me.TradeNum & " AS TradeNum, FilterNum, " & strField & " " & _
    "FROM tblFilters WHERE " & strField & " Is Not Null;"

Assumes TradeNum is number type - if it is text, use apostrophe delimiters:
SELECT '" & Me.TradeNum & "' AS .

If SQL injection is a concern review, How do I use parameters in VBA in the different contexts in Microsoft Access?

June7
  • 19,874
  • 8
  • 24
  • 34