input parameter to 3 fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry if this has been asked before, but I cannot find a similar posting.
I am trying to set up a parameter query to find a project name (fieldname=
project) between 2 dates (fieldname=attendeddate) OR link project (fieldname
= linkproject) between the same 2 dates (fieldname=attendeddate).
I do not want the user to have to put in the date range twice.
the WHERE statement in the sql is as follows

WHERE ((([Project Codes].Project)=[enter project]) AND (([event
schedule].eventdate) Between [first date] And [second date])) OR
(((contractual.[link project])=[and enter link project]));

but this finds the project between the dates, but not the link project
between the dates.

Can I state that I want to find project OR link project between the same 2
dates.

Hope this makes sense?
 
Might this work for you?

WHERE ([Project Codes].Project=[enter project]
OR contractual.[link project]=[and enter link project])
AND [event schedule].eventdate Between [first date] And [second date]


- Raoul
 
Tried this and am afraid it doesnt work. It find only "link projects" within
these dates and displays ALL "projects"

JaRa said:
Might this work for you?

WHERE ([Project Codes].Project=[enter project]
OR contractual.[link project]=[and enter link project])
AND [event schedule].eventdate Between [first date] And [second date]


- Raoul

lynn atkinson said:
Sorry if this has been asked before, but I cannot find a similar posting.
I am trying to set up a parameter query to find a project name (fieldname=
project) between 2 dates (fieldname=attendeddate) OR link project (fieldname
= linkproject) between the same 2 dates (fieldname=attendeddate).
I do not want the user to have to put in the date range twice.
the WHERE statement in the sql is as follows

WHERE ((([Project Codes].Project)=[enter project]) AND (([event
schedule].eventdate) Between [first date] And [second date])) OR
(((contractual.[link project])=[and enter link project]));

but this finds the project between the dates, but not the link project
between the dates.

Can I state that I want to find project OR link project between the same 2
dates.

Hope this makes sense?
Click to expand...
Click to expand...
 
can you provide me with the full sql statement please.

If there are dates available on contractual table then the where clause
should become :

WHERE ([Project Codes].Project=[enter project]
AND [event schedule].eventdate Between [first date] And [second date])
OR (contractual.[link project]=[and enter link project]
AND [contractual].eventdate Between [first date] And [second date])

- Raoul


lynn atkinson said:
Tried this and am afraid it doesnt work. It find only "link projects" within
these dates and displays ALL "projects"

JaRa said:
Might this work for you?

WHERE ([Project Codes].Project=[enter project]
OR contractual.[link project]=[and enter link project])
AND [event schedule].eventdate Between [first date] And [second date]


- Raoul

lynn atkinson said:
Sorry if this has been asked before, but I cannot find a similar posting.
I am trying to set up a parameter query to find a project name (fieldname=
project) between 2 dates (fieldname=attendeddate) OR link project (fieldname
= linkproject) between the same 2 dates (fieldname=attendeddate).
I do not want the user to have to put in the date range twice.
the WHERE statement in the sql is as follows

WHERE ((([Project Codes].Project)=[enter project]) AND (([event
schedule].eventdate) Between [first date] And [second date])) OR
(((contractual.[link project])=[and enter link project]));

but this finds the project between the dates, but not the link project
between the dates.

Can I state that I want to find project OR link project between the same 2
dates.

Hope this makes sense?
Click to expand...
Click to expand...
Click to expand...
 
I think I have got this to work

SELECT [Project Codes].Project, [event schedule].eventdate AS [event date],
bookings.[event ID], [course details].[course title], Count(bookings.status)
AS CountOfstatus, contractual.[link project]
FROM [course details] INNER JOIN ((((bookings INNER JOIN (employeedetails
INNER JOIN [post details] ON employeedetails.[post ID] = [post details].[post
ID]) ON bookings.employeeID = employeedetails.[employee ID new]) INNER JOIN
[event schedule] ON bookings.[event ID] = [event schedule].[event ID]) INNER
JOIN [Project Codes] ON [post details].[project code] = [Project Codes].Code)
INNER JOIN contractual ON employeedetails.[employee ID new] =
contractual.[employee ID]) ON [course details].[course code] = [event
schedule].[course code]
GROUP BY [Project Codes].Project, [event schedule].eventdate,
bookings.[event ID], [course details].[course title], contractual.[link
project]
HAVING ((([Project Codes].Project)=[enter location]) AND (([event
schedule].eventdate) Between [first date] And [second date])) OR ((([event
schedule].eventdate) Between [first date] And [second date]) AND
((contractual.[link project])=[and enter link project]));

