Unable to add new records - My Query has the flu!

[chiara]

I know what's wrong with my query, but I don't know how to fix it.

Attached is a screen shot of the relationships in my query. You'll notice I reference 5 tables, and 2 queries. The two Queries contain expressions for combining fields in the tables with date of birth and date of death info. For data entry I have separated day of birth (and death), month of birth (and death) and year of birth (and death). These queries combine those fields into date strings month/day/year so that I can use them in a complex calculation. (To find the exact age of a person upon death in years, months, and days.)

The problem I have is that while referencing these queries in the query I use for data entry, I can't add new records. I have discovered that if I remove those queries referencing expressions then I am once again able to add new records. However, if I remove them, then my calculation field on my form doesn't work!


Any ideas are welcome.
Also attached is a picture of my relationships for your reference. (Sorry if it's a little fuzzy, I had to resize it a tad in order to attach it.)

 

[The_Doc_Man]

Our site has a download filter so i can't look at your screen shots. However, I've run into a similar problem based on your description.

When a function references a field but the field itself isn't referenced in isolation, you sometimes have trouble updating things because Access doesn't know what to put in the field. The function is not reversible, you see. (Oh, you can write a function that theoretically has a reverse operation - but ACCESS cannot reverse the function. It cannot put in a result and get back the input needed by the function to GET that result, which is what I mean by reversibility in this context.) So Access doesn't know how to store anything. Now if the integrity rules for the record require something in that field, and there are not other data sources, Access cannot store anything there because it cannot supply the data for a required field.

Aggregate functions or a SUM query will do this every time (almost). Other functions have the same possibility.

 

[Kodo]

recommed removing # from your field names.. it's just a plain bad idea to use them or any special character including spaces in field names.

 

[chiara]

Does it matter that the function I'm using is only referenced in the text box on my form? It's not present in any query, it's merely using the results of an expression (which exists in a query) for it's calculation.

Does that make a difference?

Edit: Good advice Kodo, I think I may do that.

 

[chiara]

Just to clean house I did what you recommended Kodo. Also, I made a new query in which to run the calculation, rather than running it in a text box on the form. This way I only reference 5 tables and 1 query (instead of 2). This is working fine, except I still can't enter new records, and I'm thinking it's simply because the query that I'm using for data entry is referencing another query.... so I guess my main question is... how can I do this without referencing a query at all?

Or, is there something I can do so that I can still enter new records while referencing a query?

 

[chiara]

Problem with storing the dates as dates is that the entry may only be a partial date, such as a year without a month or day. In order to adapt to this I included "unknown" for the month, and 0 day and 0000 year. 0/0/1989 is not a valid date.

But, you say I can convert the separate data entry fields to a date and save them... how? Isn't that converting backwards? It would be like taking a field that is LastName_FirstName then making 2 different data entry fields for LastName and FirstName. I know how you can combine two fields, but I've never heard of splitting one. Can you give an example?

Actually, all the data entry is on one form (by request of the user), so the one query is necessary (as I have it set up now). However, I suppose I could do subforms. That might do the trick.

 

[chiara]

Thanks for all your help, I fixed it. Couldn't have done it without all of your advice.