How to implement where clause in subquery 1

[Mandyxoxo]

I built a query to take totalcount of distinct cardnumbers of individuals accessing a datacenter. The code looks like so:

SELECT Count(EntryData_2008_02_to_04.CardNumber) AS TotalCount
FROM [Select DISTINCT EntryData_2008_02_to_04.CardNumber, EntryData_2008_02_to_04.EntryDate FROM EntryData_2008_02_to_04 AS EntryData_2008_02_to_04 WHERE EntryData_2008_02_to_04.EntryDate BETWEEN StartDate And EndDate]. AS [%Alias];

Instead of having date parameters popup for me to input the dates, I have created a dialog form to allow the user to input the dates in one single window. I need to add the following code in place of the StartDate and EndDate above.

Forms!Date Prompt for Total Granted Access!Text0 And
(EntryData_2008_02_to_04.EntryDate)=Forms!Date Prompt for Total Granted Access!Text2

Please help me because when I try to fix the code with the code I need, it always gives me a count of 0. That is obviously not correct.

Thank you for any help!

 

[PWise]

SELECT Count(EntryData_2008_02_to_04.CardNumber) AS TotalCount
FROM [Select DISTINCT EntryData_2008_02_to_04.CardNumber, EntryData_2008_02_to_04.EntryDate FROM EntryData_2008_02_to_04 AS EntryData_2008_02_to_04 WHERE EntryData_2008_02_to_04.EntryDate BETWEEN Forms!Date Prompt for Total Granted Access!Text0 And Forms!Date Prompt for Total Granted Access!Text2]. AS [%Alias];

 

[Mandyxoxo]

I did what you posted. It gives me an error saying: Syntax error (missing operator) in query expression EntryData_2008_02_to_04.EntryDate BETWEEN Forms!Date Prompt for Total Granted Access!Text0 And Forms!Date Prompt for Total Granted Access!Text2

Any ideas why?

 

[PWise]

SELECT Count(EntryData_2008_02_to_04.CardNumber) AS TotalCount
FROM [Select DISTINCT EntryData_2008_02_to_04.CardNumber, EntryData_2008_02_to_04.EntryDate FROM EntryData_2008_02_to_04 AS EntryData_2008_02_to_04 WHERE EntryData_2008_02_to_04.EntryDate BETWEEN Forms![Date Prompt for Total Granted Access]!Text0 And Forms![Date Prompt for Total Granted Access]!Text2]. AS [%Alias];

 

[Mandyxoxo]

I did that and it gives the error Invalid bracketing of name and then includes the entire subquery in the error message....

 

[PWise]

SELECT Count(EntryData_2008_02_to_04.CardNumber) AS TotalCount
FROM (Select DISTINCT EntryData_2008_02_to_04.CardNumber, EntryData_2008_02_to_04.EntryDate FROM EntryData_2008_02_to_04 AS EntryData_2008_02_to_04 WHERE EntryData_2008_02_to_04.EntryDate BETWEEN Forms![Date Prompt for Total Granted Access]!Text0 And Forms![Date Prompt for Total Granted Access]!Text2) AS [%Alias];

 

[Golom]

Try this

SELECT Count(X.CardNumber) AS TotalCount

FROM 
(
Select DISTINCT E.CardNumber, E.EntryDate 
FROM EntryData_2008_02_to_04 AS E
WHERE E.EntryDate BETWEEN 
          Forms![Date Prompt for Total Granted Access]!Text0 
      And Forms![Date Prompt for Total Granted Access]!Text2
) AS X