Entering data in a text box.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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
I will have to add the query as Table, but this is a dumb error by Access.
Code: Select all
DateOnly: Int([Tradedate])
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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"
However the sort is sorting on the dd/mm/yy, not ont eh serial nmbers?
Code: Select all
Date: format(Int([Tradedate]),"dd/mm/yy")
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
No, don't use the Format function. Instead, set the Format property of the calculated column to dd/mm/yy in design view.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
That does better.
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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?
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
You can use Sum(Abs([Quantity])), but you have to set the Total option to Expression.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
Can you use
NameOfNextControl.SetFocus
substituting the correct control name of course?
NameOfNextControl.SetFocus
substituting the correct control name of course?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
I entered some code to calculate profit, but I received an error message.
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.
The profit was calculated, but when the code returned to
I received an error message "type mismatch". Can anyone let me know what should be changed to allow the code to complete without errors?
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
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
Code: Select all
If Me.ActionID = 47 Or Me.ActionID = 49 Then
CalcProfit (Profit)
End If
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
Are you suggesting that those statements should be CalcProfit and FunctionCalcProfit?
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.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.
OK.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.
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
Adding a one to many instead of a one to one
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.
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
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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?
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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
Thanks
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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:
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.
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
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Entering data in a text box.
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)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1409
- Joined: 08 Jul 2016, 18:53
Re: Entering data in a text box.
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.
Object required. Surely access can "find" the object between codes? Maybe this why the frm hasn't opened yet.
Code: Select all
If frmTrades.ActionID = 47 Or frmTrades.ActionID = 49 Then
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands