Using a form data field as a input to a query

[mikem]

I am trying to use the selection from the Strategy field in the form below to populate an append query. I have searched for the correct syntax for my query but can't find it.

on my main form, I select one of 5 different strategies. in this example "BBSqz".



I want to run an append query based on the Strategy and tie it to the main form with the TradeNum on the query. The Tradenum part works fine. I run the append query from the Filter table to another table that is the control for the SubForms on the right side of my main form.





I want to filter the SBBSqz column where values are not Null. The query would then append the records to the table and would show up in the subforms on the right side of main form like in the image above.

here is my query. (edit: I have a criteria for Is Not Null to only filter for values in the SBBSqz field)


when I just type "SBBSqz" in the rank column, the query runs fine. But I need the "S" added to the field result of "BBSqz". I assume my concatenation is incorrect but was unable to find the correct syntax.

any help is appreciated
thanks
mike

[Welshgasman]

That syntax should work, providing the form is open?
I have just tested this, and it works fine, however it appended all 28 rows from the form?, so you might need criteria? if you expect only the current record.

INSERT INTO Table1 ( Field1, Field2 )
SELECT 27 AS Expr1, "S" & [Forms]![Form1]![Field2] AS Expr2
FROM Table1;

[mikem]

I'm closer to knowing what I need but not how to get it.

when I don't have tblFilters in the table row of the query, I get this which has SBBExh and looks like what I want, except I want that field from the tblFilters



but when I say I want to get that from the tblFilters like below, I get an error

[mikem]

here is what I have

INSERT INTO tblShortScores ( TradeNum, FilterNum, Rank )SELECT [Forms]![frmOpenTrades]![TradeNum] AS TradeNum, tblFilters.FilterNum, "S" & [Forms]![frmOpenTrades]![Strategy] AS Rank
FROM tblFilters
WHERE ((("S" & [Forms]![frmOpenTrades]![Strategy]) Is Not Null));

the form is open. I'm able to get the TradeNum from it

[mikem]

I think mine looks like yours. What am I missing?

[mikem]

to put my query into english (I'm new to SQL)
I want to get the values from the SBBSqz field and put them into the Rank filed of another table
but I want to get SBBSqz as a combination of "S" and the content of a filed that contains "BBSqz"

this works

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

using this where I just type SBBExh in the query
tblFilters.SBBExh AS Rank

I need something like
tblFilters.("S"& [Forms]![frmOpenTrades]![Strategy])

I need the "BBExh" part to come from the field in the form and concatenate with the "S" to get the filed to get the data from

[Welshgasman]

I have NEVER selected from a form, and not sure you can do it.?
If you are creating a value of "S" and value of SBBSqz, then you cannot have value in the table row.?

Put the criteria into another column in the QBE

[mikem]

I'm new to SQL but it looks kinda like regular programming so I thought

tblFilters.SBBExh
could be created with
tblFilters.("S" & [whatever field holds BBExh])

sorta like a variable = concatenated values

I need data from one of 15 columns depending on the trading strategy so I can hard code 15 queries and just call the appropriate ones.

is there a container that can hold and execute multiple SQL statements? if so then I can at least group the 3 together that I need for each different strategy. I don't know where that would go in Access.

it would look like

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));


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


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

thanks for the help

[Welshgasman]

You would run those one after the other in vba.
I'm still not sure about Select and Forms, so wait for an expert to chip in.
Seems you can

INSERT INTO Table1 ( Field1, Field2 )
SELECT 27 AS Expr1, "S" & [Forms]![Form1]![Field2] AS Expr2
FROM Table1
WHERE (((Table1.Field1) Is Null));

[wvmitchell]

you need to clear the "Table" row in the grid for the Rank column in your "error.jpg" graphic.

[mikem]

that gets rid of the error but then I get the text value of "SBBExh" instead of the values I need from the SBBExh field from the tbl Filters table

[mikem]

In case anyone else needs to do this, I got this from stack overflow

Very simple and works perfectly.


strField = "S" & [Forms]![frmOpenTrades]![Strategy]

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

[Welshgasman]

No, what was suggested works just as well.? I even tested it all out in one of my DBs as it is not something I have done before, as I emntioned?, and the syntax looked strange to me. :-(

You never said you were prepared to put anything into variables?

That opens a heap of new options. :-)