dlookup based on first digits of a field

[John]

I am trying to to do a dlookup based on the first 3 digits of my
MeasureNumber Field. Measure number is a text field and takes the form of
'1.a.1'. I want the dlookup to reference my t_Sections table to find a match
in the "SectionNumber" field (text) and return the value of the
"SectionTitle" field. The goal of this is to have a section header at each
change in the first two characters (e.g. 1.a to 1.b would result in a section
header above 1.b). I consistently get #Error as a result. I cant seem to
get the syntax down as.

code: =DLookUp("SectionTitle","t_Sections","SectionNumber = " &
Left([MeasureSection],3))

 

[fredg]

I am trying to to do a dlookup based on the first 3 digits of my
MeasureNumber Field. Measure number is a text field and takes the form of
'1.a.1'. I want the dlookup to reference my t_Sections table to find a match
in the "SectionNumber" field (text) and return the value of the
"SectionTitle" field. The goal of this is to have a section header at each
change in the first two characters (e.g. 1.a to 1.b would result in a section
header above 1.b). I consistently get #Error as a result. I cant seem to
get the syntax down as.

code: =DLookUp("SectionTitle","t_Sections","SectionNumber = " &
Left([MeasureSection],3))

Perhaps you could tell us how "1.a.1" are 3 characters and "1.a" is 2
characters when I see 5 and 3 characters respectively.
You can ignore the dots, but Access can't.
In any event, "1.a.1" and "1.a" are text, not a number, so, at the
very least, your where clause criteria needs to surround it with
quotes.

Also, what is the [MeasureNumber] field? I see SectionNumber and
MeasureSection. Remember, we cannot see your database so it's up to
you to be accurate in your message.

Perhaps:
"SectionNumber = '" & Left([MeasureSection],5) & "'")
will work for you.

If you still need help re-post, using the actual field names.

 

[John]

Fred - First - your syntax worked - thanks

A section is made up of just the first 3 characters (incl. decimal) of a
measure. Think of it as a hierarchy; measure 1.a.1 is in section 1.a

I have a table (t_Sections)) with fields for "SectionNumber" and
"SectionTitle". I was struggling with the dlookup syntax to this table. The
three left most digits of the MeasureNumber - by definition gives me the
SectionNumber. MeasureNumber is a field native to the report so I am
leveraging it for the lookup value rather than adding another field.

If you have a better solution, I am open to hearing it as what I've come up
with seems slighty hokey and could result in an issue should we get to 10 or
more chapters (the number prior to the first decimal). It is unlikely that
we will get more than 10 but who knows what other applications I may want to
apply this to.


Thanks again though
--
QWERTY

I am trying to to do a dlookup based on the first 3 digits of my
MeasureNumber Field. Measure number is a text field and takes the form of
'1.a.1'. I want the dlookup to reference my t_Sections table to find a match
in the "SectionNumber" field (text) and return the value of the
"SectionTitle" field. The goal of this is to have a section header at each
change in the first two characters (e.g. 1.a to 1.b would result in a section
header above 1.b). I consistently get #Error as a result. I cant seem to
get the syntax down as.

code: =DLookUp("SectionTitle","t_Sections","SectionNumber = " &
Left([MeasureSection],3))

Perhaps you could tell us how "1.a.1" are 3 characters and "1.a" is 2
characters when I see 5 and 3 characters respectively.
You can ignore the dots, but Access can't.
In any event, "1.a.1" and "1.a" are text, not a number, so, at the
very least, your where clause criteria needs to surround it with
quotes.

Also, what is the [MeasureNumber] field? I see SectionNumber and
MeasureSection. Remember, we cannot see your database so it's up to
you to be accurate in your message.

Perhaps:
"SectionNumber = '" & Left([MeasureSection],5) & "'")
will work for you.

If you still need help re-post, using the actual field names.

 

[Marshall Barton]

I am trying to to do a dlookup based on the first 3 digits of my
MeasureNumber Field. Measure number is a text field and takes the form of
'1.a.1'. I want the dlookup to reference my t_Sections table to find a match
in the "SectionNumber" field (text) and return the value of the
"SectionTitle" field. The goal of this is to have a section header at each
change in the first two characters (e.g. 1.a to 1.b would result in a section
header above 1.b). I consistently get #Error as a result. I cant seem to
get the syntax down as.

code: =DLookUp("SectionTitle","t_Sections","SectionNumber = " &
Left([MeasureSection],3))

Since SectionNumber is a Text field, you need to put the
value in quotes:

.... ,"SectionNumber = """ & Left(MeasureSection,3) & """ ")