parameters

  • Thread starter Thread starter Cillies
  • Start date Start date
C

Cillies

Hi I'm having trouble setting Parameters. What I want to do is be abl
to select a client and a date as parameters which is fine, but I als
need to be fit to select just a client, and get all client info back
Or just select a date, which will return all clients under that date

Below is a copy of My SQL:

SELECT [Time Card Hours].DateWorked, Projects.ProjectID
Clients.CompanyName, Projects.ProjectName, Sum(Projects.ProjectStartup
AS SumOfProjectStartup, Sum(Projects.ProjectCall) AS SumOfProjectCall
Sum(Projects.ProjectTotalBillingEstimate) A
SumOfProjectTotalBillingEstimate, Sum(Projects.ProjectLeads) A
SumOfProjectLeads
FROM (Clients INNER JOIN (Projects INNER JOIN [Time Card Expenses] O
Projects.ProjectID = [Time Card Expenses].ProjectID) O
Clients.ClientID = Projects.ClientID) INNER JOIN [Time Card Hours] O
Projects.ProjectID = [Time Card Hours].ProjectID
GROUP BY [Time Card Hours].DateWorked, Projects.ProjectID
Clients.CompanyName, Projects.ProjectName
HAVING (((Clients.CompanyName)=[Enter Client])) OR ((([Enter Client]
Is Null))And ((([Time Card Hours].DateWorked) Between [Enter Star
Date] And [Enter End Date])) OR ((([Time Card Hours].DateWorked) I
Null))
ORDER BY Clients.CompanyName;

This doesn't work:

E.G.
If I enter a client then request a particular date I get all th
results back for that client, irrespective of date, the same happens i
I reverse the parameter order.

when I entered code kindly provided by SuicidED which has a WHER
clause as opposed to a HAVING clause I keep getting an error message:

SELECT [Time Card Hours].DateWorked, Projects.ProjectID
Clients.CompanyName, Projects.ProjectName, Sum(Projects.ProjectStartup
AS SumOfProjectStartup, Sum(Projects.ProjectCall) AS SumOfProjectCall
Sum(Projects.ProjectTotalBillingEstimate) A
SumOfProjectTotalBillingEstimate, Sum(Projects.ProjectLeads) A
SumOfProjectLeads
FROM (Clients INNER JOIN (Projects INNER JOIN [Time Card Expenses] O
Projects.ProjectID = [Time Card Expenses].ProjectID) O
Clients.ClientID = Projects.ClientID) INNER JOIN [Time Card Hours] O
Projects.ProjectID = [Time Card Hours].ProjectID
GROUP BY [Time Card Hours].DateWorked, Projects.ProjectID
Clients.CompanyName, Projects.ProjectName
WHERE (((Clients.CompanyName)=[Enter Name]) AND (( [Time Car
Hours].DateWorked) Between [Start Date] And [End Date]));

I get an error message as below.
Syntex error (missing operator)in query expressio
'Projects.ProjectName
WHERE (((Clients.CompanyName)=[Enter Name]) AND (( [Time Car
Hours].DateWorked) Between [Start Date] And [End Date]));

Does anyone know how I can select a date and client search as well a
being able to select null values in either or to retrieve either a lis
of clients between a certain date or all of a clients activitie
throughout the year irrespective of date.

If any clarification is need please let me know
 
Cillies,

Try this modification in the WHERE clause:

WHERE (((Clients.CompanyName)=(Nz([Enter Name],"*")) AND (( [Time Card
Hours].DateWorked) Between Nz([Start Date],#01/01/1900#) And Nz([End
Date],#12/31/2100#)));

HTH,
Nikos

Cillies said:
Hi I'm having trouble setting Parameters. What I want to do is be able
to select a client and a date as parameters which is fine, but I also
need to be fit to select just a client, and get all client info back.
Or just select a date, which will return all clients under that date

Below is a copy of My SQL:

SELECT [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName, Sum(Projects.ProjectStartup)
AS SumOfProjectStartup, Sum(Projects.ProjectCall) AS SumOfProjectCall,
Sum(Projects.ProjectTotalBillingEstimate) AS
SumOfProjectTotalBillingEstimate, Sum(Projects.ProjectLeads) AS
SumOfProjectLeads
FROM (Clients INNER JOIN (Projects INNER JOIN [Time Card Expenses] ON
Projects.ProjectID = [Time Card Expenses].ProjectID) ON
Clients.ClientID = Projects.ClientID) INNER JOIN [Time Card Hours] ON
Projects.ProjectID = [Time Card Hours].ProjectID
GROUP BY [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName
HAVING (((Clients.CompanyName)=[Enter Client])) OR ((([Enter Client])
Is Null))And ((([Time Card Hours].DateWorked) Between [Enter Start
Date] And [Enter End Date])) OR ((([Time Card Hours].DateWorked) Is
Null))
ORDER BY Clients.CompanyName;

This doesn't work:

E.G.
If I enter a client then request a particular date I get all the
results back for that client, irrespective of date, the same happens if
I reverse the parameter order.

when I entered code kindly provided by SuicidED which has a WHERE
clause as opposed to a HAVING clause I keep getting an error message:

SELECT [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName, Sum(Projects.ProjectStartup)
AS SumOfProjectStartup, Sum(Projects.ProjectCall) AS SumOfProjectCall,
Sum(Projects.ProjectTotalBillingEstimate) AS
SumOfProjectTotalBillingEstimate, Sum(Projects.ProjectLeads) AS
SumOfProjectLeads
FROM (Clients INNER JOIN (Projects INNER JOIN [Time Card Expenses] ON
Projects.ProjectID = [Time Card Expenses].ProjectID) ON
Clients.ClientID = Projects.ClientID) INNER JOIN [Time Card Hours] ON
Projects.ProjectID = [Time Card Hours].ProjectID
GROUP BY [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName
WHERE (((Clients.CompanyName)=[Enter Name]) AND (( [Time Card
Hours].DateWorked) Between [Start Date] And [End Date]));

I get an error message as below.
Syntex error (missing operator)in query expression
'Projects.ProjectName
WHERE (((Clients.CompanyName)=[Enter Name]) AND (( [Time Card
Hours].DateWorked) Between [Start Date] And [End Date]));

Does anyone know how I can select a date and client search as well as
being able to select null values in either or to retrieve either a list
of clients between a certain date or all of a clients activities
throughout the year irrespective of date.

If any clarification is need please let me know.
Click to expand...
 
The parameter functionality is very limited in it's abilities. As you see,
you have surpassed them by using more than one. :D

I typically use VBA to create the query strings in code, based on the
criteria selected. This gives you much more control, plus validation
capabilities, over the process.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Cillies said:
Hi I'm having trouble setting Parameters. What I want to do is be able
to select a client and a date as parameters which is fine, but I also
need to be fit to select just a client, and get all client info back.
Or just select a date, which will return all clients under that date

Below is a copy of My SQL:

SELECT [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName, Sum(Projects.ProjectStartup)
AS SumOfProjectStartup, Sum(Projects.ProjectCall) AS SumOfProjectCall,
Sum(Projects.ProjectTotalBillingEstimate) AS
SumOfProjectTotalBillingEstimate, Sum(Projects.ProjectLeads) AS
SumOfProjectLeads
FROM (Clients INNER JOIN (Projects INNER JOIN [Time Card Expenses] ON
Projects.ProjectID = [Time Card Expenses].ProjectID) ON
Clients.ClientID = Projects.ClientID) INNER JOIN [Time Card Hours] ON
Projects.ProjectID = [Time Card Hours].ProjectID
GROUP BY [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName
HAVING (((Clients.CompanyName)=[Enter Client])) OR ((([Enter Client])
Is Null))And ((([Time Card Hours].DateWorked) Between [Enter Start
Date] And [Enter End Date])) OR ((([Time Card Hours].DateWorked) Is
Null))
ORDER BY Clients.CompanyName;

This doesn't work:

E.G.
If I enter a client then request a particular date I get all the
results back for that client, irrespective of date, the same happens if
I reverse the parameter order.

when I entered code kindly provided by SuicidED which has a WHERE
clause as opposed to a HAVING clause I keep getting an error message:

SELECT [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName, Sum(Projects.ProjectStartup)
AS SumOfProjectStartup, Sum(Projects.ProjectCall) AS SumOfProjectCall,
Sum(Projects.ProjectTotalBillingEstimate) AS
SumOfProjectTotalBillingEstimate, Sum(Projects.ProjectLeads) AS
SumOfProjectLeads
FROM (Clients INNER JOIN (Projects INNER JOIN [Time Card Expenses] ON
Projects.ProjectID = [Time Card Expenses].ProjectID) ON
Clients.ClientID = Projects.ClientID) INNER JOIN [Time Card Hours] ON
Projects.ProjectID = [Time Card Hours].ProjectID
GROUP BY [Time Card Hours].DateWorked, Projects.ProjectID,
Clients.CompanyName, Projects.ProjectName
WHERE (((Clients.CompanyName)=[Enter Name]) AND (( [Time Card
Hours].DateWorked) Between [Start Date] And [End Date]));

I get an error message as below.
Syntex error (missing operator)in query expression
'Projects.ProjectName
WHERE (((Clients.CompanyName)=[Enter Name]) AND (( [Time Card
Hours].DateWorked) Between [Start Date] And [End Date]));

Does anyone know how I can select a date and client search as well as
being able to select null values in either or to retrieve either a list
of clients between a certain date or all of a clients activities
throughout the year irrespective of date.

If any clarification is need please let me know.
Click to expand...
 
Back
Top