DMax for date return - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DMax for date return

Kim Nielsen

3 years ago

Hello, I have reviewed the videos on here regarding the DMax function. I have tried a couple of variations in order to find the last time a cylinder was filled. I want my form of cylinder details(2-CylinderInfoT) to reference a fill table (1a-CylFilledT), where the cylinder serial number is recorded along with the dates it was filled. I want it to find the last date a cylinder was filled based on the current Cylinder Serial number on the form (2-CylinderInfoT & CylinderInfoF).

I first used this in the text field (txtLastFilled) under control source with the following code:
=Nz(Dmax("FillDate2","1a-CylFilledT", "CylSerial="&CylSerial),0)
Now access continually placed the square brackets here around the 2nd "CylSerial":
=Nz(Dmax("FillDate2","1a-CylFilledT", "CylSerial="&[CylSerial]),0)
I then tried:
=Dmax("FillDate2","1a-CylFilledT", "CylSerial="&[CylSerial])

The text field flashes "Error", to test the first part of the DMax to return the very last date in the table this worked. with:
=Dmax("FillDate2","1a-CylFilledT") and it did return the very last date.

I then tried to place it through a Query where I could group the serial numbers and referenced the query "CylLastFilledQ" vice the "1a-CylFilledT"
I then tried placing this in the form load VBA as:
txtLastFilled = DMax("FillDate2","CylLastFilledQ","CylSerial = "& CylSerial) and tried: txtLastFilled = DMax("FillDate2","1a-CylFilledT","CylSerial = "& CylSerial) Here i received a code error and highlighted in yellow.

I am thinking there is an issue with the return of the current cylinder serial number (CylSerial). I tried a single serial number with only one fill and still returns an error.
In my database, a cylinder could have more than one fills on the same date. Would this also cause me an issue?

Any guidance where to look for what is happening here is greatly appreciated. Thank you

	Kevin Robertson @Reply 3 years ago
Is CylSerial a Short Text field? Double Double Quotes

Kim Nielsen OP @Reply

3 years ago

Yes the 2 CylSerial Fields are Short Text. I tried the double Quotes on second CylSerial as follows:
txtLastField - Control Source:
DMax("FillDate2", "1a-CylFilledT", "CylSerial=" & ""CylSerial"") and the field returned a #Name? I verified that the spelling was also correct and they are.
I placed an = in front of the DMax, and there was an error with that?
So I tried this code: =DMax("FillDate2", "1a-CylFilledT", "CylSerial=" & """CylSerial""") and this returned an empty field however no errors or unexpected displays. but no last date filled.

Would this be better placed in VBA on the form onload event?

	Kevin Robertson @Reply 3 years ago
Try it like this: `=DMax("FillDate2", "1a-CylFilledT", "CylSerial=""" & [CylSerial] & """")`

Kim Nielsen OP @Reply

3 years ago

Unfortunately this did not work, it generated an error. Just to verify i checked the following to make sure i did not miss anything:
Table: 1a-CylFilledT the serial number (CylSerial) is a shorttext, the FillDate2 is a date field when it was filled.
Table: 2-CylinderinfoT, connected to CylinderInfoF where the CylSerial is a shorttext, last fill is an unbound text box called txtLastFilled where i would like to get the last time this cylinder was filled. I verified all the data parameters and they match.

i tried this updated line in the control source of the txtLastFilled unbound box and received an error. "The expression you entered contains an invalid syntax"

Would this work better through a query, where i group all the CylSerial numbers? I have no idea what i could be missing here.

	Kim Nielsen OP @Reply 3 years ago
Kevin, FOund my issue, I forgot the & at the end of the String, sorry missed that one piece and it causes havoc.. I checked this with number of serial numbers and it works great. Thank you yet again for assistance

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View