DLookup that isn't working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a once-working Dlookup that has decided to quit. Here are the
particulars:

tblTransactions
LinkField (PK, autonumber)
StudentName

tblHistory
LinkField
Year (ex. 2003)
Class (ex. “P1â€)
Grade (ex. “Sâ€)

tblTransactions and tblHistory are related in a one-to-many relationship
based on LinkField.

QueryCombined (based on tblHistory) with 3 fields:
LinkField
Year
Combined (created by combining Class and Grade, ex. “P1Sâ€)

In a report based on tblTransactions, I have in the detail area the following:
StudentName
LinkField (though it’s not visible)
A text box representing a year, named “Year1â€
A calculated Control with the following Control Source:
=DLookUp("[Combined]","CombinedQuery","[LinkField]=[LinkField]" And
"[Year]=[Year1]")

It is pulling up a Combined field from the query, but I get the same field
for every record.

What am I missing?
 
The 3rd argument for DLookup needs to be a string like this:

=DLookUp("[Combined]", "CombinedQuery",
"[LinkField] = " & [LinkField] & " And [Year]= " & [Year1])

It would probably be much more efficient to base the report on a query that
includes both tables, rather than use the DLookup().
 
Thanks, Allen, but for some reason it didn't work. I copied your suggestion
exactly. I'd like to try your "more efficient" method, but I'll admit I'm
not sure how to do it. What the report does is list the student name on a
line and then his/her 10 years worth of "combineds" across on the same line.
Does that make sense? I didn't know how to do that with a one-to-many
relationship, without it repeating the one side (student name) on multiple
lines. What do you suggest?

Thanks in advance.

Allen Browne said:
The 3rd argument for DLookup needs to be a string like this:

=DLookUp("[Combined]", "CombinedQuery",
"[LinkField] = " & [LinkField] & " And [Year]= " & [Year1])

It would probably be much more efficient to base the report on a query that
includes both tables, rather than use the DLookup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JWCrosby said:
I have a once-working Dlookup that has decided to quit. Here are the
particulars:

tblTransactions
LinkField (PK, autonumber)
StudentName

tblHistory
LinkField
Year (ex. 2003)
Class (ex. "P1")
Grade (ex. "S")

tblTransactions and tblHistory are related in a one-to-many relationship
based on LinkField.

QueryCombined (based on tblHistory) with 3 fields:
LinkField
Year
Combined (created by combining Class and Grade, ex. "P1S")