With this statement I input the "project" the start and end dates of the
range and the "link project". It doesnt ask for the dates again and as far as
I can see the data presented is correct. Does this make sense?

JaRa said:
can you provide me with the full sql statement please.

If there are dates available on contractual table then the where clause
should become :

WHERE ([Project Codes].Project=[enter project]
AND [event schedule].eventdate Between [first date] And [second date])
OR (contractual.[link project]=[and enter link project]
AND [contractual].eventdate Between [first date] And [second date])

- Raoul


lynn atkinson said:
Tried this and am afraid it doesnt work. It find only "link projects" within
these dates and displays ALL "projects"

JaRa said:
Might this work for you?

WHERE ([Project Codes].Project=[enter project]
OR contractual.[link project]=[and enter link project])
AND [event schedule].eventdate Between [first date] And [second date]


- Raoul

:

Sorry if this has been asked before, but I cannot find a similar posting.
I am trying to set up a parameter query to find a project name (fieldname=
project) between 2 dates (fieldname=attendeddate) OR link project (fieldname
= linkproject) between the same 2 dates (fieldname=attendeddate).
I do not want the user to have to put in the date range twice.
the WHERE statement in the sql is as follows

WHERE ((([Project Codes].Project)=[enter project]) AND (([event
schedule].eventdate) Between [first date] And [second date])) OR
(((contractual.[link project])=[and enter link project]));

but this finds the project between the dates, but not the link project
between the dates.

Can I state that I want to find project OR link project between the same 2
dates.

Hope this makes sense?
Click to expand...
Click to expand...
Click to expand...
 
yep it does the having clause looks exact as i suggested.

Success

- Raoul


lynn atkinson said:
I think I have got this to work

SELECT [Project Codes].Project, [event schedule].eventdate AS [event date],
bookings.[event ID], [course details].[course title], Count(bookings.status)
AS CountOfstatus, contractual.[link project]
FROM [course details] INNER JOIN ((((bookings INNER JOIN (employeedetails
INNER JOIN [post details] ON employeedetails.[post ID] = [post details].[post
ID]) ON bookings.employeeID = employeedetails.[employee ID new]) INNER JOIN
[event schedule] ON bookings.[event ID] = [event schedule].[event ID]) INNER
JOIN [Project Codes] ON [post details].[project code] = [Project Codes].Code)
INNER JOIN contractual ON employeedetails.[employee ID new] =
contractual.[employee ID]) ON [course details].[course code] = [event
schedule].[course code]
GROUP BY [Project Codes].Project, [event schedule].eventdate,
bookings.[event ID], [course details].[course title], contractual.[link
project]
HAVING ((([Project Codes].Project)=[enter location]) AND (([event
schedule].eventdate) Between [first date] And [second date])) OR ((([event
schedule].eventdate) Between [first date] And [second date]) AND
((contractual.[link project])=[and enter link project]));

With this statement I input the "project" the start and end dates of the
range and the "link project". It doesnt ask for the dates again and as far as
I can see the data presented is correct. Does this make sense?

JaRa said:
can you provide me with the full sql statement please.

If there are dates available on contractual table then the where clause
should become :

WHERE ([Project Codes].Project=[enter project]
AND [event schedule].eventdate Between [first date] And [second date])
OR (contractual.[link project]=[and enter link project]
AND [contractual].eventdate Between [first date] And [second date])

- Raoul


lynn atkinson said:
Tried this and am afraid it doesnt work. It find only "link projects" within
these dates and displays ALL "projects"

:

Might this work for you?

WHERE ([Project Codes].Project=[enter project]
OR contractual.[link project]=[and enter link project])
AND [event schedule].eventdate Between [first date] And [second date]


- Raoul

:

Sorry if this has been asked before, but I cannot find a similar posting.
I am trying to set up a parameter query to find a project name (fieldname=
project) between 2 dates (fieldname=attendeddate) OR link project (fieldname
= linkproject) between the same 2 dates (fieldname=attendeddate).
I do not want the user to have to put in the date range twice.
the WHERE statement in the sql is as follows

WHERE ((([Project Codes].Project)=[enter project]) AND (([event
schedule].eventdate) Between [first date] And [second date])) OR
(((contractual.[link project])=[and enter link project]));

but this finds the project between the dates, but not the link project
between the dates.

Can I state that I want to find project OR link project between the same 2
dates.

Hope this makes sense?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top