Using field to give result

[funkytunaa]

I have a db with a few tables.

1/Client Details
2/PostCodePointValues
Etc.

What I need is to have a query that will return a value from the PostCodes dependant on what the Clients Post code is.

I have currently Opened a query with the following fields.

Client Details - [PostCode]
PostCodePointValues - [From]
PostCodePointValues - [To]
PostCodePointValues - [Points]

What I need to do is return the number of points if the PostCode falls inbetween PostCodes From and To.

I have done an iif statement (Below) but it is coming up with an error, any ideas would be appreciated.

PCPoints:iif([PostCode]>=[From],iif[PostCode]<=[TO],[Points],""),"")

Any direction at the moment is good direction because it will be steering me away from total brain failure.

Cheers!!!!!!

 

[PHV]

A starting point (SQL code):
SELECT C.PostCode, P.Points
FROM [Client Details] As C, PostCodePointValues As P
WHERE C.PostCode Between P.From And P.To

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[funkytunaa]

Thanks for that, I have a bit of playing around to do to get that one going but this code helped me perfect my "access to word mail merge feature with a click of the button" in a round about way!

So thanks!

Cheers