In a report based on tblTransactions, I have in the detail area the
following:
StudentName
LinkField (though it's not visible)
A text box representing a year, named "Year1"
A calculated Control with the following Control Source:
=DLookUp("[Combined]","CombinedQuery","[LinkField]=[LinkField]" And
"[Year]=[Year1]")

It is pulling up a Combined field from the query, but I get the same field
for every record.

What am I missing?
Click to expand...
Click to expand...
 
The suggested DLookup() expression assumes:
- your report has text boxes named LinkField and Year1,
- both of these are bound to fields of type Number (not Text),
- there are always values for both fields (neither is Null).

If the text boxes are not present on the report, add them.
If they are Text fields you need extra quotes.
If they could be Null, you need to use Nz().

I can't suggests details for the alternative without knowing what
CombinedQuery contains.

If you are trying to concatenate values from a related table into a single
string to show in the main report, this link might help:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JWCrosby said:
Thanks, Allen, but for some reason it didn't work. I copied your
suggestion
exactly. I'd like to try your "more efficient" method, but I'll admit I'm
not sure how to do it. What the report does is list the student name on a
line and then his/her 10 years worth of "combineds" across on the same
line.
Does that make sense? I didn't know how to do that with a one-to-many
relationship, without it repeating the one side (student name) on multiple
lines. What do you suggest?

Thanks in advance.

Allen Browne said:
The 3rd argument for DLookup needs to be a string like this:

=DLookUp("[Combined]", "CombinedQuery",
"[LinkField] = " & [LinkField] & " And [Year]= " & [Year1])

It would probably be much more efficient to base the report on a query
that
includes both tables, rather than use the DLookup().

JWCrosby said:
I have a once-working Dlookup that has decided to quit. Here are the
particulars:

tblTransactions
LinkField (PK, autonumber)
StudentName

tblHistory
LinkField
Year (ex. 2003)
Class (ex. "P1")
Grade (ex. "S")

tblTransactions and tblHistory are related in a one-to-many
relationship
based on LinkField.

QueryCombined (based on tblHistory) with 3 fields:
LinkField
Year
Combined (created by combining Class and Grade, ex. "P1S")

In a report based on tblTransactions, I have in the detail area the
following:
StudentName
LinkField (though it's not visible)
A text box representing a year, named "Year1"
A calculated Control with the following Control Source:
=DLookUp("[Combined]","CombinedQuery","[LinkField]=[LinkField]" And
"[Year]=[Year1]")

It is pulling up a Combined field from the query, but I get the same
field
for every record.

What am I missing?
Click to expand...
Click to expand...
Click to expand...
 
I think you may have nailed it! LinkField is a text field. Now I'm unsure
of how to do the "double quotes" thing. Could you show me with my original
statement?

Incidently, I went ahead and added a field to the History table that is a
concentation(?) of the year and grade fields, rather than to create them in a
separate query. All that means is that rather than have to do my DLookup to
a query, I can do it to a table.

Thanks, Allen.

Jerry

Allen Browne said:
The suggested DLookup() expression assumes:
- your report has text boxes named LinkField and Year1,
- both of these are bound to fields of type Number (not Text),
- there are always values for both fields (neither is Null).

If the text boxes are not present on the report, add them.
If they are Text fields you need extra quotes.
If they could be Null, you need to use Nz().

I can't suggests details for the alternative without knowing what
CombinedQuery contains.

If you are trying to concatenate values from a related table into a single
string to show in the main report, this link might help:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JWCrosby said:
Thanks, Allen, but for some reason it didn't work. I copied your
suggestion
exactly. I'd like to try your "more efficient" method, but I'll admit I'm
not sure how to do it. What the report does is list the student name on a
line and then his/her 10 years worth of "combineds" across on the same
line.
Does that make sense? I didn't know how to do that with a one-to-many
relationship, without it repeating the one side (student name) on multiple
lines. What do you suggest?

Thanks in advance.

Allen Browne said:
The 3rd argument for DLookup needs to be a string like this:

=DLookUp("[Combined]", "CombinedQuery",
"[LinkField] = " & [LinkField] & " And [Year]= " & [Year1])

It would probably be much more efficient to base the report on a query
that
includes both tables, rather than use the DLookup().

I have a once-working Dlookup that has decided to quit. Here are the
particulars:

tblTransactions
LinkField (PK, autonumber)
StudentName

tblHistory
LinkField
Year (ex. 2003)
Class (ex. "P1")
Grade (ex. "S")

tblTransactions and tblHistory are related in a one-to-many
relationship
based on LinkField.

QueryCombined (based on tblHistory) with 3 fields:
LinkField
Year
Combined (created by combining Class and Grade, ex. "P1S")

In a report based on tblTransactions, I have in the detail area the
following:
StudentName
LinkField (though it's not visible)
A text box representing a year, named "Year1"
A calculated Control with the following Control Source:
=DLookUp("[Combined]","CombinedQuery","[LinkField]=[LinkField]" And
"[Year]=[Year1]")

It is pulling up a Combined field from the query, but I get the same
field
for every record.

What am I missing?
Click to expand...
Click to expand...
Click to expand...
 
Okay, to get quotes within quotes, you double them up.
Example. to get:
This has a "word" in quotes."
you use:
"This has a ""word"" in quotes."

Therefore:
=DLookUp("[Combined]", "CombinedQuery",
"[LinkField] = """ & [LinkField] & """ And [Year]= " & [Year1])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JWCrosby said:
I think you may have nailed it! LinkField is a text field. Now I'm unsure
of how to do the "double quotes" thing. Could you show me with my
original
statement?

Incidently, I went ahead and added a field to the History table that is a
concentation(?) of the year and grade fields, rather than to create them
in a
separate query. All that means is that rather than have to do my DLookup
to
a query, I can do it to a table.

Thanks, Allen.

Jerry

Allen Browne said:
The suggested DLookup() expression assumes:
- your report has text boxes named LinkField and Year1,
- both of these are bound to fields of type Number (not Text),
- there are always values for both fields (neither is Null).

If the text boxes are not present on the report, add them.
If they are Text fields you need extra quotes.
If they could be Null, you need to use Nz().

I can't suggests details for the alternative without knowing what
CombinedQuery contains.

If you are trying to concatenate values from a related table into a
single
string to show in the main report, this link might help:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JWCrosby said:
Thanks, Allen, but for some reason it didn't work. I copied your
suggestion
exactly. I'd like to try your "more efficient" method, but I'll admit
I'm
not sure how to do it. What the report does is list the student name
on a
line and then his/her 10 years worth of "combineds" across on the same
line.
Does that make sense? I didn't know how to do that with a one-to-many
relationship, without it repeating the one side (student name) on
multiple
lines. What do you suggest?

Thanks in advance.

:

The 3rd argument for DLookup needs to be a string like this:

=DLookUp("[Combined]", "CombinedQuery",
"[LinkField] = " & [LinkField] & " And [Year]= " & [Year1])

It would probably be much more efficient to base the report on a query
that
includes both tables, rather than use the DLookup().

I have a once-working Dlookup that has decided to quit. Here are the
particulars:

tblTransactions
LinkField (PK, autonumber)
StudentName

tblHistory
LinkField
Year (ex. 2003)
Class (ex. "P1")
Grade (ex. "S")

tblTransactions and tblHistory are related in a one-to-many
relationship
based on LinkField.

QueryCombined (based on tblHistory) with 3 fields:
LinkField
Year
Combined (created by combining Class and Grade, ex. "P1S")

In a report based on tblTransactions, I have in the detail area the
following:
StudentName
LinkField (though it's not visible)
A text box representing a year, named "Year1"
A calculated Control with the following Control Source:
=DLookUp("[Combined]","CombinedQuery","[LinkField]=[LinkField]" And
"[Year]=[Year1]")

It is pulling up a Combined field from the query, but I get the same
field
for every record.

What am I missing?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top