Update Field Based on Value of Segment of Another Field

[Guest]

I need to write an update query that calls a module, and I can't figure out how to set up a module. I've successfully coded a ton of forms, though this is proving very difficult for me.

The table (tblDataBT) contains fields such as: Cost, Unit1, Description, Date which are to be updated with segments of data from field "Source" which is located within the same table, depending upon the value of the first 6 characters of "Source"

I was hoping to use select case, because it's the most straightforward for me:


Select Case left([Source],6)
Case 102790
Cost=mid([Source],20,10)
Case 100513
OCC_Q=rtrim(mid([Source],27,15)
UNIT1=mid([Source],19,11)
UNIT2=mid([Source],63,11)
UNIT3=mid([Source],96,11)
UNIT4=mid([Source],107,11)
UNIT5=mid([Source],118,11)
UNIT6=mid([Source],129,11)
'many more cases will be included
End Select

Can anyone show me how to start the module? Basically, I need to know how to get the module to understand the fields within the table. Alternatively, if there is an efficient way to accomplish the following in a query without creating, then calling, a module, please let me know.

Thanks!

 

[Paul Ridings]

Create a new module in your database and open it.and add in the following
public function:-

Public Function CheckSource (Source as String, ID as String) as Variant
CheckSource = Null
Select Case left([Source],6)
Case 102790
' This is Cost
CheckSource=mid([Source],20,10)

Case 100513
'ID is required to identify the section of the string to look at
If ID = "OCC_Q" then CheckSource=rtrim(mid([Source],27,15)
If ID = "UNIT1" then CheckSource=mid([Source],19,11)
If ID = "UNIT2" then CheckSource=mid([Source],63,11)
etc
'many more cases will be included
End Select
End Function

Your update query would look something like this :-

UPDATE tblDataBT SET Cost=CheckSource([Source],"Cost"),
OCC_Q=CheckSource([Source],"OCC_Q"), UNIT1=CheckSource([Source],"UNIT1") etc
etc

Hope that helps

Paul

I need to write an update query that calls a module, and I can't figure

out how to set up a module. I've successfully coded a ton of forms, though
this is proving very difficult for me.

The table (tblDataBT) contains fields such as: Cost, Unit1, Description,

Date which are to be updated with segments of data from field "Source" which
is located within the same table, depending upon the value of the first 6
characters of "Source"

I was hoping to use select case, because it's the most straightforward for me:


Select Case left([Source],6)
Case 102790
Cost=mid([Source],20,10)
Case 100513
OCC_Q=rtrim(mid([Source],27,15)
UNIT1=mid([Source],19,11)
UNIT2=mid([Source],63,11)
UNIT3=mid([Source],96,11)
UNIT4=mid([Source],107,11)
UNIT5=mid([Source],118,11)
UNIT6=mid([Source],129,11)
'many more cases will be included
End Select

Can anyone show me how to start the module? Basically, I need to know how

to get the module to understand the fields within the table. Alternatively,
if there is an efficient way to accomplish the following in a query without
creating, then calling, a module, please let me know.