#Name? problem again

[Dchall_San_Anto]

Surely this problem comes up more than I can find it when I search these archives.

I want a field in a form to display whether folks have paid their dues (Yess) or are late (Noo). Here is the expression I built by selecting what I thought were the right elements from the Expression Builder:

=IIf([New FRUA Table]![Last Payment]>[New FRUA Table]![This Dues Due],"Yess","Noo")

where
New FRUA Table is the name of the table I made using Make-Table Query,
Last Payment is the payment date of record (field type is Date),
This Dues Due is the due date of record (field type is Date), and
I purposely misspelled Yess and Noo as a diagnostic.

I thought the answer would be a Text type field but in the field properties, Text is not one of my choices. My only choices are number types (including dates) and the Yes/No group. Selecting Yes/No makes no difference. Of course the result I see in the form is #Name?

By the way, I might add that this is my first attempt to use Access. So far I feel great that I have gotten all the fields and all the records on this computer. Now I'm trying to redo my calculated fields. My only other database experience is in building this membership database on a Macintosh using FileMaker Pro - and that was years ago. So please be gentle with me on this.

 

[boblarson]

Make sure your controls are NOT named the same as your field names. If you used the Forms Wizard to create your forms, the controls will be named the same as your fields and calculated fields don't like that. Rename your controls, if they are the same name, to something like txtLastPayment, etc.

 

[Dchall_San_Anto]

At the risk of sounding really dense, what are controls, and where do I change them? I just searched the MS Access Help file for Control and got a bazillion hits which seem to thread through every single aspect of the program. Can you narrow it down for me?

 

[missinglinq]

You truly are a babe-in-the-woods (but then we all were at one time). Controls are those "things" you place on a form, i.e.

Check boxes
Combo boxes
Command buttons
Drop-down list boxes
Labels
List boxes
Option buttons
Option groups
Subforms
Subreports
Text boxes
Toggle buttons

You change their properties (such as their names) by Right clicking on one, selecting Properties, and finding the property you need. Since you're a beginner, select the property tab "All". This lists all the properties.

The "field names" are the actual names of fields in the underlying tables. As boblarson pointed out, the Access form wizard gives the controls the same name as the underlying field. While this works for most things, it does tend to befuddle the issue when some of the fields are calculated.

There's a naming scheme known as the Hungarian nomenclature, or some such, that many programmers use to name controls. I not sure if Help for Access lists this, but you can find it on-line with a search engine. A short list of the often used controls is as follows:

cmdName command buttons
txtName text boxes
cboName comboboxes
lstName list boxes
lblName label
frmName forms
fraName frames

This naming scheme actually does make things easier in the long run because you, or anyone familiar with VB/Access can look at code and immediately know what kind of control you're dealing with.

Hope this helps some.

The Missinglinq

 

[Dchall_San_Anto]

I changed the names of all the fields, buttons, and labels on the entire form and still have the #Name? error. Other ideas?

Babe-in-the-woods

 

[Pat Hartman]

You are not referencing the fields properly:

=IIf([Last Payment] > [This Dues Due],"Yess","Noo")

 

[Dchall_San_Anto]

BINGO! Tell him what he's won, Johnny!

I was referencing the values in the fields in the original Table. Apparently it wants values from the current Form, which, of course refer back to the original Table.

Well that ended two days of frustration. Thank you so much!