CODEWITHSUNDEEP
ms-accessselectparameters
w00ngy
w00ngy

Reputation: 1828

MS Access Query - Use a parameter value as one of the selected records

I am writing a MS Access Query with Parameters and wondered if it were possible to include one of the parameters as the returned selected records:

PARAMETERS [@SubmissionID] IEEEDouble, [@StartDate] DATETIME, [@EndDate] DATETIME;
INSERT INTO tblHUD_client_profile
(SubmissionID, ClientID)
SELECT [@SubmissionID] as SubmissionID, DISTINCT(ClientID) 
FROM tblClientEducation
WHERE (BeginDate BETWEEN [@StartDate] AND [@EndDate]
   OR EndDate BETWEEN [@StartDate] AND [@EndDate])
   AND NOT EXISTS(
                  Select ClientID 
                  from tblHUD_client_profile 
                  WHERE SubmissionID = [@SubmissionID] 
                  AND ClientID = tblClientEducation.ClientID
                  );

The "Select [@SubmissionID] as SubmissionID" always gives me a syntax error.

I apologize if there's a question with this solution already out there. I looked around for this but with so many basic questions about MS Access Queries and Parameters, I couldn't find what I was looking for.

Upvotes: 0

Views: 792

Answers (2)

w00ngy
w00ngy

Reputation: 1828

After reviewing the original code, it seems to work as expected.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

Leave out @ for MS Access:

PARAMETERS SubmissionID Integer;
SELECT [SubmissionID] as SubmissionID, DISTINCT(ClientID) 
FROM tblClientEducation

Upvotes: 1

Related Questions