parameters

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
 
N

Nikos Yannacopoulos

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...
 
M

[MVP] S.Clark

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems with dates in query 2
Customer Query 1
Dsum slow 2
Query with two Tables with date filed for Date Range 1
Query with two date field for Date range 3
Using the DatePart function in an Access 2003 HAVING clause 9
troubleshooting a Between ... And ... query with dates 1
Enter Parameter Value Message Pops Up 4

Top