Max Function Not Working With Date

[cec]

Good morning All-



Ok I have a text box on my main form that I'm trying to pull in the most recent date from a subform.

I have tried just : =Max(Form!Subform!RecvdDate) =Error

I have also tried : =DMax("[RecvdDate]","tbl_Subform") = 11/7/12, which isn't the correct anwser and honestly not sure where it got that

Can anyone help with this... Please

Thanks!

[alansidman]

Look at this video on subforms for the getting the correct syntax re: subforms.

http://www.datapigtechnologies.com/f.../subform2.html

Alan

[cec]

wasn't able to open this....

[alansidman]

Does your IT department have restrictions on sites you can open. It is a hyperlink that should open for you. I just tested and it opened for me directly by double clicking on the link. If that doesn't work, click on the link in my signature listed for DataPig. Scroll to the section on sub-forms.

[Missinglinq]

...Ok I have a text box on my main form that I'm trying to pull in the most recent date from a subform.

...I have tried just : =Max(Form!Subform!RecvdDate) =Error

...I have also tried : =DMax("[RecvdDate]","tbl_Subform") = 11/7/12, which isn't the correct

Aggregate Functions, such as Max or DMax, cannot be run against the Controls in a Form, as you first attempted! They have to be run against a Field in a Table or Query, such as in your second example.

Look at this video on subforms for the getting the correct syntax re: subforms.

Since you cannot run Aggregate Functions against the Controls in a Form (or Subform) the proper syntax for referencing these Controls, in a Subform, isn't really germane to the question at hand.

Assuming that RecvdDate and tbl_Subform are spelled correctly, the first thing I'd look at would be the Datatype of RecvdDate. Is it actually defined as a DateTime Field? Defining a date-holding Field as Text is a frequent causative factor when 'dates' misbehave!

Another thing that causes problems with dates is when the date format being used is not the U.S. format, i.e. is not MM/DD/YYYY. MS Access is very US-centric in this matter. If you're using a British-type formatting, have look at Allen Browne's excellent article on handling international dates.

http://allenbrowne.com/ser-36.html

Linq ;0)>