Puzzling query out pt

[bknight]

I have had this query from the beginning of creating the Db. Today it presents no records and I can't find a reason.

Code: Select all

SELECT Trades.Tradedate, Day([tradedate]) AS [Day], Month([tradedate]) AS [Month], Year([tradedate]) AS [Year], Trades.Symbol, Trades.ContractMonth, Trades.Quantity, Trades.ActionID, Trades.ActionName, Trades.RawP, Trades.Price, Trades.Commission, Trades.Fees, Trades.Amount, Trades.Profit, Trades.OrderNum, Trades.Notes
FROM Trades
WHERE (((Day([tradedate]))=31) AND ((Month([tradedate]))=5) AND ((Year([tradedate]))=23))
ORDER BY Trades.Tradedate;

There are trades from yesterday. What am I missing?

[HansV]

Shouldn't it be

((Year([tradedate]))=2023))

[bknight]

That worked, but it has worked for 6 months with only 23. I don't understand why that is, but thanks.

[SpeakEasy]

>it has worked for 6 months with only 23
I'm surprised

[bknight]

>it has worked for 6 months with only 23
I'm surprised

Since created ???, the year was 23 and that worked on the days it was employed, not every day, but frequently. Today was the first day that no records were retuned, prompting the thread and subsequently the comment/question.

[HansV]

I don't see how this could ever have worked. The Year function ALWAYS returns the four-digit year (2023), not the two-digit year (23).

[bknight]

I don't see how this could ever have worked. The Year function ALWAYS returns the four-digit year (2023), not the two-digit year (23).

I may not understand why it worked either, but it did until today.

[Gasman]

Perhaps show the base query from where that sql is generated?

[bknight]

It has been changed, but

[Gasman]

And that shows 2023 not 23 ?

[bknight]

That is because I changed it as mentioned in previous post, that was the change after Hans suggested it should be 2023, not 23.

[HansV]

If you temporarily delete 2023 from the Criteria line, then switch from design view to datasheet view, what do you see in the Year column? Values such as 21, 22, 23? Or such as 2021, 2022, 2023? Or even a mixture?

[bknight]

2023

[HansV]

Yeah, that's what I expected. As I wrote in an earlier reply, the Year function returns the 4-digit year of a date 2023 in this example.

I still don't see how just 23 as Criteria could have worked...

[bknight]

I don't know either, but it did, until it didn't.
In the same Db you have suggested a convert2number function and I would like a refinement preventing copy/paste/typing values in two fileds. Current I copy the string price in the frmFracCalculation. Hit return then paste that string into Rawp. Move back to frmFracCalculation and copy he calculated value to Price.

Code: Select all

Private Sub RawPrice_AfterUpdate()
    Me.Price = Convert2Num(Me.RawPrice)
End Sub

Code: Select all

Function Convert2Num(Price As Variant)
    Dim p As Double, p1 As Double
    
    If IsNull(Price) Then
        Convert2Num = Null
    Else
        p = InStr(Price, "'")
        If p <> 0 Then
        Convert2Num = Left(Price, p - 1) + Mid(Price, p + 1) / 32
        Else
        p = InStr(Price, " ")
        p1 = InStr(Price, "/")
        Convert2Num = Left(Price, p - 1) + Mid(Price, p + 1, p1 - p - 1) / 32
        End If
    End If
End Function

[HansV]

I'm sorry, I don't understand your question. Could you try to explain it more clearly?

(I won't be able to reply immediately, so no hurry)

[bknight]

I copy the string "price" from the trading software.
I then paste the string into the frmFracCalculater and pressenter
The string is converted to a number.
I then paste the string "price" into the RawP field, I would like this to be automatic.
I copy the converted number from the frmracCalculaor and paste that number into the price field, this I would like don automatic.
We should be able to use the code I have, but a new code can be written.

[HansV]

I still don't understand. Is frmFracCalculater (or frmFracCalculation or frmracCalculaor) the only form involved, or is there another form?

[bknight]

Yes frmTrades contains the fields that will be updated frmTrades.RawP will contain the RawPrice of the Convert2Number input while frm.Trades.Price will contain the ouput of the Convert2Number function. I tried a bit of those actions, but it isn't working.

Code: Select all

Function Convert2Num(Price As Variant)
    Dim p As Double, p1 As Double
    
    If IsNull(Price) Then
        Convert2Num = Null
    Else
        p = InStr(Price, "'")
        If p <> 0 Then
        Convert2Num = Left(Price, p - 1) + Mid(Price, p + 1) / 32
        Else
        p = InStr(Price, " ")
        p1 = InStr(Price, "/")
        Convert2Num = Left(Price, p - 1) + Mid(Price, p + 1, p1 - p - 1) / 32
        End If
    End If
    Forms!frmtrades.Price = Convert2Num
End Function

Code: Select all

Private Sub RawPrice_AfterUpdate()
    Me.Price = Convert2Num(Me.RawPrice)
    Forms!frmtrades.RawPrice = Me.RawPrice
End Sub

[HansV]

So what does frmFracCalculater (or frmFracCalculation or frmracCalculaor) have to do with it?