VBA DMax function

[edson]

Hello my friends,


The more I learn the more I find how much I do not know… and how often I forget. Aging maybe, ha?
The following is a “tuff” one. Please see the attached set of pics.

BACKGROUND
Pics 1 and 2 refer to both form and design view of the FrmAtzNor which is my Normal Price type updating form. Please focus on the first 3 records related to REGISTRO DE ARMA and keep in mind the respective price effective dates (under list column labeled Data Vigência and named NORDTVIG). This form is used to update the TblAtzNor (see pics 3 and 4 for data sheet and design views). This table plays the whole of my Normal Price list. They all work just fine and I have no problems with them.

In my project there will be a moment where I’ll search and select a price to apply to a service which in my story will be the service REGISTRO DE ARMA (which comes from the FrmCadastro). For the selecting purpose I use the FrmBuscaPrNor which can be seen through the Pics 5 and 6. LstPrNor data source query can be seen in the Pics 7 and 8. Please note that the query eliminates future price effective dates as well as null prices just in case. Everything works fine up to this point.

USER ACTION AND EXPECTED RESULTS
So when I click (select) a price in the LstPrNor, the selected price is supposed to load the fields PRVIG and PRLQD of the FrmCadastro as you can see in the list click event code (Pic 8). Besides, I want to message the user if he picks an “old price”. If you understand that today I could input a future effective date price, than the price to be applied stands for a “non future price which has the newest effective date (either current or past)”. Any other past effective date price should be considered an old price. That is why I used the function DMax for NORDTVIG instead of the function DLast.

PROBLEM DESCRIPTION
Code of the Pic 9 does not do it and it is driving me crazy. It goes always to the Else leg of the first If condition, no matter what price I pick. And if I remove the NORHVIG terms from the first if condition, it accepts any price picked.
I do not understand… Initially I suspected the code was not reading the DMax function properly due to some syntax mistake but I also thought it cannot be possible since I have in my project 5 (Five) other equal sets of objects for other 5 (Five) service prices and they all work fine I can assure with exactly the same coding (just different field names, but properties and types equally designed). What is wrong with this one? I can’t see anything wrong.
I am really praying and hoping that some skilled eagle eyed mentor could find the solution for this mystery. Many thanks in advance.

Private Sub LstPrNor_Click()
On Error Resume Next

'If Me.LstPrNor.Column(5) = DMax("NORDTVIG", "TblAtzNor", "MOTOCULTA ='" & Me.LstPrNor.Column(3) & "' and Nordtvig < = date()") Then
    'Forms!FrmCadastro!PRVIG = Me.LstPrNor.Column(4)
    'Forms!FrmCadastro!PRLQD = Me.LstPrNor.Column(4)
    'DoCmd.Close
    'Exit Sub
    
If Me.LstPrNor.Column(5) & Me.LstPrNor.Column(6) = DMax("NORDTVIG & NORHVIG", "TblAtzNor", "MOTOCULTA ='" & Me.LstPrNor.Column(3) & "' and Nordtvig <= date()") Then
    Forms!FrmCadastro!PRVIG = Me.LstPrNor.Column(4)
    Forms!FrmCadastro!PRLQD = Me.LstPrNor.Column(4)
    DoCmd.Close
    Exit Sub
Else
    If MsgBox("Você selecionou um preço ultrapassado. É este mesmo que deseja?", vbCritical + vbYesNo, Me.Caption) = vbYes Then
        Forms!FrmCadastro!PRVIG = Me.LstPrNor.Column(4)
        Forms!FrmCadastro!PRLQD = Me.LstPrNor.Column(4)
        DoCmd.Close
        Exit Sub
    Else
        Me.Undo
        DoCmd.CancelEvent
        DoCmd.Close
    End If
End If
End Sub

[June7]

If user should not pick 'old' price then why don't you exclude them from the list?

[edson]

Hello June7. How are you?
This is a business rule, meaning the user should not pick an old price on daily basis, however he can pick it under special circunstances... so the price history must be available.

[June7]

Could try using # delimiters for the Date parameter.

and Nordtvig <=#" & Date() & "#)


What is the data type of MOTOCULTA field? Does it actually store that text?

[edson]

I was wondering if it has a way to combine DLookup and DMax to get my problem solved... Don't know how to write it down yet.

[edson]

Type is Text. MOTOCULTA is actually a trick. MOT (short for Motive, e.g. reason) comes from a combo box. MOTOCULTA (Hiden MOT) stores the text of the combo box selection. I did that to avoid code complexity when referring to a combo box, so instead of writing for example ComboBoxName.Column(2) I simply write MOTOCULTA. It makes my work easier in queries too.

[edson]

Dear June7, I just tried
Nordtvig <=#" & Date() & "#)
as per your suggestion but it pops up syntax error message. Are we missing a list separator or parenthesis?

[June7]

Sorry, missing quote mark at end:

and Nordtvig <=#" & Date() & "#")

[edson]

Sorry, missing quote mark at end:

and Nordtvig <=#" & Date() & "#")

Wooooooooooooow!
Hold on a minute, please! I'm still looking for my chin bumping out on the floor.

It worked! Do you believe on that? It's really WORKING !!!!!

You are really a skilled eagle eyed master, June7. Congratulations!

In time, I'll change the other 5 codes accordingly just in case.
Well, I hate misunderstand things I do, so I have to ask you: How come, in your oppinion, the other 5 codes work fine without delimiters?

[June7]

I don't know. Because Date() is an intrinsic function that returns a date value, your original code should have worked. I just tested and it does for me.