Q about finding records

[Dchall_San_Anto]

I'm a babe in the woods on Access.

I have a club membership db that I'm moving from FilemakerPro, so I'm starting over with many of the fields. I have a field on a form that figures out whether the dues are paid and iif they are, it pastes in the word, "Member," otherwise, "Non-member." When I want to make mailing labels for current members, how do I find the current members? The only actual notation to search on is in that field that does the comparison of date fields.

Should I create a new field in the table that looks at the pay date and versus the due date and fills in the Member or Non-Member status, so I can search on that field? I'm not sure how to do that??

I wish someone would write, "MS Access for Dummies, for Dummies"

 

[dcx693]

You don't say what the criteria is for a person to be a member. But in any case, you don't need (or want) an extra field in your table. Create a query with a field that does the calculation for you and base your mailing label report on the query. Have it print out only those people who are members.

 

[Pat Hartman]

Rather than keeping a field that says "member" or "non-member", you should keep PaidThroughDate. That way you don't have to worry about changing the status field when a member's paid period expires.

To tel if a person is a member look for PaidThroughDate >= Date().

 

[Dchall_San_Anto]

I like the paid through date idea. I've been doing it the other way. The criterion for membership is having paid your dues. Dues are due on January 1 for the next year. If you pay on December 31, dues are due the next day (theoretically if I want to be a jackas*s about it).

Remember I'm really new at this. When I create a query, the results seem to want to be stored in a new table which has to have a name and all that. Then it gets stored with the info from that query dated from whenever I ran the query. Surely databases are more sophisticated than that. What is the basics of querierueing that I'm missing?

And how do I run the query from the form? I assume a button. What I was doing was clicking on the membership field in the form and hitting the filter button. Nice and easy, but I never could figure out how to automate that either.

 

[Pat Hartman]

The only kind of query that permanently stores data is a make-table query. It creates a new table from the data selected by the query. If all your queries are asking to make a table, you must be using a make-tabke query as a sample. Try making your queries from stratch using the query design view.

There is no need to "run" the query. It is not an update query. It is a simple select query that includes a calculated column. You can use the query as the recordsource for a form or a report.

 

[Dchall_San_Anto]

Thank you so far. I'm making progress in very small strides.

I did make a query that doesn't make a table, so I'm counting every success. That Access Help is next to useless for someone at my level of proficiency, by the way. I used the following expression in the Field column of my Query...

Expr1: IIf([dtfldLastPayment]>[dtfldThisDuesDue],"Member","Non-Member")

The fields are named exactly as they appear in the table I'm using. Then in Criteria I put "Members" and it works great...as long as I'm in the query mode. Then I went back to the Form I've been working on to add a button (btnFindMembers) that runs this new membership query. When I did that, I got the following notice.

The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: btnFindMembers_Click.

*The expression may not result in the name of a macro, the name of a user-defined function, or {Event Procedure].
*There may have been an error evaluating the function, event, or macro.

At the same time, every field in my form that has a calculated expression in it has the #Name? error in it. They don't seem to go away after removing the button and restarting Access.

When I started writing the expression in the Query, I copied this following expression from my form:

=IIf([dateLast Dues Collected]>[dateThis Dues Due],"Member","Non-Member")

Notice the slight difference in the names of the Fields from the Form to the Query. I'm not sure why they are different, but these names were what it took to make it work (originally) in the Form view and the Field names up above were what it took to make the Query work in the Query view. As far as I can tell, there are no two names alike anywhere other than the fact that the Form and the Query are both supposed to be attached to the same table.

However, I've also noticed that when I'm in the Form design view, and I'm in the Expression Builder for my calculated expressions, on the left side of the bottom of the window, I used to see the name of the table I'm linked to as the top folder. Now I see the name of the form as the top folder. I'm 99% certain it used to be the table name there. Am I dreaming about that? Because using the specific names in that folder seemed to be the key to getting my expressions built right in the first place.

Any ideas as to what's up with this?

 

[Pat Hartman]

Lots of confusion here. Help (Access or otherwise) is not really intented to teach you. It is intended to clarify syntax and answer questions. The problem that newcomers face is that they don't know what question to ask. Office help is more confusing than necessary because it is broken up into GUI and VBA sections and so you get different (or no) responses depending on whether you ask from the main Access window or from the VBA editor window. Whey you want guidence, try using the table of contents and drilling down (different entries will be found in GUI and VBA lists). You'll have much better success.

Controls on forms/reports have a Name property (you can see it on the Other tab of the property dialog). These controls also have a ControlSource property. When you build a form using a wizard, the wizard will assign the bound field name from a control's ControlSource as the value for the Name property. This causes subtle problems when you move into VBA code and calculated expressions. For example, if you just change the controlSource of a formerly bound control to an expression, your form will show #Name. This is because you have confused Access. To resolve the problem, change the value of the Name property to something other than the name of a bound field.

For future reference, do NOT use embedded spaces in any object names. They require the object name to be surrounded with square brackets. Plus, when you build events for fields with these names, VBA MUST change the field name and replace the spaces with underscores which causes real confusion.

 

[Dchall_San_Anto]

Pat I really appreciate your helping with this.

As far as I remember I didn't embed any spaces. I picked those names off a list that was included in the expression builder. Obviously I did embed spaces, but I don't recall it. And when I tried picking the names directly from the table, I got longer expression that included the table name with a colon and the expression didn't work. That was the subject of another question you might have been in on several days ago.

Anyway, as far as I can determine, the name properties on the form were all made up as I went along. I rechecked and they are all nonsensical relative to any field names.

I just tried recreating one a text box with a new created expression and get the same #Name? problem.

 

[Pat Hartman]

Are you preceeding the expressions with the = sign?

You may need to start again. First create a query that retrieves the information you need. Then using the wizard, create a form based on that query.

 

[Dchall_San_Anto]

Yes I'm using the = sign, just like in the example I posted.

Start over huh. I'm certainly glad I wasn't 95% finished with it (where's that emoticon for dripping sarcasm?).

I'll give that a whirl. Sure would like to know where I went wrong. I'll try to pay close attention to the names of the tables and stuff this time.

Thanks again.

 

[Dchall_San_Anto]

Okay. Well that was fun. I started a new form. I don't think I did it quite the way you suggested, but it led me to the source of the problem. I had a couple of false starts in making the button to find my membership. Apparently it wrote visual basic code for each false start and saved it. Each one carried a subroutine with the same name. When I deleted the duplicates, it sort of worked. So I flipped back to my original form and it no longer had the #Name? in my calculated fields. So that's good. For some reason the first several times I was never carried over to the VB code to check it out. When I started the new form, as soon as I clicked the button, I was sent to VB to review the code.

Then I deleted all references to the button and started over again. It works now, sort of. What I had expected was that the query would result in giving me the names of members on the same form - like what happens when you run a filter. Instead I get the result in a table format like was designed in the query. Can I get the result in the form format?