Update Query that ignores missing fields?

[Barry Stuckman]

Automatically handling "Enter Parameter Value" prompts?

EDIT: I think my original post may have been detailed to a fault and probably crossed-over into confusing. So I've all but rewritten my post in an effort to make it as clear and concise as the best of my abilities allow for. I apologize to anyone whose time I've wasted and urge you to review the update with a fresh set of eyes (and patience). But just in case I completely missed my mark, I added 2 database files and a diagram highlighting my desired results. Please take a look at them before replying. Thank you.

Hello party people!

I'm trying to create a single, Update Query in Access 2007 that does all of these 3 things:

1. Update (skipping zero-length values)
2. Append
3. Automatically handle missing Fields without showing any "Enter Parameter Value" prompts.

I've managed to accomplish the 1st & 2nd items on my own, but I'm stumped on the 3rd. In essence, what I'm looking for is a way of telling Access to automatically use a zero-length/null value for any missing Field(s) the Query encounters, so long as it does not involve manually modifying the Table beforehand. Automation is key. And that's where you guys come in.

Attached, is a diagram of the desired results applied to a set of sample Tables. And here's the SQL code for the Update Query adjusted to work with those sample Tables:

UPDATE Table_1 RIGHT JOIN Table_2 ON Table_1.ID = Table_2.ID SET Table_1.ID = NZ([Table_2].[ID],[Table_1].[ID]), Table_1.Company = NZ([Table_2].[Company],[Table_1].[Company]), Table_1.Address = NZ([Table_2].[Address],[Table_1].[Address]), Table_1.Phone = NZ([Table_2].[Phone],[Table_1].[Phone]);

Any help would be greatly appreciated.

 

[arnelgp]

I dont see any problem in your query, maybe its a parameter query. On that case just go and edit your query using query designer and click Parameters on the ribbon and delete the parameter there and see what happens.

 

[Barry Stuckman]

I dont see any problem in your query, maybe its a parameter query. On that case just go and edit your query using query designer and click Parameters on the ribbon and delete the parameter there and see what happens.

Hello and thank you for replying. It appears that my original post did not describe my end goal as clearly as I thought it would, so I made several changes to it and even added 2 database files to aid in understanding my problem. Please, have a second look and let me know what you think.

I apologize for any confusion I may have caused.

 

[arnelgp]

your table_2 table on "not working database" has no phone field you included this in your query. Only table_1 has phone field.

 

[Barry Stuckman]

your table_2 table on "not working database" has no phone field you included this in your query. Only table_1 has phone field.

Hello again and thank you for replying. I appreciate you pointing that out, however, I am very well aware of this, as that is essentially the whole reason I'm asking for help here.

3. Automatically handle missing Fields without showing any "Enter Parameter Value" prompts.​

I find it hard to believe that a language as powerful as SQL has no way of creating an Update Query with optional Fields or pre-defining a parameter to use in the event it cannot find a Field. Especially considering that it was specifically designed for databases, coupled with the fact that VBA (yet another powerful language) could aid in the process.

 

[Uncle Gizmo]

I don't think I have quite grasped the essence of your question so I thought it best to explain my understanding of it before proceeding further.

You have an update query designed to update several fields in a table, however it is possible that the table may not contain a particular field at the time you want to update it.

Specifically your update query is written to update the telephone number field however the update query fails because the table does not contain a telephone number field.

Is this a correct interpretation of your question?

 

[Barry Stuckman]

Hello kind sir and thank you for your assistance. I will try my best to make helping me as painless as possible. Please bear with me.

You have an update query designed to update several fields in a table, however it is possible that the table may not contain a particular field at the time you want to update it.

That is a mostly accurate interpretation of my intended use.

I have an Update Query designed to update a "Main" Table with data in an "Updates" Table. The Fields in the "Main" Table are fixed, however the "Updates" Table is generated on a periodic basis and will most likely be missing 1 or more Fields that the Update Query will look for. This is what causes the "Enter Parameter Value" prompt as described in an article titled Parameter of Type Text is Evaluated Wrongly by Allen Browne. (Can't post links yet, so that's the best I can do for now.)

Specifically your update query is written to update the telephone number field however the update query fails because the table does not contain a telephone number field.

That is an accurate description of the problem and the cause, specific to the sample Databases I provided.

The 2 Databases I attached to my original post are merely examples that contain just enough data to display A. The unwanted behavior (Not Working.mdb) and B. How insignificant of a change is required in order to achieve my desired results (Working.mdb). The only difference between the two being—as you pointed out—a missing Field that contains no data whatsoever.

