help with multiple select statement query

[nikloskim]

hi all... i am trying to grab rows based on two user-inputted criteria, 'theEmpID' and 'theDate'..and then, based on 'theDate' value, 'startDate' will be used as the criteria for the beginning date range up until the ending date range of 'theDate'

SELECT  *
FROM     [salesQuery]
WHERE  emp_id = theEmpID AND date_of BETWEEN startDate AND theDate
WHERE  (startDate = SELECT  DISTINCT (begin_date)
                   FROM     [salesQuery]
                   WHERE  date_of = theDate)

any pointers or help would be greatly appreciated

 

[dhookom]

Are you attempting to use parameter prompts in queries and subqueries? IMHO, this type of user interface is not acceptable. Consider creating a form with controls for users to enter the criteria.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

plus you can't have 2 WHERE portions of a query.

I was going to try and show you how to do it with 2 parameter prompts, but I can't figure out how you are determining a RANGE with a single date.....

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[nikloskim]

thank you guys for responding; the parameters passed in by the user are from the form, selecting the employee name and selecting a date. then what I wanted to do was, based upon the selected employee and date, retreive the data for the week start up until the selected date, and sum them up... I realized that what I wanted was a week-to-date functionality, which I have found quite a few hits for searching around... sorry for the hassle, I'm not actually a programmer, just a financial analyst trying to expand on this system I am using

 

[randy700]

How about...

"SELECT *, begin_date as startDate " & _
"FROM salesQuery " & _
"WHERE emp_id = '" & Forms!yourForm!theEmpID & "'" & _
"AND date_of BETWEEN begin_date AND #" & _
Forms!yourForm!theDate & "#"

Note: not tested


Randy