Difficult expression

[Leslie Isaacs]

Hello All

I have a report [rpt summary] that is based on table [x confirmed]. The
table includes the following fields:

[month no] (integer)
[name] (text)
[tax code no] (integer)

First of all, I should say that I have 'inherited' this db, so the naming of
field [name] was not my doing! It is however firmly embeded throughout the
various queries, reports and forms, and the db works, so it will have to
stay.

Table [x confirmed] contains many records with the same value in [name]
(i.e. for each person), but for each of these records there is always a
different value for [month no] (i.e. each person is only in the table once
for each month number).

I need to create a new textbox on the report that gives, for each person,
the value of [tax code no] that occurs with that person's highest value of
[month no] that is between 73 and 84 (inclusive). This textbox will be in
the report's detail section, where there is already a field [name].

I have tried using this DLookup function:

=DLookup ("[tax code no]", "[x confirmed]", "[name]=[reports].[rpt
summary].[name] and [month no] = DMax ("[month no]","[x confirmed]","[name]=
[reports].[rpt summary].[name] and [month no] > 72 and [month no] < 85")")

When this just gave me #ERROR I suspected the [name] field, so I renamed it
as [person] in the report and tried:

=DLookup ("[tax code no]", "[x confirmed]", "[name]=[reports].[rpt
summary].[person] and [month no] = DMax ("[month no]","[x
confirmed]","[name]= [reports].[rpt summary].[person] and [month no] > 72
and [month no] < 85")")

.... but with the same result.

Am I on the right lines (is it just my syntax?), or is there a better way of
getting this?

I hope someone can help.
Many thanks
Les lie Isaacs

 

[Wayne Morgan]

I suspect that you need to concatenate in the value of [reports].[rpt
summary].[name]

Example:
=DLookup("[tax code no]", "[x confirmed]", "[name]=" & [reports].[rpt
summary].[name] & " and [month no] = DMax ("[month no]","[x
confirmed]","[name]=" & [reports].[rpt summary].[name] & " and [month no] >
72 and [month no] < 85")")

On the report, change the name of the [Name] textbox to txtName. This will
get you around the reserved word problem and also help prevent a #Name error
in the equation due to the textbox and the field having the same name,
preventing Access from knowing which of the two you want to use. Once you do
that, the above would become:

=DLookup("[tax code no]", "[x confirmed]", "[name]=" & [txtName] & " and
[month no] = DMax ("[month no]","[x confirmed]","[name]=" & [txtName] & "
and [month no] > 72 and [month no] < 85")")

Also, I believe the quotes in the DMax() function may cause a problem with
the quotes in the DLookup() function. If so, double all of the quotes in the
DMax().

=DLookup("[tax code no]", "[x confirmed]", "[name]=" & [txtName] & " and
[month no] = DMax (""[month no]"",""[x confirmed]"",""[name]="" & [txtName]
& "" and [month no] > 72 and [month no] < 85"")")

The reason for this is the following will cause an error:

strVariable = "This is my " string"

If you really want to put a quote in the string above, it needs to be:

strVariable = "This is my "" string"

When you double the quotes, it is assumed that you literally want a quote
character in the string instead of it being the next delimiter in the
string.

--
Wayne Morgan
MS Access MVP

Hello All

I have a report [rpt summary] that is based on table [x confirmed]. The
table includes the following fields:

[month no] (integer)
[name] (text)
[tax code no] (integer)

First of all, I should say that I have 'inherited' this db, so the naming
of field [name] was not my doing! It is however firmly embeded throughout
the various queries, reports and forms, and the db works, so it will have
to stay.

Table [x confirmed] contains many records with the same value in [name]
(i.e. for each person), but for each of these records there is always a
different value for [month no] (i.e. each person is only in the table once
for each month number).

I need to create a new textbox on the report that gives, for each person,
the value of [tax code no] that occurs with that person's highest value of
[month no] that is between 73 and 84 (inclusive). This textbox will be in
the report's detail section, where there is already a field [name].

I have tried using this DLookup function:

=DLookup ("[tax code no]", "[x confirmed]", "[name]=[reports].[rpt
summary].[name] and [month no] = DMax ("[month no]","[x
confirmed]","[name]= [reports].[rpt summary].[name] and [month no] > 72
and [month no] < 85")")

When this just gave me #ERROR I suspected the [name] field, so I renamed
it as [person] in the report and tried:

=DLookup ("[tax code no]", "[x confirmed]", "[name]=[reports].[rpt
summary].[person] and [month no] = DMax ("[month no]","[x
confirmed]","[name]= [reports].[rpt summary].[person] and [month no] > 72
and [month no] < 85")")

