Syntax error in SQL query for report

[maxmaggot]

Hi all,



I'm displaying the DnerRegID from the PaymentsReceived table on this report. What I would like to do is to get the Doner firstname and last name from the donations table based on the DonerRegID. I keep getting a syntax error. Here is the code I have written. Any ideas.

Select [Donations]![DonerLastName],[Donations]![DonerFirstName] WHERE [PaymentsReceived]![DonerRegID] = [DonerRegID]

Kind Regards
Karl

[ItsMe]

I am not sure how you are displaying the result. Since I am guessing how I will suggest a solution. As for the code you are showing here. The WHERE statement seems to switch domains from the table named "Donations" to the table your report is using for a recordset.

so
WHERE [PaymentsReceived]![DonerRegID] = [DonerRegID]
would probably work better as
WHERE [Donations]![DonerRegID] = [DonerRegID]
and you still probably need to define the report's current record association to [DonerRegID] after the equal sign.

I don't know if the following code will slow down your DB too much but here is an option. This is assuming you have an unbound text box named "Me.txtAnswer" in the Header or Footer for your DonerRegID text box.

Place the following VBA in the footer or header's format section for your report. Depending on where txtAnswer resides.

Dim strFullName As String
strFullName = DLookup("DonerLastName", "Donations", "[DonerRegID] =" & Me.DonerRegID)
strFullName = strFullName & ", " 'Place a comma and a space after the Last Name
strFullName = strFullName & DLookup("DonerFirstName", "Donations", "[DonerRegID] =" & Me.DonerRegID)
Me.txtAnswer = strFullName



.

[maxmaggot]

Thanks for your help. Going to try that out now (all methods you suggested) ans see how I get on. Thanks again.

[ItsMe]

There are other ways to go about it. Considering where you may be now, the above options are worth consideration.