Dlookup

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,
ia am using the Dllokup function to display a unit as follows.
=DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" &
[Forms]![frmCandidateReviews]![candidateID])

The unit displayed is English/001
Would it be possible to display only 001 or English using the Dlookup
function without using a query?

Regards

John
 
Maybe those fields should be stored separately, and concatenated as needed.
Having said that, you can't do what you want with DLookup alone, but you can
use the Left, Right, and maybe InStr functions. The complete answer to your
question depends in part on whether the format is consistent from one record
to the next. If the suffix is always three characters (or four, counting
the slash), you can display just the suffix:
=Right(DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" & _
[Forms]![frmCandidateReviews]![candidateID]),3)

Or you could display just the first part:
=Left(DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" & _
[Forms]![frmCandidateReviews]![candidateID]), _
Len(DLookUp ("[Unit1]","tblCandidateDETAILS","[CandidateID] =" & _
[Forms]![frmCandidateReviews]![candidateID])) - 4)

You could use the InStr function to locate the slash if the suffix is of
variable length.

If the control containing this expression is located on frmCandidateReviews
there is no need for the full [Forms]![frmCandidateReviews]![candidateID]
syntax.

What is your objection to a query? You would be using the same expression
there.
 
?mid(x,instr(x,"/")+1)
?mid(x,1,instr(x,"/")-1)

To show 001
strUnitNo = DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" &
[Forms]![frmCandidateReviews]![candidateID])
strUnitNo = Mid(strUnitNo, (Instr(UnitNO, "/") + 1)

To show English
strUnitNo = DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" &
[Forms]![frmCandidateReviews]![candidateID])
strUnitNo = Mid(strUnitNo, 1,(Instr(UnitNO, "/") - 1)

Now the issue here is where are you using it? The above will not be
appropriage for an control source or a default value. If this is what you
are doing, then I recommend you create a function with the above code and
call the function from the property. It would be possible to do it without
the function; however, it would be less efficient because it would have to do
the database lookup twice.

For example to show English:

=Mid(DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" &
[Forms]![frmCandidateReviews]![candidateID]), 1,
Instr(DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" &
[Forms]![frmCandidateReviews]![candidateID]), "/") - 1)

(can't guarantee the syntax is correct)
 
John,

It looks like your table is not normalized properly.

The first indication of this is the name of the field (Unit1) you have in
the DLOOKUP( ) domain function. From this, I can assume that you have at
least a Unit2, and potentially more "Unit#" fields. This indicates that you
need another table (maybe CandidateUnits) to properly normalize the table.

The next indication of non-normalized table is that it appears that you are
storing more than one piece of information in your "Unit1" field. If the
lookup is returning
"English/001", and you need to decompose that for some reason, then you
should probably have two fields, one for "English" (I assume this is a course
name) and one for "001" (I assume this is some sort of course increment).

Given the structure you have, and that all of the values in the Unit1 field
contain the "/" character to seperate these values, you could use something
like:

Mid(DLOOKUP("[Unit1]", "tblCandidateDetails", "CandidateID = " & ....), _
INSTR([Unit1], "\") + 1)

HTH
Dale
 
John,
Given that Unit1 values are always any number of Text chars and any
number of digits... divided by a "/"...

(not tested...check my syntax)
To display the text only...
=Left(DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" &
[Forms]![frmCandidateReviews]![candidateID]), InStr(Unit1,"/")-1)

To Display the digits....
=Right(DLookUp("[Unit1]","tblCandidateDETAILS","[CandidateID] =" &
[Forms]![frmCandidateReviews]![candidateID]), InStr(Unit1,"/")+1)

--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."
 
Thanks for your help guys,
I'll take a look at creating a second table for the units

Regards

John
 
Back
Top