Subtracting a TextBox Date Value from DMax Date Value

[RunTime91]

Here's your stupid question of the day ~ So frustrating!

This works perfecxtly:
Me.TxtTtlDays = Date - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")

This is what I'm trying to get to work but it returns NULL:



Me.TxtTtlDays = MeTxtDate - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")

I've tried all of these...And well, here I am:

Me.TxtTtlDays = 'MeTxtDate' - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")
Me.TxtTtlDays = "'MeTxtDate'" - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")
Me.TxtTtlDays = #MeTxtDate# - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")
Me.TxtTtlDays = (MeTxtDate - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'"))

Me.TxtDate is formatted as Short Date in the property sheet

What am I missing???

Thank You all

[Welshgasman]

Works for me, though not sure what you are trying to get?

? date - dlookup("TransactionDate","TestTransactions", "ID=12")
 1095

ID 12 holds value of 21/01/2020

? date - #01/21/2020#
 1095

I know I used DlookUp(), but result should be the same?
So make sure Dmax() produces something first.

Break any problem down into smaller steps and test each part.

The closest you got was your first try,. the next 3 are just completely wrong.
The last the same as the first?, just a different control/value?

[Micron]

MeTxtDate

By any chance is that supposed to be Me.TxtDate ?

[RunTime91]

Welshgasman And Micron Thank you both for chiming in on this...

Welshgasman ~ Yes, I did break it down and tested the following:

I removed the Me.TxtDate and tested just the DMax portion ~ works perfectly.
According to the intelisense Me.TxtDate is showing the correct date and I tested it with a variable and the variable returns the date I entered into the TextBox.
The last one I added a left and right parenthesis around the whole equation.
So, both components of the code work independently but once I put them together ~ Nothing.

Micron ~ Woops ~ Yeah the period is there in the code window, I'm having to use 2 computers right now and copy/paste is not an option, so I just retyped everything free hand.

What I'm trying to achieve is simply totaling the # of days between two dates ~ The first date will be the date of the last entry in the table, and the second date is what I enter into Me.TxtDate textbox.

Me.TxtTtlDays acts (for now) as a variable which is called in a SQL query. All of this is stage one of a several stage build that will eventually be an Access front end / SQL backend application...

Hope this was helpful ~ Thank you Both for being out there ~

[Micron]

This I don't understand (According to the intelisense Me.TxtDate is showing the correct date) because that's not what intellisense does. Maybe try

Me.TxtTtlDays = "#" & Me.TxtDate & "#" - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")
or assign Me.TxtDate to a date/time variable and use the variable instead:

Dim dteDate As Date
dteDate = Me.TxtDate

Me.TxtTtlDays = dteDate - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")

EDIT IIRC DMax returns a variant. Maybe your returned value is text (although I don't think it should matter, but grabbing at straws here) so could also try substituting real dates rather than variables or references on both sides and see if anything changes. Or try
CDate(DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'"))

[davegri]

one more to try...

Me.TxtTtlDays = CDate(Me.txtDate) - DMax("PurchDate", "TblVehicleExp", "Type = 'Gas'")

[RunTime91]

Micron & Davegri ~

Both solutions (CDate & setting a variable) worked!

Micron, I was referring to the little yellow text window that pops up when you hover over the code... You're right though, it's not Intellisense...


Micron, Welshgasman & Davegri ~ Thank You all!!