?
Solved

query expression problem in access 2010

Posted on 2013-01-26
17
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 40

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 40

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
 
LVL 40

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 40

Accepted Solution

by:
als315 earned 1500 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 40

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 40

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

719 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