Is this a correct interpretation of your question?

I guess the easiest way to visualize what I'm asking is:

• Q. How can I get the post-Query results of Working.mdb using the two Tables from Not Working.mdb without getting any "Enter Parameter Value" prompts when the Update Query is run, in such a way that does not involve manually modifying the Tables?

Examples of acceptable solutions:

• SQL/VBA code that allows me to declare a default value to use for any missing Fields the Query finds.
• SQL/VBA code that omits Fields from the Query if they are missing.
• VBA code that can temporarily tell Access to shut up and quit yer whinin'.

I hope I've actually helped clear things up for you and not just complicated them even more. In any case, there's always the diagram. It depicts exactly what I'm trying to do: Get from Point A to Point B without any prompts. Easier said than done? Most definitely. This thread has almost 300 views and you're only 1 of the 2 brave souls to take a stab at it.

Thanks again. I look forward to your reply.

 

[Uncle Gizmo]

Please explain why the table sometimes has the telephone number field and sometimes does not have the field? It seems that correcting here would solve your problem.

 

[Barry Stuckman]

Hello again and thank you for your quick response.

Please explain why the table sometimes has the telephone number field and sometimes does not have the field? It seems that correcting here would solve your problem.

The information that can be collected varies depending a number of external factors. As a result, certain Fields will be completely left out, as they have no data. Also, in practice, there's a lot more than just 1 "Main" Table and "Updates" pair, which is why automation is so important. Keep in mind that the 2 Databases I provided, along with the data structure and all of the data therein, are just examples meant for visualization purposes only.

On a more comprehensive note, am I to understand there is no way to programmatically handle missing fields during a Query in Microsoft Access using SQL or VBA?

 

[jdraw]

Barry,

Have you considered reading the table(s)fields definition(s) and comparing these to the fields you intend to update? If there are fields you wish to update that do not exist in the table, then drop them from the update, but output a message or some other means to communicate the facts/condition to you. This wouldn't be a simple query, but could be done in a function/procedure that did the revised update and flagged the condition you mention.

 

[Uncle Gizmo]

Are you dealing with tables within MS Access?

 

[Barry Stuckman]

Barry,

Have you considered reading the table(s)fields definition(s) and comparing these to the fields you intend to update? If there are fields you wish to update that do not exist in the table, then drop them from the update, but output a message or some other means to communicate the facts/condition to you. This wouldn't be a simple query, but could be done in a function/procedure that did the revised update and flagged the condition you mention.

Hello and thank you for replying.
Unfortunately, I don't know VBA, so I wouldn't know how to go about doing that. Could you, perhaps, quickly throw something together that would work for the sample Database I provided?

Are you dealing with tables within MS Access?

Yes, sir.

 

[Uncle Gizmo]

It seems very strange to me that you have a situation where you are not aware of the number of Fields within a table you are operating on, particularly as you have created an update query for said table. In other words you must have some idea of the structure of the table or you would not be able to create the update query.

You indicate that you want to automate this update process, you also indicate that there are unknowns in the fields, and I don't just mean that you might have a missing telephone number field I'm thinking that you may have other Fields missing or not named the same as you would expect them to be named. In other words it sounds like you are importing spreadsheets from various sources where the people creating the spreadsheets are in the habit of modifying them when really they shouldn't.

I'm thinking that instead of messing around with the update query you should be concentrating on making sure the tables are in the same correct format, because not having them in the same format means that anything else you do with those particular tables will need to be modified as well.

 

[Barry Stuckman]

On a more comprehensive note, am I to understand there is no way to programmatically handle missing fields during a Query in Microsoft Access using SQL or VBA?

Could you please answer my question? Thank you.

 

[MarkK]

Could you please answer my question? Thank you.

The answer is No.
hth

 

[jdraw]

Barry,
Here is a modified version of your NotWorking database.
It contains a custom set up to do what you asked
-create and run an Update query to update matching fields in table1
and table2. This routine determines the common fields and builds an update query to update fields in table1 with field values from matching field names in table2. As per your example, the join is done on the ID (first field) in each of the tables.

There is an opening form describing the process and a button to Run the process and update query.

In module data dictionary, the routine that does it all is called DoIt.

Good luck.

I agree with the others that this is not something I'd expect SQL or any non custom code to do. You would not request an update to a field that you know doesn't exist.
I think from database principles (Codd etc) that you would interrogate the catalog
to identify available fields in a custom process --which is what I did (sort of).
I hope it's useful to you, but you may consider reviewing your requirement to a more conventional approach.