Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 30th, 2005, 11:12 AM
Authorized User
 
Join Date: Sep 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update query conundrum!

Hi,
Here is the task:
I need to update field 1d in Table 1 with the two concatenated field values (there are good reasons for doing this). However, the two fields are not necessarily from the same table. If field 2b from Table 2 contains a value, then field 1d is "field 2b & field 1c". If field 2b is empty, then field 1d is "field 1b & field 1c". I am doing it by a VBA statement and function, as below:

strSQL = UPDATE Table 1 LEFT JOIN Table 2 ON Table1.1b = Table2.2a SET Table1.1d = MakeField(Table2.2b, Table1.1c, Table1.1b)

Function MakeField(2b, 1c, 1b) As String

If 2b > "" then
MakeField = "2b & 1c"
Else
MakeField = "1b & 1c"
End If

Table Diagram:
Table 1 Table 2
1a 2a
1b 2b
1c 2c
1d

The tables are linked by 1b-2a

Here is the problem:
The statement only works where the tables are linked! I thought that the LEFT JOIN made all the records on Table 1 updatable but it performs the task as if it were using an INNER JOIN.

Any ideas would be very appreciated as I have spent hours staring at the problem and performing various experiments!!

 
Old March 30th, 2005, 01:03 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hey,

   Your function is going to need to be a little more phistimicated. :D

   It sounds like you are doing all of this in a query.

   I suppose you could put this function on a button's on click event. Then open the first table as a recordset, run through each record and compare it to the second table (as a recordset) and then do your checking for null in the second table, and concatenate based on the results. Then perhaps put the results in a hidden table, run the update query based on the hidden table, and then clear the hidden table.

   You know, there is more than one way to skin a cat in VBA, and I am way sure that another user could come up with a more elegant solution. But if you want to pursue this one, lemme know.

HTH

mmcdonal
 
Old March 30th, 2005, 01:56 PM
Authorized User
 
Join Date: Sep 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks,
Geeeeez! Sounds a bit convoluted but may execute quickly enough. Can you give me some "skeleton code?
I appreciate it.
Tim

 
Old April 1st, 2005, 02:03 PM
Authorized User
 
Join Date: Sep 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I worked it out.

 
Old April 1st, 2005, 02:04 PM
Authorized User
 
Join Date: Sep 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I worked it out.
Thanks






Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Query rsm42 ASP.NET 1.0 and 1.1 Basics 0 April 9th, 2007 09:32 AM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Update query edcaru Access 3 June 14th, 2004 03:04 AM
Excel VBA - Wordwrap conundrum Shack Excel VBA 1 May 21st, 2004 06:12 AM
AutoNumber Conundrum enterbase Access VBA 4 January 28th, 2004 05:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.