Vba Function to use in queries (1 Viewer)

andrecarmo

New member
Local time
Today, 07:32
Joined
Sep 6, 2016
Messages
3
Hi, I need some help. I have a table called "Bridge" in which i have several columns. One of those columns is called "Prod_Pack_Code_CIH_OLD" that has one or more codes. For instance, one cell may be empty or it can have one code (p.e. 123456789) or can have several codes, separeted with ";" (p.e. 1234568;123548;12345648).
What i need is a function (to be used in a query), where i can check which code , one by one, is not present in one other table, that has a column that receives only one code by cell (The other table is called Products and the column is Prod_Pack_Code).
The function has to be in VBA and i´m kind of lost in here.

Thanks
 

plog

Banishment Pending
Local time
Today, 02:32
Joined
May 11, 2011
Messages
11,613
The function has to be in VBA and i´m kind of lost in here.

The easiest way to do this is by correctly storing your data. Each discrete piece of data in a database should be stored, discretely. You shouldn't be cramming multiple values into one field.

Even if this is just a one time deal, the best way is to do just that. You should build a function that goes line by line through all your incorrectly stored values and sends them to a new table to hold them properly. That means this:


BridgeID, Bridge.Prod_Pack_Code_CIH_OLD
1, "12345, 3812, 77, 19"
2, "77, 195"

Would become this:

GoodTable.BridgeID, GoodTable.Prod_Pack_Code_CIH_OLD
1, 12345
1, 3812
1, 77
1, 19
2, 77
2, 195

With that structure its a trivial query to find out which ones are not in another table. That's how you should store your data and that should be the goal of a function you write.

That function is going to include Recordsets and DoCmd.RunSQL to insert records into the new GoodTable I laid out.
 

Ranman256

Well-known member
Local time
Today, 03:32
Joined
Apr 9, 2015
Messages
4,339
I can make the function, but what do you want to return?
The field can only get 1 string. What do you do with the rest?
Your example shows 3 strings in the 1 field.
It can only return 1.
 

andrecarmo

New member
Local time
Today, 07:32
Joined
Sep 6, 2016
Messages
3
Hi, first of all i want to thank you both for your quick answers.
Well, in the query that i have to create, she will return the column id of every single row that has a code that not exists in the other table.
It has to search, code by code. Every single cell(Bridge.Prod_Pack_Code_CIH_OLD), of the main table, may have none, one, or multiples codes.
If i find one code that not exists on the Products table, she will give me the Id of the row. I will use an Inner join, on bridge.id = Products.id; the Where clause will have to use the function i need to search wich row has a code that does not exist in Products.
The fucntion must split by ";" and check each one of the code, one by one. If the result shows me one false, it returns me the row that i´m looking for.

Once again, thank you guys.
 

andrecarmo

New member
Local time
Today, 07:32
Joined
Sep 6, 2016
Messages
3
How do you suggest me to send one by one value? should i use an Array to store each line and send each one to a new table?
 

plog

Banishment Pending
Local time
Today, 02:32
Joined
May 11, 2011
Messages
11,613
No, this doesn't need an array. Just a few loops. This is some psuedo code of what that function should do:


load table into recordset
loop through recordset
--loop1--assign Prod_Pack_Code_CIH_OLD to a variable
--loop1--loop through variable for each unique semicolon in it
--------loop2--extract out unique value from variable
--------loop2--construct APPEND query and write that value to your new table with DoCmd.RunSQL
--------end loop2
--end loop1
 

Users who are viewing this thread

Top Bottom