VBA code to do VLOOKUPS/Rough Matching

[MatthewH]

I have used VBA quite a lot before for Microsoft Excel, but only once before (terribly) for Access. Please bare with me as I try to explain what I intend to do with this code!!

I'm looking to have the code run and do the following:
1) Run my Update and Append Queries
2) Start running the searches and lookups and putting out the answers I need.
3) Run the Select and Delete Queries to get rid of the unnecessary information.

I'm having a problem with #2 (as #1 and #3 are just DoCmd.OpenQuery and I should have no problem with those, though it may be extra queries but I'll learn to make it more efficient after I get it to run haha)

I want to update my main table [U-Pull] and its "B-Comments", "M-Date", and "M-Time" fields.
The way it will update this comments field is based on the following logic:
a) If "Party" in [U-Pull] is "PartyA" and "ID#" for that recordset IS found in [M-Pull].[ID#] (Second table and field), then change "B-Comments" to "Finished", change "M-Date" and "M-time" to the corresponding "M-Date" and "M-Time" in [M-Pull] under the same ID# (Like a DLOOKUP).
b) If "Party" in [U-Pull] is "PartyA" and "ID#" for each recordset IS NOT found in [M-Pull].[ID#], then change "B-Comments" to "Not found - Please try again tomorrow." and leave "M-Date" and "M-time" blank in [U-Pull].
c) I'm still figuring out the logic for the case where "Party" in [U-Pull] is NOT "PartyA" so I'm going to leave that unfinished for the time being as I try to follow the logic I've been using in my excel document.

I hope somebody can help me with the logic for a) and b) and I can hopefully have more on the rest of the logic by the time I get a response.

Thanks in advance and if you have any questions and/or need clarification, please let me know. I know it's a lot but I really do appreciate any help that can be provided!!!!

Thanks,
M

 

[Uncle Gizmo]

I've read your question three times now and I am not grasping the essence of it. I need a more of an overview of the problem so that I can think about it in context. I suggest you start by explaining a bit more about what you are trying to do not in a database or VBA or coding techniques but in a general way that anyone can understand.

 

[static]

Should just require a couple of queries.

a)
UPDATE [U-Pull] 
INNER JOIN [M-Pull] ON [U-Pull].ID = [M-Pull].ID 
SET [U-Pull].[M-Date] = [M-pull].[M-Date], [U-Pull].[M-Time] = [M-pull].[M-time]
WHERE [U-Pull].Party="PartyA"

b)
UPDATE [U-Pull] 
LEFT JOIN [M-Pull] ON [U-Pull].ID = [M-Pull].ID 
SET [U-Pull].[B-Comments] = 'not found'
WHERE [U-Pull].Party="PartyA" AND [M-Pull].ID Is Null

c) I'm still figuring out the logic for the case where "Party" in [U-Pull] is NOT "PartyA"

The above will only look for records matching "PartyA". Change the string as required

 

[MatthewH]

Should just require a couple of queries.

a)
UPDATE [U-Pull] 
INNER JOIN [M-Pull] ON [U-Pull].ID = [M-Pull].ID 
SET [U-Pull].[M-Date] = [M-pull].[M-Date], [U-Pull].[M-Time] = [M-pull].[M-time]
WHERE [U-Pull].Party="PartyA"

b)
UPDATE [U-Pull] 
LEFT JOIN [M-Pull] ON [U-Pull].ID = [M-Pull].ID 
SET [U-Pull].[B-Comments] = 'not found'
WHERE [U-Pull].Party="PartyA" AND [M-Pull].ID Is Null

The above will only look for records matching "PartyA". Change the string as required

I'm going to test the first one shortly but it looks as if it perfectly solves my problem (Just updating those 3 fields on my U-Pull table). The second one I'm having trouble understanding if you don't mind clarifying exactly how it works. I want it to run through every record and if there is NOT a match, leave Date and Time alone and just add that comment to U-Pull table.

None of these queries should change the data in M-Pull, right? I just want to be sure of that before I run it, that table is basically a master file of all the data.

 

[static]

I'm going to test the first one shortly but it looks as if it perfectly solves my problem (Just updating those 3 fields on my U-Pull table). The second one I'm having trouble understanding if you don't mind clarifying exactly how it works. I want it to run through every record and if there is NOT a match, leave Date and Time alone and just add that comment to U-Pull table.

UPDATE [U-Pull] ... Where [M-Pull].ID Is Null

Only U-Pull is updated if M-Pull ID is not found in U-Pull

None of these queries should change the data in M-Pull, right? I just want to be sure of that before I run it, that table is basically a master file of all the data.

Your data is your responsibility.

Never run any code you find on the internet unless you know what it does. And make regular backups.

 

[MatthewH]

Hey,

I have used the SQL statement for 1) and run into a problem. It's not properly populating (or populating anything for that matter.) I changed one entry so I know it would have to populate and it just ignored it.

It's also stating that it will update 144 records but my U-Pull is 181 records... and my M-Pull is like 10,000.

Does anyone know what may be causing this problem? Any help appreciated.

 

[static]

Best way to test things is on a small set of data.
Take a copy of the database, delete all but a few records and see if works.
If not, run a compact and repair and upload it here so we can take a look.

 

[MatthewH]

Best way to test things is on a small set of data.
Take a copy of the database, delete all but a few records and see if works.
If not, run a compact and repair and upload it here so we can take a look.

You're absolutely right it worked!! Now on to the next steps, I'll be sure to report back if I run into any more trouble (which is BOUND to happen....)

Thanks,
M