Get part value from Postcode to create a cost (1 Viewer)

[hullstorage]

Hi all,

I have a form which has a number of fields and 2 of them are called [Postcode] and [Cost].

The cost field is defaulted to "£75.00"

What I am trying to is after the user enters the postcode it looks at the first and second value and if they enter a postcode in Scotland like below it changes the cost field accordingly.

The changes will only effect postcodes in Scotland if that makes sence
as we have 2 different pricing areas for these

examples of some postcodes
AB1 1AU £125.00
EH1 1RT £85.00
G1 1AT £85.00
IV5 1ER £125.00
ML1 1RT £85.00
and so on

so any postcode containing the following would update

AB = 125
EH = 85
G = 85
IV = 125
ML = 85
DD = 85
FK = 125
etc etc

all other postcodes in the uk will show the default value of £75.00

Thanks

Simon

 

[plog]

Default means 'starting value'. Should the user be able to change the cost value? Or should it always remain the default value? Is the cost something the user can actually edit or is it tied unequivocally to the postcode?

 

[jdraw]

Select Case Left(strPostCode, 2)
   Case "AB","IV","FK"
        Me.txtCost = "125.00"
    Case "EH", "G1","ML", "DA"
        Me.txtCost = "85.00"
    Case Else
        Me.txtCost = "75.00"
    End Select

 

[hullstorage]

Default means 'starting value'. Should the user be able to change the cost value? Or should it always remain the default value? Is the cost something the user can actually edit or is it tied unequivocally to the postcode?

Hi there, yes the cost field can be changed to any value.

Thanks

 

[hullstorage]

Select Case Left(strPostCode, 2)
   Case "AB","IV","FK"
        Me.txtCost = "125.00"
    Case "EH", "G1","ML", "DA"
        Me.txtCost = "85.00"
    Case Else
        Me.txtCost = "75.00"
    End Select

how would i use this code and where would i place it etc??

thanks

 

[hullstorage]

i notice you have used G1, problem there is that there is G1, G2, G3, G4, etc upto G76 ?

 

[jdraw]

The code is vba. It could be placed in a procedure.
If you want to use this sort of thing in a query, you could convert the code to a function as below.
You call it with the 2 leftmost characters of the Postcode and it returns the appropriate cost as a number.
eg

use it in a query such as
SELECT PostCode,PostCost("EH") from YourTableName

  Function PostCost(Postal2 as string) as Double
   Select Case Postal2
   Case "AB","IV","FK"
       PostCost = 125.00
    Case "EH", "G1","ML", "DA","G2","G3","G4","G5","G6","G7"
        PostCost = 85.00
    Case Else
        PostCost =  75.00 
    End Select
End Function

Note: To compare 2 characters, you have to supply 2 characters.

"G" doesn't satisfy nor does "G76"

 

[hullstorage]

i used to use something like this but cant seem to get this to work anymore

afterupdate event i think it was
[postcode] Like "AB#*" Or [postcode] Like "DD#*" Or [postcode] Like "DG#*" Or [postcode] Like "EH#*" Or [postcode] Like "FK#*" Or [postcode] Like "G#*" Or [postcode] Like "HS#*" Or [postcode] Like "IV#*" Or [postcode] Like "KA#*" Or [postcode] Like "KW#*" Or [postcode] Like "KY#*" Or [postcode] Like "ML#*" Or [postcode] Like "PA#*" Or [postcode] Like "PH#*" Or [postcode] Like "TD#*" Or [postcode] Like "ZE#*"

 

[jdraw]

You could use it in a beforeUpdate event.

But I gave you a Function that you can use from vba or in a query.

You do realize that this

Left(strPostCode, 2)

represents the leftmost 2 characters of strPostCode.

 

[Simon_MT]

Actually there are a number of formats:

Pos: Space:
2 3 E2 5BN
3 4 E12 5BN
2 4 EC2 5BN
2 5 EC12 5BN
2 4 E1A 5BN
2 5 EC2A 5BN

So to get to the Post Cost determination can be a little tricky.

Simon