Put data in form without running a query

[ka3pmw]

Windows 7 Access 2007
I have a form that I want to put a calculated date in. The value is based on a table called Dues, and is the max date for the DatePaid field in the Dues table.

The value is not to be editable and I don't want to run a query to get it. The purpose of the field is to show if the member has paid for the current year.

Can that be done?

I have included a sample here.

 

[pbaldy]

How about a DMax()?

 

[cyanidem]

SomeLabel.Caption = DMax("DatePaid", "Dues")

 

[jdraw]

Not exactly sure if you want the Max (DatePaid), or the Max(DatePaid) for each Member.

But you might try DMax("DatePaid", "Dues") as a starting point.

Wow! You guys are fast. I need some typing lessons.....

 

[ka3pmw]

It needs to show the max date for each member as their record is pulled up in the Look Up form. I am not clear on just how to put it in.

 

[pbaldy]

http://www.mvps.org/access/general/gen0018.htm

 

[ka3pmw]

I am getting #Name? in the field. my control source on the form looks like =DLookUp("DatePaid","Dues","Criteria = #" & [Forms]![Dues]![DatePaid] & "#")

Am I putting it in the wrong place or improper syntax?

 

[pbaldy]

"Criteria" would be the name of your field. I would expect it to be a member ID field or something, not a date field. I'd also use DMax()

 

[ka3pmw]

I changed it to =DLookUp("DatePaid","Dues","Criteria = #" & [Forms]![Dues]![RosterID] & "#") and still get the same error.

 

[pbaldy]

Try this, which incorporates the things I mentioned.

=DMax("DatePaid","Dues","RosterID = " & [Forms]![Dues]![RosterID])

 

[ka3pmw]

=DMax("DatePaid","Dues","RosterID = " & [Forms]![Dues]![RosterID]) gives me the same error.

I am putting this in a field on a form that is not part of the Roster table. I am putting it in the Data Control Source using the ... part that brings up the expression builder.

Is that the correct place?

 

[pbaldy]

Sounds correct. Can you attach the db here?

 

[ka3pmw]

Here it is. I have 2 records in it. Both should show 2015 as the last dues paid. Feel free to play with it. The actual database with 140+ records was not sent. I just saved it as this file then deleted all but two records.

 

[pbaldy]

Looks like it's working fine. If you use the Dues form as a subform, the syntax to refer to it changes.

http://www.mvps.org/access/forms/frm0031.htm

 

[ka3pmw]

I'm still getting the same error. The field it should be putting it in is the Last Paid which is under the Joined label.

I must be missing something.

 

[pbaldy]

Helps when you post what you tried. This works:

=DMax("DatePaid","Dues","RosterID = " & [Forms]![look up].form![Dues]![RosterID])

 

[ka3pmw]

OK, I see now. If there is no date for any year in the dues, it says Error.

It would be nice to do something like 00/00/0000 if they have never paid.

Although, I am not the treasurer. She will have to put the dates in.

 

[pbaldy]

Untested, but try

=Nz(DMax("DatePaid","Dues","RosterID = " & [Forms]![look up].form![Dues]![RosterID]),0)

which with date formatted textbox should give you the seed date in 1900 or whatever.

 

[ka3pmw]

Got the #ERROR message but the others with dates in them are ok.

 

[pbaldy]

Ah, because the subform is empty. Why don't we refer to the ID from the main form?

=Nz(DMax("DatePaid","Dues","RosterID = " & [Forms]![look up]![RosterID]),#1/1/2000#)