Unbound text box with formula

[bknight]

Hi in
SELECT TDAmeritradePositions.Symbol, Max(Sheet1.[Ex Date]) AS [Next Ext Date], Max(Sheet1.Dividend) AS [Next Dividend], Max(Sheet1.[Pay Date]) AS [Next Pay Date], [Amount] AS Expr1
FROM TDAmeritradePositions LEFT JOIN Sheet1 ON TDAmeritradePositions.Symbol = Sheet1.Symbol
WHERE (((Sheet1.[Ex Date])=(SELECT Max(T.[Ex Date]) FROM Sheet1 AS T WHERE T.Symbol=TDAmeritradePositions.Symbol)))
GROUP BY TDAmeritradePositions.Symbol
HAVING ((([Amount])=[Sheet1].[Dividend]*[TDAmeritrade].[Shares]))
ORDER BY TDAmeritradePositions.Symbol;

I'm attempting to calculate an Amount which would be [Sheet1.Dividend] * [TDAmeritradePositions.Shares] I'm coming up with an error you attempted ...I don'y have he exact error message, but it deals with HAVING ((([Amount])=[Sheet1].[Dividend]*[TDAmeritrade].[Shares]))

[HansV]

Your query is based on TDAmeritradePositions and Sheet1.
In your HAVING clause, you refer to [TDAmeritrade].[Shares]. What is TDAmeritrade?

[bknight]

It is just a table.

[HansV]

But TDAmeritrade is not mentioned in the FROM clause, so Access/SQL doesn't "know" it.

[bknight]

FROM TDAmeritradePositions LEFT JOIN Sheet1 ON TDAmeritradePositions.Symbol = Sheet1.Symbol

??

ETA: So change it to TDAmeitradePositions?

[bknight]

Ok tried adding Positions to the tale name and got this error, similar if not equal to the original error, but I have an image now.

[HansV]

As the error message says, you can only use aggregated expressions such as Sum(...), Count(...) etc. in the HAVING clause. Otherwise, conditions should be in the WHERE clause.

Perhaps this?

SELECT TDAmeritradePositions.Symbol, Max(Sheet1.[Ex Date]) AS [Next Ext Date], Max(Sheet1.Dividend) AS [Next Dividend], Max(Sheet1.[Pay Date]) AS [Next Pay Date]
FROM TDAmeritradePositions LEFT JOIN Sheet1 ON TDAmeritradePositions.Symbol = Sheet1.Symbol
WHERE (((Sheet1.[Ex Date])=(SELECT Max(T.[Ex Date]) FROM Sheet1 AS T WHERE T.Symbol=TDAmeritradePositions.Symbol))) AND ((([Amount])=[Sheet1].[Dividend]*[TDAmeritrade].[Shares]))
GROUP BY TDAmeritradePositions.Symbol

[bknight]

With that expression

Maybe Amount should not be a Where, but just a calculation which was the original intent

[HansV]

Should Sheet1.Dividend*TDAmeritradePositions.Shares be summed? If so:

SELECT TDAmeritradePositions.Symbol, Max(Sheet1.[Ex Date]) AS [Next Ext Date], Max(Sheet1.Dividend) AS [Next Dividend], Max(Sheet1.[Pay Date]) AS [Next Pay Date], Sum(Sheet1.Dividend*TDAmeritradePositions.Shares) AS Amount
FROM TDAmeritradePositions LEFT JOIN Sheet1 ON TDAmeritradePositions.Symbol = Sheet1.Symbol
WHERE (((Sheet1.[Ex Date])=(SELECT Max(T.[Ex Date]) FROM Sheet1 AS T WHERE T.Symbol=TDAmeritradePositions.Symbol)))
GROUP BY TDAmeritradePositions.Symbol

[bknight]

My first reaction is no, the value should the shares(number) times the dividend for each symbol with the number of shares and the dividend being unique to each symbol. However I took your suggestion and ran with it, the results are what I would expect so the answer would be yes.
Thanks

ETA:
Well almost correct
I can fix the formatting, but how do I get rid of Text9 and replace it with amount. This of course is a form.

ETA2:
Nevermind, I stumbled through and fixed the control name, not the label name.

[bknight]

Part two of this thought and I having a bit of problem with it. The query as record source to the form runs and display the information I wanted, however when I run the for, I get #Name? in each record on one of the fields, even though it displays in the query. I thought it might be a mismatch to the query in the record source but when I select the drop down list has the field, and is/was selected. Any thoughts on eliminating the #Name? and displaying the information.

[HansV]

The query we discussed earlier in this thread has a column named Next Dividend, but not a column named Dividend:

..., Max(Sheet1.Dividend) AS [Next Dividend], ...

[bknight]

Code: Select all

SELECT tblTradestationPositions.Symbol, Max(Sheet1.[Ex Date]) AS [Next Ex Date], Max(Sheet1.Dividend) AS Dividend, Max(Sheet1.[Pay Date]) AS [Next Pay Date], Last([Sheet1].[Dividend]*[tblTradestationPositions].[Shares]) AS Amount
FROM tblTradestationPositions LEFT JOIN Sheet1 ON tblTradestationPositions.Symbol = Sheet1.Symbol
WHERE (((Sheet1.[Ex Date])=(SELECT Max(T.[Ex Date]) FROM Sheet1 AS T WHERE T.Symbol=tblTradestationPositions.Symbol)))
GROUP BY tblTradestationPositions.Symbol;

In this case.

[HansV]

That looks like it should work, but perhaps Access gets confused. What happens if you use the original Max(Sheet1.Dividend) AS [Next Dividend] and set the Control Source of the text box to Next Dividend? The label in the header can still have Dividend as caption.

[bknight]

That looks like it should work, but perhaps Access gets confused. What happens if you use the original Max(Sheet1.Dividend) AS [Next Dividend] and set the Control Source of the text box to Next Dividend? The label in the header can still have Dividend as caption.

Tried that and came up with the exact results.

[bknight]

I deleted the form and re-created it, works, now all I have to do is modify it to the way I wish it to look.
Thanks

[bknight]

Easy question today, I don't remember nor have I been able to find where you "assign" a form to open when the dB opens.

[HansV]

Select File > Options.
Select 'Current Database' in the navigation pane on the left.
Select the form you want to be shown automatically from the 'Display Form' dropdown.
Click OK.

S3124.png

[bknight]

I'm am beyond stupid today, I don't know where to find File>Options

[bknight]

Never mind, I found it, thanks for your patience.