Unbound text box with formula
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Unbound text box with formula
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]))
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]))
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unbound text box with formula
Your query is based on TDAmeritradePositions and Sheet1.
In your HAVING clause, you refer to [TDAmeritrade].[Shares]. What is TDAmeritrade?
In your HAVING clause, you refer to [TDAmeritrade].[Shares]. What is TDAmeritrade?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
It is just a table.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unbound text box with formula
But TDAmeritrade is not mentioned in the FROM clause, so Access/SQL doesn't "know" it.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
FROM TDAmeritradePositions LEFT JOIN Sheet1 ON TDAmeritradePositions.Symbol = Sheet1.Symbol
??
ETA: So change it to TDAmeitradePositions?
??
ETA: So change it to TDAmeitradePositions?
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unbound text box with formula
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
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
With that expression
Maybe Amount should not be a Where, but just a calculation which was the original intent
Maybe Amount should not be a Where, but just a calculation which was the original intent
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unbound text box with formula
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
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unbound text box with formula
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], ...
..., Max(Sheet1.Dividend) AS [Next Dividend], ...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
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;
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unbound text box with formula
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
Tried that and came up with the exact results.HansV wrote: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.
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
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
Thanks
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
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.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Unbound text box with formula
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
I'm am beyond stupid today, I don't know where to find File>Options
-
- BronzeLounger
- Posts: 1419
- Joined: 08 Jul 2016, 18:53
Re: Unbound text box with formula
Never mind, I found it, thanks for your patience.