My First Module

[Guest]

I need to write an update query that calls a module, to assign values from a string of characters to another field. I've never used modules before, and using a query with a nested Iif statement doesn't seem to be the way to go. I've successfully coded a ton of forms, though this is proving very difficult for me

The table that stores the original and final data (tblDataBT) contains fields such as: Cost, Unit1, Description, Date. Those fields, among others, are to be updated with segments of data from field "Source" [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 10279
Cost=mid([Source],20,10
Case 10051
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 include
End Selec

Can anyone show me how to start the module? For instance, how do I tell the module the table (or maybe query of the table because of I'm using part of the field) that I'm referring to.

Thanks

 

[Chris]

First some terminology
A module is a collection of routines and functions (among
other things), and cannot be called by a query.

A function within a module can be called by a query.
So you need something like this...

Function DoIt(Source as variant, lngStart as long,
lngLength as long) as string

if isnull(Source) then
DoIt = "?"
exit function
end if
Select Case left([Source],6)
Case 102790
DoIt=mid([Source],lngStart, lngLength)
Case 100513
DoIt=rtrim(mid([Source],lngStart, lngLength)
'many more cases will be included
End Select
end function

You would then call the function once for every conversion
....

Select
DoIt([Source],20,10) as cost,
DoIt([Source],27 15) as OCC_Q,
etc.

Note that this will be VERY SLOW.

I would not use a query for this, I would do it entirely
with code.

-----Original Message-----
I need to write an update query that calls a module, to

assign values from a string of characters to another
field. I've never used modules before, and using a query
with a nested Iif statement doesn't seem to be the way to
go. I've successfully coded a ton of forms, though this
is proving very difficult for me.

The table that stores the original and final data

(tblDataBT) contains fields such as: Cost, Unit1,
Description, Date. Those fields, among others, are to be
updated with segments of data from field "Source" [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? For

instance, how do I tell the module the table (or maybe
query of the table because of I'm using part of the field)
that I'm referring to.