Entering data in a text box.

[HansV]

Create a calculated column in the query:

DateOnly: Int([DateTimeField])

where DateOnly is the name you want to give the calculated column and DateTimeField is the name of the Date/Time field.
Format the column as a date.

[bknight]

Now this is screwy. The error says that there are too many ")" in the expression, however if one looks closely at the image the expression is

Code: Select all

 DateOnly: Int([Tradedate])
 

I will have to add the query as Table, but this is a dumb error by Access.

[bknight]

Wait a moment if I remove the Table entry the query runs fine, however the output are serial numbers and I guess I'll need to format them as "dd/mm/yy"

Code: Select all

Date: format(Int([Tradedate]),"dd/mm/yy")

However the sort is sorting on the dd/mm/yy, not ont eh serial nmbers?

[HansV]

No, don't use the Format function. Instead, set the Format property of the calculated column to dd/mm/yy in design view.

S2176.png

[bknight]

That does better.

[bknight]

In a grouping query I would like to sum the absolute value of a field. Tried Sum(Abs([Quantity])), but got a you cannot us a sum in an aggregate function. So how may I sum those absolute values?

[HansV]

You can use Sum(Abs([Quantity])), but you have to set the Total option to Expression.

S2179.png

[bknight]

After an event code enters a number I would like the next field to be selected. After entering a lot of data I find myself typing data instead of moving to the next field. Will $crs(9) or $crs(11?) work.

[HansV]

Can you use

NameOfNextControl.SetFocus

substituting the correct control name of course?

[bknight]

I entered some code to calculate profit, but I received an error message.

Code: Select all

Private Sub Notes_AfterUpdate()
'Me.Dirty = False
    If Me.ActionID = 47 Or Me.ActionID = 49 Then
        CalcProfit (Profit)
    End If
End Sub

CalcProfit is in module 3, the code was copied from a previous code that I use everyday without errors so the Fldn look out of order.

Code: Select all

Function CalcProfit()
'This Function Will Calculate The Profit
Dim db As Database
Dim Rs As Recordset
Dim Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld6 As Field
Dim Fld11 As Field, Fld12 As Field
Dim I As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim dblNextAmt As Double, dblCurAmt As Double
Dim strCurSymbol As String, strNextSymbol As String, strCurCon As String, strNextCon As String
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By Tradedate")
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
    strCurSymbol = Fld3
    strCurCon = Fld4
    intCurQty = Fld5
    dblCurAmt = Fld11
For I = Rs.RecordCount To 2 Step -1
    If I < Rs.RecordCount Then
        If Fld6 = 46 Or Fld6 = 48 Then
        'Find prev opening trades
            If Fld3 = strCurSymbol And Fld4 = strCurCon Then
            'Found prev opening symbol and contract
                If Fld5 + intCurQty = 0 Then
                'closing quantity equals prev opening quan
                    dblNextAmt = Fld11
                    Rs.MoveLast ' move to the closing trade
                    Rs.Edit
                    Fld12 = dblNextAmt + dblCurAmt
                    Rs.Update
                    Exit For
                End If
            End If
        End If
    End If
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function

The profit was calculated, but when the code returned to

Code: Select all

 If Me.ActionID = 47 Or Me.ActionID = 49 Then
        CalcProfit (Profit)
    End If

I received an error message "type mismatch". Can anyone let me know what should be changed to allow the code to complete without errors?

[HansV]

Your function declaration does not provide for an argument:

Function CalcProfit()

But you call it with an argument:

CalcProfit (Profit)

I don;t understand the logic of the function. You appear to loop backwards through the records of the recordset Rs, but you jump to the last record inside the loop.

Finally, I'd change the declarations

Dim db As Database
Dim Rs As Recordset

to

Dim db As DAO.Database
Dim Rs As DAO.Recordset

to avoid confusion with a ADODB recordset.

[bknight]

Your function declaration does not provide for an argument:

Function CalcProfit()

But you call it with an argument:

CalcProfit (Profit)

Are you suggesting that those statements should be CalcProfit and FunctionCalcProfit?

I don;t understand the logic of the function. You appear to loop backwards through the records of the recordset Rs, but you jump to the last record inside the loop.

