Solved

query expression problem in access 2010

Posted on 2013-01-26
17
361 Views
Last Modified: 2013-01-30
hello,

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
0
Comment
Question by:allfredeneuman
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38823573
open your vba window
Tools > References

see if you have any reference prefixed with "MISSING"
uncheck and find the available reference
0
 
LVL 39

Expert Comment

by:als315
ID: 38823576
Your query should work in Access 2010. Can you create new DB and test this query?
Even:
SELECT Date() AS Expr1;
should work
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38824055
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
Etc...
0
 

Author Comment

by:allfredeneuman
ID: 38824843
Hi,

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.

Thanks
1-Simple-Query.jpg
2-Simplier-query-that-works.jpg
3-Simple-query-that-does-not-wor.jpg
0
 
LVL 39

Expert Comment

by:als315
ID: 38825752
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?
0
 

Author Comment

by:allfredeneuman
ID: 38827809
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..
0
 

Author Comment

by:allfredeneuman
ID: 38829443
Sample database.

I made a sample database which includes the problem.
Database1.accdb
0
 

Author Comment

by:allfredeneuman
ID: 38829548
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.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 39

Expert Comment

by:als315
ID: 38829808
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.
0
 

Author Comment

by:allfredeneuman
ID: 38831860
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38831876
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.
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 38831986
Look at your sample. I've modified some fields on your form. Now there some data. Button run append query and records are added.
Database1--2-.accdb
0
 

Author Comment

by:allfredeneuman
ID: 38836359
als315,

why does your query (query1) work but mine does not? by work I mean add 1 record?
0
 
LVL 39

Expert Comment

by:als315
ID: 38836495
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.
0
 

Author Comment

by:allfredeneuman
ID: 38836682
als315,

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,
Al
0
 

Author Closing Comment

by:allfredeneuman
ID: 38837879
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
0
 
LVL 39

Expert Comment

by:als315
ID: 38838489
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now