Help appeciated with query (1 Viewer)

[jd_boss_hogg]

Hi All - i would greatly appreciate some help with a query...

I have a text field, 2 samples here below...
CSLS7_06- SL Chrome Super High Res, 7" x 0.060"
CSLS12_09- SL Chrome Super High Res, 7" x 0.090"

This is a product, square plate with a thickness. I need to work out the square inch of the plate, so that i can then multiply it by the quantity field to find out the total sq ".

Question - how do i read this text field and pick out just the numbers i require? I presume i want to search the string for the first ' " ' character, and then use the 2 preceding ones, and change it to a numeric value so that i can use it in a multiplication.... ??

 

[plog]

You are probably going to need a few functions to accomplish this, so instead of jamming them all onto one line in a query, my advice for this is to create a function within a module. In the query it would look like this:

SquareArea: getSquareArea([YourTextFieldNameHere])

Then in the module you would write a function like this:

Function getSquareArea(dimensiontext)
    ' takes text, extracts dimensions and then calculates square feet
 
    ret=0          
    ' this will be value that is returned from function
 
Put Your Code Here
 
    getSquareArea=ret
    End Function

As to the code you need to write, you will need these functions:

Mid() http://www.techonthenet.com/access/functions/string/mid.php
This function will extract a substring from a text field

InStr() http://www.techonthenet.com/access/functions/string/instr.php
This function will determine position of specific characters within a text field

Val() http://www.techonthenet.com/access/functions/numeric/val.php
This function will convert a string to a number

 

[jd_boss_hogg]

Thanks Plog..... this is probably a dumbass question, but the first line in the module... where/what does (dimensiontext) represent?

Function getSquareArea(dimensiontext)

 

[plog]

Not a dumb question, its a little confusing until you see how it works. First and foremost, dimensiontext means nothing its just the name I created for a variable inside the function and it exists only inside that function. Unlike [YourTextFieldNameHere] you do not have to change it.

Think of the process as a truck shipment from your query to your function. You load the truck like this:

SquareArea: getSquareArea([YourTextFieldNameHere])

[YourTextFieldNameHere] is the name of the cargo which is the text value: 'CSLS7_06- SL Chrome Super High Res, 7" x 0.060"'

Now it gets to the function but the function has no idea original field name was, nor does it care or need to. All it gets is ''CSLS7_06- SL Chrome Super High Res, 7" x 0.060"' and loads it into a container called dimensiontext. Now throughout the function whenever you need to reference the value that you passed to the function you use dimensiontext.


For a hands on example replace "Put Your Code Here" in the function with this:



ret=Mid(dimensiontext, 1,5)

Save it and run your query and you should get the first 5 characters of the field you passed it. Now from here, modify your code until it returns what you want.

 

[jd_boss_hogg]

Sorry, still having grouble actually calling the module? It returns "undefined function 'getSquareArea' in expression ".... which to me sounds like it can't find the module ? My query is ...

Expr1: getSquareArea ([NOTES])

and my module is

Function getSquareArea(dimensiontext)
    ' takes text, extracts dimensions and then calculates square feet
 Dim sizenum As Double
 Dim sizestring As String
 Dim pos As Integer
 
    ret = 0
    ' this will be value that is returned from function
 
    pos = InStr(1, dimensiontext, "x")
    sizestring = Mid(dimensiontext, pos - 4, 2)
    sizenum = Val(sizestring)
    ret = (ret * ret) * 2.2

    getSquareArea = ret
    End Function

I'm aware the working out of the code is wrong at the moment, just waiting to get it called correctly before i then finalise the actual coding/maths.

 

[plog]

That looks correct. Can you upload a copy of your database?