DlookUp Problem using a query

[pinkpanther6666]

I have a Dlookup problem

Shown below are my query results and my form .... i need to populate the HomeTeam2 Pts column with Total of the corresponding Total from the query

i am using the following in the HomeTeams2 Pts Textbox but i am getting #Error

=DLookUp("[Total]","qryFixDarts","[Week] =" & [Forms]![frmFixtures2]![Week] & " And [Team] =" & [Forms]![frmFixtures2]![HomeTeam2])

Week	Team	Total	CDarts
1	TeamA	12	2
1	TeamC	12	2
1	TeamE	12	2

qryFixDarts </caption><thead>
</thead><tbody>
</tbody>

Week	HomeTeam2	Pts	AwayTeam2	Pts
1	TeamA		TeamB
1	TeamC		TeamD
1	TeamE		TeamF
1	TeamG		TeamH
1	TeamJ		TeamK
1	TeamL		TeamM
1	TeamN		TeamO
1	TeamP		TeamQ

<caption> frmFixtures2 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Could somebody have a look please


Many Thanks



Steve

 

[xenou]

Assuming you need single quotes around the text value for Team:
=DLookUp("[Total]","qryFixDarts","[Week] =" & [Forms]![frmFixtures2]![Week] & " And [Team] ='" & [Forms]![frmFixtures2]![HomeTeam2] & "'")

 

[pinkpanther6666]

Xenou

Many thanks for your reply


i have copied and pasted your reply and i still get #Error

i think i am resounded to the fact i will have to input this data manually

many thanks again for your help


Cheers


Steve

 

[xenou]

If week is a text field it would need the single quotes too. All text literal values should be enclosed in quotes. Possibly you can just include the values you want in your query that feeds the form. Not sure why a dlookup is needed here, I guess. In general a dlookup is a select query using a weird function syntax, so as an exercise, write the dlookup as a sql select query (using hard values as needed) -- just to see how it works. Then transform it into a dlookup.

 

[pinkpanther6666]

Xenou

Many thanks for your reply

Week is a Long Integer
HomeTeam2 and AwayTeam2 are both Short Text

I will have a go at SQL ( might have to have a quick internet search to do that)

Many Thanks


Steve