... but with the same result.

Am I on the right lines (is it just my syntax?), or is there a better way
of getting this?

I hope someone can help.
Many thanks
Les lie Isaacs

 

[Leslie Isaacs]

Wayne

Many thanks for that: your solution worked a treat!

Thanks again
Les

I suspect that you need to concatenate in the value of [reports].[rpt
summary].[name]

Example:
=DLookup("[tax code no]", "[x confirmed]", "[name]=" & [reports].[rpt
summary].[name] & " and [month no] = DMax ("[month no]","[x
confirmed]","[name]=" & [reports].[rpt summary].[name] & " and [month no]

72 and [month no] < 85")")

On the report, change the name of the [Name] textbox to txtName. This will
get you around the reserved word problem and also help prevent a #Name
error in the equation due to the textbox and the field having the same
name, preventing Access from knowing which of the two you want to use.
Once you do that, the above would become:

=DLookup("[tax code no]", "[x confirmed]", "[name]=" & [txtName] & " and
[month no] = DMax ("[month no]","[x confirmed]","[name]=" & [txtName] & "
and [month no] > 72 and [month no] < 85")")

Also, I believe the quotes in the DMax() function may cause a problem with
the quotes in the DLookup() function. If so, double all of the quotes in
the DMax().

=DLookup("[tax code no]", "[x confirmed]", "[name]=" & [txtName] & " and
[month no] = DMax (""[month no]"",""[x confirmed]"",""[name]="" &
[txtName] & "" and [month no] > 72 and [month no] < 85"")")

The reason for this is the following will cause an error:

strVariable = "This is my " string"

If you really want to put a quote in the string above, it needs to be:

strVariable = "This is my "" string"

When you double the quotes, it is assumed that you literally want a quote
character in the string instead of it being the next delimiter in the
string.

--
Wayne Morgan
MS Access MVP

Hello All

I have a report [rpt summary] that is based on table [x confirmed]. The
table includes the following fields:

[month no] (integer)
[name] (text)
[tax code no] (integer)

First of all, I should say that I have 'inherited' this db, so the naming
of field [name] was not my doing! It is however firmly embeded throughout
the various queries, reports and forms, and the db works, so it will have
to stay.

Table [x confirmed] contains many records with the same value in [name]
(i.e. for each person), but for each of these records there is always a
different value for [month no] (i.e. each person is only in the table
once for each month number).

I need to create a new textbox on the report that gives, for each person,
the value of [tax code no] that occurs with that person's highest value
of [month no] that is between 73 and 84 (inclusive). This textbox will be
in the report's detail section, where there is already a field [name].

I have tried using this DLookup function:

=DLookup ("[tax code no]", "[x confirmed]", "[name]=[reports].[rpt
summary].[name] and [month no] = DMax ("[month no]","[x
confirmed]","[name]= [reports].[rpt summary].[name] and [month no] > 72
and [month no] < 85")")

When this just gave me #ERROR I suspected the [name] field, so I renamed
it as [person] in the report and tried:

=DLookup ("[tax code no]", "[x confirmed]", "[name]=[reports].[rpt
summary].[person] and [month no] = DMax ("[month no]","[x
confirmed]","[name]= [reports].[rpt summary].[person] and [month no] >
72 and [month no] < 85")")

... but with the same result.

Am I on the right lines (is it just my syntax?), or is there a better way
of getting this?

I hope someone can help.
Many thanks
Les lie Isaacs