Using a Lookup Value in a Calculated Feild on Form

  • Thread starter Thread starter Trevor Aiston
  • Start date Start date
T

Trevor Aiston

I have a data entry form on a projects database which has three fields to
store the component parts of the project reference code
[PSPC]
[PSPN]
[PFYN]
I have put a text box on the form which conacanates the field to give a
complete reference value
=[PSPC] & "." & [PSPN] & "." & [PFYN]
That works BUT. On the form Field [PSPC] is a combo box with a row source
control of
SELECT [SERVICES AVAILABLE].ServRecordNumber, [SERVICES AVAILABLE].ServCode,
[SERVICES AVAILABLE].ServDescription FROM [SERVICES AVAILABLE];
Which shows the contents of the ServDescription from a lookuptable [SERVICES
AVAILABLE].

But when I refernce the [PSPC] field in the text box it returns the PSPC
record number (ServRecordNumber)rather than description (ServDescription).

Any ideas how-to get the ServDescription used rather than ServRecordNumber?

TIA
Trevor
 
=[PSPC].Column(2) & "." & [PSPN] & "." & [PFYN]

If you do not specify the column (numbering starts at 0), Access gives you
the value of the Bound Column (the Property Sheet will tell you which column
is bound).
 
Thanks bruce you are a star. Cpl hours tearing my hair out could have been
relived by posting the question earlier.

Really appreaciatte that.

BruceM said:
=[PSPC].Column(2) & "." & [PSPN] & "." & [PFYN]

If you do not specify the column (numbering starts at 0), Access gives you
the value of the Bound Column (the Property Sheet will tell you which column
is bound).

Trevor Aiston said:
I have a data entry form on a projects database which has three fields to
store the component parts of the project reference code
[PSPC]
[PSPN]
[PFYN]
I have put a text box on the form which conacanates the field to give a
complete reference value
=[PSPC] & "." & [PSPN] & "." & [PFYN]
That works BUT. On the form Field [PSPC] is a combo box with a row source
control of
SELECT [SERVICES AVAILABLE].ServRecordNumber, [SERVICES
AVAILABLE].ServCode,
[SERVICES AVAILABLE].ServDescription FROM [SERVICES AVAILABLE];
Which shows the contents of the ServDescription from a lookuptable
[SERVICES
AVAILABLE].

But when I refernce the [PSPC] field in the text box it returns the PSPC
record number (ServRecordNumber)rather than description (ServDescription).

Any ideas how-to get the ServDescription used rather than
ServRecordNumber?

TIA
Trevor
Click to expand...
Click to expand...
 
Glad to help. You probably learned quite a bit of stuff while trying to
figure it out. You'll use at least some of that information some day. It's
usually best to do some searching before you post, although I know full well
the aggravation of being tripped up by something that turns out to have such
a simple solution.

Trevor Aiston said:
Thanks bruce you are a star. Cpl hours tearing my hair out could have
been
relived by posting the question earlier.

Really appreaciatte that.

BruceM said:
=[PSPC].Column(2) & "." & [PSPN] & "." & [PFYN]

If you do not specify the column (numbering starts at 0), Access gives
you
the value of the Bound Column (the Property Sheet will tell you which
column
is bound).

Trevor Aiston said:
I have a data entry form on a projects database which has three fields
to
store the component parts of the project reference code
[PSPC]
[PSPN]
[PFYN]
I have put a text box on the form which conacanates the field to give a
complete reference value
=[PSPC] & "." & [PSPN] & "." & [PFYN]
That works BUT. On the form Field [PSPC] is a combo box with a row
source
control of
SELECT [SERVICES AVAILABLE].ServRecordNumber, [SERVICES
AVAILABLE].ServCode,
[SERVICES AVAILABLE].ServDescription FROM [SERVICES AVAILABLE];
Which shows the contents of the ServDescription from a lookuptable
[SERVICES
AVAILABLE].

But when I refernce the [PSPC] field in the text box it returns the
PSPC
record number (ServRecordNumber)rather than description
(ServDescription).

Any ideas how-to get the ServDescription used rather than
ServRecordNumber?

TIA
Trevor
Click to expand...
Click to expand...
Click to expand...
 
Back
Top