query expression problem in access 2010


I have a append query in which I use an expression to retrieve values to be appended. The query does not retrieve the values. I tried to reduce the query to one field and and ran it as a select, but still no luck.

here is the expression: Expr1: [Forms]![frm_sig_ic_questions_review]![leadership_factor] the leadership_factor does have a value.

I thought it might have something to do with nulls, but query will still NOT work if I use something like Date(). Here is the SQL code generated by ACCESS 2010:    SELECT Date() AS Expr1 FROM temp_tbl_sig_ic_titlepage;  

The query will work in ACCESS 2003.

Thank you for any help on this
Who is Participating?
als315Connect With a Mentor Commented:
Look at your sample. I've modified some fields on your form. Now there some data. Button run append query and records are added.
Rey Obrero (Capricorn1)Commented:
open your vba window
Tools > References

see if you have any reference prefixed with "MISSING"
uncheck and find the available reference
Your query should work in Access 2010. Can you create new DB and test this query?
SELECT Date() AS Expr1;
should work
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Is Date actually the name of the field you are retrieving?  If so, consider changing the field name to something that is not a vba function.

If that is not possible, wrap it in square brackets so that Access knows you are referring to a field and not today's date.

Select [Date]
From yourTable
allfredeneumanAuthor Commented:

Thank you for your responses. Unfortunately, none seem to work. I am not sure how to look at the VBA window. I don't know if ACCESS give me that window for a query like it does for a form.

I did get the date field to work. However when I tried to print a text field from an open form that I know is populated I have a problem. I attached some screen shots that may help. (jpg #3) I have verified that the field contains data and is the same type (text)

As I'm writing this I thought that I would get a text field from a form that is text and not a reference.

You don't need any table in your query if you like to add only one record. Is form frm_competency_variables opened in form view?
May be you can upload sample DB with this form and table?
allfredeneumanAuthor Commented:
respond to ala315 plus additional info on problem

The query I presented was a sample. The actual query requires getting data from a couple of 'open' forms. I figure if I get just one field to work I would be OK with adding the rest.

The database contains a bunch of proprietary info so I have a problem with uploading a sample database mostly because of the time it would take to strip out the proprietary info.

Below is some more information which may just be confusing the issue of writing data from a form to a temporary table in access 2010 which did work in access 2003.

The form "frm_competency_variables" is opened in form view. This is a temporary form which collects data from some other forms and tables. When I'm done collecting data into this form, I want to write it to a temporary table and then write it to a permanent table. I inherited this DB and don't know why I don't just write it to the permanent table in the first place. Maybe, I need to know the record number of the record appended to the permanent table so I can use this number in an index field in another table

Any help would be appreciated..
allfredeneumanAuthor Commented:
Sample database.

I made a sample database which includes the problem.
allfredeneumanAuthor Commented:
Mystery (half) solved!!!!!!

The temp title page table is recreated every time I run the main menu. The table is normally empty. I put some data in the table. I did not delete and build the title page table as is normally done in the main menu. I ran the forms and everything worked!!! The correct info was appended to the title page.

This does not really solve my problem though. It is a work around of sorts. I don't really like solutions for which there is no underlying logic.

Now here's the rub. I do something very similar with a questions table which is the detail records associated to the title page (table). I do not have any problems with this table. One big difference is that the question detail is created empty but records are written (save record) to it before the records are appended to the permanent question table

Here is my conclusion: I cannot use an empty temporary table to append a record to another table when the appended record is created from form data. The temporary table must contain at least one record before the append record is written using the temporary table.

Note: I have not tried to write a record directly to title page table simply because I didn't want to screw with an undocumented design which in hindsight was a mistake.
I don't understand your sample. All fields on form are referenced to other (missing) form. Please, make sample DB close to your original DB as much as possible. If your fields are bounded, they should be bounded also in sample and vice versa.
allfredeneumanAuthor Commented:
respond to als315

my sample is only intended to show that the query does not work.

1. run the query, enter as much data as you want when prompted.
2. look at the archive table for your input. it is not there.
3. enter a record directly into the temp table
4 do step 1
5 now when you look in the archive table you will find the data you entered in step 4 but not the data you entered in step 3. The data you entered into the temp table in step three is still in the temp table, but not and should not be in the archive table
6 do step 3 again. (now you have 2 records in the temp table.)
7 do step 1 again
8 now when you look at the archive able you will find one instance of the record you entered in step 4 and 2 instances of the record you  entered in step 7. You will have three records in the archive table but you only entered two! (step 4 and step 7. The first record in step 1 was never saved)

Conclusion: ACCESS will append the new record the number of times equal to the number of records in the temp table. This conclusion does not apply to ACCESS 2003

This makes no sense but that's way it is. No wonder ACCESS is not used extensively in business.

I think I'll call it a day on this question. I have a solution albeit somewhat inane.
Alfredeneuman (I love the name!  Just caught it)

Go ahead and accept your own comment as the answer to close the question if you are done here.
allfredeneumanAuthor Commented:

why does your query (query1) work but mine does not? by work I mean add 1 record?
I have no answer to "why", because have no real sample DB. May be Access 2010 don't like calculated fields?
You shouldn't have table in such query and you should make all calculations in query.
allfredeneumanAuthor Commented:

Thanks for the quick answer.

Where you using ACCESS 2003? 2007? or 2010?

Also, I did not understand "you shouldn't have table in such query. you should make all calculation in query". I looked at your query in design mode and noticed that you do not have a table defined in the query. Is that what you mean?

(PS Is my lack of experience showing too badly? I hope my boss doesn't subscribe to these. But then again she doesn't know me as alfredeneuman - just honey :)

Thanks again,
allfredeneumanAuthor Commented:
This whole thing is still a bit of a mystery but I have what I need to know to continue. Thank you for your help -Al
1. You are adding fields from some form, but have a table in query (FROM .... in SQL). Why you need it?
2. I've noticed it in form's field, where default value is =Date(). If I add this field to query, it is empty. If I add =Date() directly to query, it work. This needs some investigation, so I've asked real sample
I am using Access 2010
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.