Max Date display problem

[TooManyReports]

I have a form that I want to display max date from a table, but every time I try to get it to display on the form, I get #error or #name?. I can run a query and get the information correctly, but it refused to display at all on the form.

I have tried the following on an unbound text box::

= MAX(

![Date]) Displays #error
=[Max Date Query]![Max Date] Displays #name?
=Dlookup("Max Date","Max Date Query") Display #error

The table that is being queried has the field set to short date. Date was converted by using DateValue because the original date field was in YYYYMMDD format and changed to MM/DD/YYYY format


Any help would be greatly appreciated!

 

[pbaldy]

The only one with a chance of working is the last. Try bracketing the table and field names, due to the inadvisable spaces. You could probably replace that and the query with a DMax().

 

[missinglinq]

Also of importance, I think, is the Datatype of the original 'date.' Is that actually defined as a Date/Time Field?

And possibly of even more importance is the fact that you're using a non-U.S. date format, which is never good, as Access is very U.S. oriented in these matters. Here's a link to Allen Browne's paper on using these kinds of dates in Access:

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

Linq ;0)>

 

[TooManyReports]

Thanks all for the information. The field that was being lookup'd was standard us format converted from non-standard format. The date field in the table for the lookup was set to short date for US.

I did eventually find my answer. I had do a dlookup against the table.

Using dlookup("Max(Date)","Date Table") is what finally worked properly.

 

[missinglinq]

Glad you got it resolved!

Good luck with your project!

Linq ;0)>