The records are chronologically earliest to latest. The profit that is being calculated is the last record if that record is a closing trade, so the reverse move until the code finds the opposite record to the last record and then the jump back to the last is necessary. I could not visualize another way to get the calculation. If you come up with a different concept, please let me know. Note this is a trivial example a one to one, but there can be one to many, and I will need to add quantities to continue searching records until the last quantity plus each found quantity is finally equal to zero.

Finally, I'd change the declarations

Dim db As Database
Dim Rs As Recordset

to

Dim db As DAO.Database
Dim Rs As DAO.Recordset

to avoid confusion with a ADODB recordset.

OK.

[bknight]

Adding a one to many instead of a one to one

Code: Select all

Function CalcProfit()
'This Function Will Calculate The Profit
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld6 As Field
Dim Fld11 As Field, Fld12 As Field
Dim I As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim dblNextAmt As Double, dblCurAmt As Double
Dim strCurSymbol As String, strNextSymbol As String, strCurCon As String, strNextCon As String
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By Tradedate")
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
    strCurSymbol = Fld3
    strCurCon = Fld4
    intCurQty = Fld5
    dblCurAmt = Fld11
For I = Rs.RecordCount To 2 Step -1
    If I < Rs.RecordCount Then
        If Fld6 = 46 Or Fld6 = 48 Then
        'Find prev opening trades
            If Fld3 = strCurSymbol And Fld4 = strCurCon Then
            'Found prev opening symbol and contract
                If Fld5 + intCurQty = 0 Then
                'closing quantity equals prev opening quan
                    dblNextAmt = Fld11
                    Rs.MoveLast ' move to the closing trade
                    Rs.Edit
                    Fld12 = dblNextAmt + dblCurAmt
                    Rs.Update
                    Exit For
                Else
                   dblCurAmt = dblCurAmt + dblNextAmt
                    intCurQty = intCurQty + Fld11
                End If
            End If
        End If
    End If
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function

I don't have any one to many examples but I'll try to do one next week to ensure that the logic is good. I attempted a [font=] around the code to high light the changes but I don't know how to use that function, it is the Else lines.

[bknight]

I gave up on the calculating profit, just too many branches to consider. However I was wondering whether a button could be opened up when the database opens, somewhere on the workspace that could run the one to one that I did code?

[HansV]

You could set a form to be opened when the database opens, in Office button > Access Options > Current Database > Display Form, and either call the code from the On Load event of this form or from a command button on the form.

Alternatively, create a macro (in the Access sense of the term, not VBA code) named AutoExec, and call the code from that macro using the RunCode command.

[bknight]

Ohther than the open form command when the Db loads, that is as clear as mud. I already have a form opening when the Db opens. I'm dropping the wole idea.
Thanks

[bknight]

Other than my brain was non functioning at 0200 this morning. Ok i added a table, built a query and I designed a form from the query. I put a button onto the grid, no records and put an event with the button. It all goes like this:

Code: Select all

Private Sub Notes_AfterUpdate()
    If Me.ActionID = 47 Or Me.ActionID = 49 Then
        DoCmd.OpenForm "frmProfit", acFormDS
    End If
End Sub

Private Sub Command0_Enter()'  This the button.
    If frmTrades.ActionID = 47 Or frmTrades.ActionID = 49 Then
        CalcProfit (Profit)
    End If
End Sub

Looked good to me except it fails because the form opens up and shows nothing but the form itself no button. Any suggestions to open the form and show the button?
The top image is what the form looks like when I open it from the left hand panel, the next image is when it opens with code.

[HansV]

Since you use acFormDS as view mode, the form opens as a datasheet form. You shouldn't use that since the form doesn't display data. Remove acFormDS (and the comma before it of course)

[bknight]

OK, well the code is faster than the machine. It hits this line and encounters an error, and the form did not show, yet as I'm tracing the code.

Code: Select all

If frmTrades.ActionID = 47 Or frmTrades.ActionID = 49 Then

Object required. Surely access can "find" the object between codes? Maybe this why the frm hasn't opened yet.

[HansV]

Try Forms!frmTrades