If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLookup that isn't working
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? |
#2
|
|||
|
|||
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWCrosby" wrote in message ... 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? |
#3
|
|||
|
|||
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" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWCrosby" wrote in message ... 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? |
#4
|
|||
|
|||
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWCrosby" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#5
|
|||
|
|||
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" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWCrosby" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#6
|
|||
|
|||
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." Therefo =DLookUp("[Combined]", "CombinedQuery", "[LinkField] = """ & [LinkField] & """ And [Year]= " & [Year1]) -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWCrosby" wrote in message ... 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" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWCrosby" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
DLookUp for multiple forms | [email protected] | Using Forms | 4 | January 9th, 2005 10:48 AM |
DLOOKUP in an external Database gives TYPE Mismatch-Error | Reiner Harmgardt | General Discussion | 1 | July 22nd, 2004 09:00 AM |
DLookup Function in Queries | Jim | Running & Setting Up Queries | 1 | June 10th, 2004 07:07 PM |
Why my macros are working in *.ptt, but not in *.pps ? | Zbig | Powerpoint | 8 | June 3rd, 2004 11:08 PM |
I am working on a project in M/S Publisher (2002) for school and need help fast! PLEASE!!!! | DBCooper | Publisher | 5 | May 6th, 2004 11:52 PM |