Trying to get Max() function to work in simple formula

[RunTime91]

I have the below query as the control source for a command button to populate a table ~ basic stuff.

DoCmd.RunSQL "INSERT INTO TblVehicleExpenses VALUES ('" & Me.TxtPurchDate & "', '" & Me.TxtMerchant & "', " & _
"'" & Me.CmbVehicle & "', '" & Me.TxtMileage & "', '" & Me.TxtTtlDays & "', '" & Me.TxtItem & "', '" & Me.CmbRsnForPurch & "', " & _
"'" & Me.TxtGallons & "', '" & Me.TxtAmount & "', '" & Me.TxtMPG & "', '" & Me.TxtCostPerGal & "', " & _
"'" & Me.TxtTtlDays & "','" & Me.TxtCostPerDay & "')"

I'm trying to evolve the above query to calculate the number of days between gas purchases.
I'm trying to set the Me.TxtTtlDays value with something like the following.

IIf RsnForPurc = "Gas", Me.TxtTtlDays = Date - Max(PurchDate), 0



Naturally, the above doesn't work as it pulls a 'Sub or Function Not Defined Error'.

In my attempt to reverse engineer a solution I have tried the below which works perfectly.

Me.TxtTtlDays = Date - PurchDate

In my attempt to expand on the above to include the MAX function I once again get a Sub or Function Not Defined Error.

Me.TxtTtlDays = Date - MAX(PurchDate)

As always any help is greatly appreciated ~

RT91

[June7]

Max() is an SQL aggregate function and does not work in VBA. DMax() domain aggregate function works in VBA.

Can use Max() in expression in textbox ControlSource property.

[Gicu]

But you shouldn't store that value as tomorrow it will be inaccurate, you can always calculate it when needed using dMax as suggested by June or in a totals query (or in a subquery).

Cheers,

[RunTime91]

Hi June ~ Thank You again for always being out there ~
Vlad Thank you as well!

Where I'm at thus far is I'm still trying to place this before the query and use it to set the value for Me.TxtTtlDatys.

Me.TxtTtlDays = Date - DMax(PurchDate, TblVehicleExpenses, RsnForPurch = Gas)

The error I'm getting is an 'invalid argument in the domain.'

I've tried with and without quotes ~ nothing works.

The intelisense indicates the correct values for both the PurchDate and RsnForPurch values.

However, depending upon the punctuation I place around the Table name I either get nothing or 'empty'.

Thanks Everyone for any guidance...

[June7]

Did you consider Vlad's advice to not store this value in table? I agree with it. Real trick with storing is figuring out what event to put code in.

Me.TxtTtltDays = Date - DMax("PurchDate", "TblVehicleExpenses", "RsnForPurch='Gas'")

Or in textbox ControlSource:

=Date() - Max([PurchDate])

=Date() - Max(IIf([RsnForPurch]="Gas", [PurchDate], Null))

=Date() - DMax("PurchDate", "TblVehicleExpenses", "RsnForPurch='Gas'")

[RunTime91]

Solved ~

Thank You June ~ Thank You Vlad!

This seems to work perfectly ~

Me.TxtTtlDays = Date - DMax("[PurchDate]", "TblVehicleExpenses", "[RsnForPurch] = 'Gas'")

Okay Experts can one (or many) of you help me to understand why the above works???

I had to add quotations & brackets on the first table field name.
I had to add just quotations for the table name.
Lastly, I had to add apostrophes around the second field value, brackets around the second field name, and then put all of that inside quotations.

If I could understand the reasons what those punctuations are necessary, the way they are.

Thank You Everyone ~

[Micron]

The short answer about the quotes around the parts are required and that fact is easily found:
https://support.microsoft.com/en-us/...b-c554053d299f

You might want to bookmark this source (or even its parent page)
https://learn.microsoft.com/en-us/of...verview/access

[RunTime91]

Hey June ~ Yes, I did understand Vlad's advice...

Thing is... I'm not putting code inside the table or the control, I'm putting it inside the code window.

Here is the code (thus far)

Private Sub Command38_Click()

DoCmd.SetWarnings False


Me.TxtTtlDays = Date - DMax("[PurchDate]", "TblVehicleExpenses", "[RsnForPurch] = 'Gas'")


DoCmd.RunSQL "INSERT INTO TblVehicleExpenses VALUES ('" & Me.TxtPurchDate & "', '" & Me.TxtMerchant & "', " & _
"'" & Me.CmbVehicle & "', '" & Me.TxtMileage & "', '" & Me.TxtTtlDays & "', '" & Me.TxtItem & "', '" & Me.CmbRsnForPurch & "', " & _
"'" & Me.TxtGallons & "', '" & Me.TxtAmount & "', '" & Me.TxtMPG & "', '" & Me.TxtCostPerGal & "', " & _
"'" & Me.TxtTtlDays & "','" & Me.TxtCostPerDay & "')"


DoCmd.SetWarnings True

End Sub

This code is just in the beginning stages.

PS... Can someone please remind me what the code tags are ~ Thanks

[June7]

The [ ] should not be necessary because names do not have spaces nor special characters.

Table name does not require [ ] even if there are spaces - don't know why, that's just the way MS programmed domain aggregate functions.

The apostrophes are necessary because field is text type. If it were date/time the delimiters would be #. Number fields do not require delimiters. This is the same syntax used in SQL statements.

Domain aggregate functions require field and table name arguments literal inputs to be within quote marks.

Your SQL statement is still saving the calculated value.

Why using SQL to create record as opposed to bound form?

Are all these fields text type?

[RunTime91]

Thanks Micron!!

Awesome...

Understanding these techniques can save a lot of questions

[RunTime91]

Hey June ~ Thank You!

PurchDate is formatted as Date ~ the other two are indeed text

As for why using SQL as opposed to using a bound form ~ Honestly, it is just the way I learned. So, habit I suppose.

This database is eventually going to be a front end with a SQL database back-end.

Not sure if that matters, but just thought I would offer the info.

[June7]

CODE tags can be generated with the # button on edit bar.

Again, if field is date/time type, do not use apostrophe delimiters for input, use #. What other two?

You have used apostrophes for fields I would expect to be number type.

But why use code?

[Gicu]

You are inserting the calculation into the table in your Docmd.RunSQL so tomorrow all records inserted today will be one day off...
And I think that you might need to adjust the calculation to include the VehicleID (numeric value which I assume is the bound hidden column of the CmbVehicle combo):

Me.TxtTtlDays = Date - DMax("[PurchDate]", "TblVehicleExpenses", "[RsnForPurch] = 'Gas' AND VehicleID ="  & Me.CmbVehicle)

If you store the Vehicle name as text then that becomes:

Me.TxtTtlDays = Date - DMax("[PurchDate]", "TblVehicleExpenses", "[RsnForPurch] = 'Gas' AND Vehicle ='"  & Me.CmbVehicle & "'")

Cheers,

[Welshgasman]

Solved ~

Thank You June ~ Thank You Vlad!

This seems to work perfectly ~

Me.TxtTtlDays = Date - DMax("[PurchDate]", "TblVehicleExpenses", "[RsnForPurch] = 'Gas'")

Okay Experts can one (or many) of you help me to understand why the above works???

I had to add quotations & brackets on the first table field name.
I had to add just quotations for the table name.
Lastly, I had to add apostrophes around the second field value, brackets around the second field name, and then put all of that inside quotations.

If I could understand the reasons what those punctuations are necessary, the way they are.

Thank You Everyone ~

FWIW you only need those brackets if you have a field name with a space in it (not advised)
The rest is the syntax of the language.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.


Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.