passing information

What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)yup, derived table:

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
,t1.CONTSUPP
FROM dbo.CONTACT1 INNER JOIN
(
SELECT ACCOUNTNO, CONTSUPP
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
) As t1 ON t1.ACCOUNTNO = CONTACT1.ACCOUNTNO

You get the benefit of only generating the derived table once as well, as opposed to being evaluated once for each record when placed in the WHERE clause.|||Thank you, you pointed me in the right direction. There was one issue with the code you wrote because you cant group by ACCOUNTNO because the select has ACCOUNTNO and CONTACT. Anyway this is what the currently working code looks like. Thank you again, without your help I would not have been able to do this.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source,
t1.contact AS 'Device'
FROM dbo.CONTACT1
INNER JOIN (
SELECT accountno, contact
FROM dbo.CONTSUPP
WHERE accountno IN (
SELECT accountno
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY accountno
HAVING COUNT(*) <= 1
) AND (
contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
)
) AS t1
ON dbo.CONTACT1.accountno = t1.accountno

pass a value from form to report

I have a form on which the user enters a date range. I would like the date range to be displayed at the top of the report

like: For Period 08/01/2006 - 08/31/2006

Is it possible to pass a value from a form into a label in a report?

You can pass information to a report using parameters. The form fields must be named exactly like the report parameters. You could then set the value of the label to the parameter value passed in from your form.

I have enountered problems when a form contained server side .NET controls. (if there are 2 report parameters, there can only be 2 fields in your form... and .net adds 2 form fields automatically to handle post-back and state information) To get around this limitation I used querystring parameters instead of posting via a form. (there is also a parameter length limitation using this approach however)

http://msdn2.microsoft.com/en-us/library/ms153563.aspx