New Db building stuff

[bknight]

If I delete the field I won't be able to see the field! Obviously from my posts you must realize that I want to see the field, filled out. Maybe that is the best way to describe my issues I want to see what you refer as "redundant" data. I'm not concerned with data bloat.
I do realize your training says it isn't necessary, it is a desire.

Let put it another way, you know nothing of the fields except you can see what they contain wouldn't it be easier to read each record? Remember you don't know that 46 = Buy to Open, but could deduce it if the data were sturdied.

[HansV]

Users can see the description in the form. They should never view the table directly.

And you can create a query based on the Trades and TradeNum table:

S2119.png

S2120.png

You can use this query as Record Source for a report, for example.

[bknight]

I could make a query on the table and you would not know what the actions are, you would have to look at another table, the image is a query not the table. But I digress and further discussions are pointless I want the field to be in the table, you don't feel it is necessary and I am unable to get a positive result so I'm dropping the issue and non-resolved in my mind.

[HansV]

You do NOT have to look at another table separately.
You can use the query as if it is the Trades table, and see both the ActionID and its description without having to store it twice in the Trades table.
The form I proposed also displays both the ActionID and its description without needing two fields in the Trades table.
So there are only advantages, without any downside.

[bknight]

Attempting to build an event

Code: Select all

Private Sub Form_DataChange(ByVal Reason As Long)
Me.[Fees].OnChange
if me.(left,3[Symbol])="MES" then

End Sub

results in an error. What is the correct syntax?

[HansV]

This is probably not an event you want to use - it applies only to a form in PivotTable view.
What exactly do you want to do?

[bknight]

Attempting to calculate a number but there are several tree possibilities thus the if statement triggered by entering/changing the fees field.
Long/short
Open/close
symbol 3 choices now but that may change with time.
I thought that event with a change would be appropriate, but if not then what?

[HansV]

Which field do you want to change to which values on which conditions?

[bknight]

It will be in the form of [price] * constant(depende on symbol) * [quantity] + [commissions] = [fees] = [amount]
If it is a closing trade then the [amount] -previous [amount]=[profit]

[HansV]

I don't think you need VBA for that - a formula (calculated column) would probably be sufficient

[bknight]

I don't think you need VBA for that - a formula (calculated column) would probably be sufficient

Never done that would you give me an example?

[HansV]

Option 1: in a query based on the table; use the query as Record Source for forms and reports.

Fees: [price]*DLookup("constant", "lookuptable", "symbol=' & [symbol] & "'")*[quantity]+[commissions]

Option 2: in the Control Source of a text box named Fees on a form or report.

=[price]*DLookup("constant", "lookuptable", "symbol=' & [symbol] & "'")*[quantity]+[commissions]

[bknight]

It seems like the second option would be better. So, a new table with symbols and constant. In that table filed one symbol and field two constant, I assume.
Suppose I call the table tblTickValue, then the formula part DLookup([Multiplier],[tblTickValue],"MES")?

[HansV]

You're almost there. It would be

DLookup("Multiplier","tblTickValue","Symbol='MES'")

but since you want it to be variable

DLookup("Multiplier","tblTickValue","Symbol='" & [Symbol] & "'")

[bknight]

You're almost there. It would be

DLookup("Multiplier","tblTickValue","Symbol='MES'")

but since you want it to be variable

DLookup("Multiplier","tblTickValue","Symbol='" & [Symbol] & "'")

I don't think that "Symbol='" & [Symbol] & "'") will work, but I will try that, shouldn't there be an"'" on the inner parentheses?
Never mind when I posted the quote, I see you used "'".
Does there need to be an "=" at the beginning of all of the formula, in the control source?

[HansV]

> Does there need to be an "=" at the beginning of all of the formula, in the control source?

Yes.

[bknight]

Acid test tomorrow, thanks

[bknight]

not correct

Code: Select all

=-[Quantity]*[Price]*DLookUp("Multiplier","tblTickValue","Symbol='" & [Symbol] & "'")*[Quantity]+[Commission]+[Fees]

All buys should be negative, all sells should be positive. Form the image all values are negative.
I changed the formula by eliminating the minus sign and all values are positive. This is a logic issue and I don't see where I'm wrong, can anyone?

[HansV]

Why do you have Quantity twice?

[bknight]

Thanks the formula worked in Excell. And I thought I entered it correctly, but alas I put quantity in twice.