Parameter Query

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

Guest

Hi.
I'm trying to do a simple revenue report in Access by Company Code.
I have two criterias in my query. The most important is in the Account
number field (I only want revenue accounts). I also tried to do a parameter
criteria to choose the company code. When I do, Access does not perform the
account number criteria.
Can you have criterias on 2 different fields?
 
Yes you can have criteria on 2 different fields which are evaluated left to
right. So if Account number field is your 1st criteria, Company Code would
only be evaluated against records matching the Account number. HTH
 
Hi.
I'm trying to do a simple revenue report in Access by Company Code.
I have two criterias in my query. The most important is in the Account
number field (I only want revenue accounts). I also tried to do a parameter
criteria to choose the company code. When I do, Access does not perform the
account number criteria.
Can you have criterias on 2 different fields?
Click to expand...

To answer your direct question, yes you can have criteria on 2 (and 3
and 4, etc.) different fields as criteria.
However, as you haven't posted your SQL, it's not possible to tell you
why your query is not working. If you care to post the SQL back,
include the datatypes of whatever fields are used in the criteria.
 
Fred:
Following is my SQL.

SELECT [Jan 2005 P&L].[Co], [Jan 2005 P&L].[Acct], [Jan 2005 P&L].[Sub],
[Acct] & "-" & [Sub] AS [Account Nbr], [Jan 2005 P&L].[Category], [Jan 2005
P&L].[Desc], [Jan 2005 P&L].[Amount]
FROM [Jan 2005 P&L]
WHERE ((([Jan 2005 P&L].[Co]) Like [Enter Company:]) And (([Jan 2005
P&L].[Acct]) Between 3000 And 3999)) Or (([Enter Company:] Is Null))
ORDER BY [Jan 2005 P&L].[Co], [Jan 2005 P&L].[Acct], [Jan 2005 P&L].[Sub];
Co is a text field, Acct is a number field.
 
Fred:
Following is my SQL.

SELECT [Jan 2005 P&L].[Co], [Jan 2005 P&L].[Acct], [Jan 2005 P&L].[Sub],
[Acct] & "-" & [Sub] AS [Account Nbr], [Jan 2005 P&L].[Category], [Jan 2005
P&L].[Desc], [Jan 2005 P&L].[Amount]
FROM [Jan 2005 P&L]
WHERE ((([Jan 2005 P&L].[Co]) Like [Enter Company:]) And (([Jan 2005
P&L].[Acct]) Between 3000 And 3999)) Or (([Enter Company:] Is Null))
ORDER BY [Jan 2005 P&L].[Co], [Jan 2005 P&L].[Acct], [Jan 2005 P&L].[Sub];
Co is a text field, Acct is a number field.

fredg said:
To answer your direct question, yes you can have criteria on 2 (and 3
and 4, etc.) different fields as criteria.
However, as you haven't posted your SQL, it's not possible to tell you
why your query is not working. If you care to post the SQL back,
include the datatypes of whatever fields are used in the criteria.
Click to expand...
Click to expand...

Why are you using "Like [[Enter Company:] " when you aren't using any
wildcard?
If you are not using a wildcard, then use
WHERE [Jan 2005 P&L].[Co] = [Enter Company:].

If you wish to show only those records where the [Acct] is within the
indicated range for the Company entered in the [prompt] (and allow
just the beginning part of the company name to be entered):
.......
WHERE [Jan 2005 P&L].[Co] Like [Enter Company:] & "*" And [Jan 2005
P&L].[Acct] Between 3000 And 3999
ORDER BY etc ....

If you wish to show as above, OR show ALL the records if nothing is
entered in the prompt:

WHERE ([Jan 2005 P&L].[Co] Like [Enter Company:] & "*" And [Jan 2005
P&L].[Acct] Between 3000 And 3999) Or [Enter Company:] Is Null
Order By etc ...

Part of your problem may be in the actual datatype of the [CO] field.
You write that it is Text, but if you are actually storing [CO] as a
Number in this table (such as a LookUp field or from the bound column
of a Combo box), then you should write:

WHERE ([Jan 2005 P&L].[CO] = [Enter Company ID:] etc..
and enter the actual [CompanyID] number value, not the Company Name as
text.

It's best to use a parameter form with a combo box to assure the
correct CompanyID value is selected. The bound column of the combo box
should be the CompanyID field.
Now you would use, as query criteria:

WHERE ([Jan 2005 P&L].[Co] = forms!FormName!ComboBoxName And [Jan 2005
P&L].[Acct] etc ....) Or forms!FormName!ComboBoxName Is Null
Order By etc.

I hope this has helped.
 
Fred:
Unfortunately, that did not work.
I'm still getting all accounts.
Our companies are designated by alpha characters.
I want a person to be able to leave the enter company input box blank to get
all companies.
I seem to be getting an additional parameter for Jan 2005. I'm not sure why.

I usually don't work with SQL view. I usually use Design View.
I'm not sure what I'm doing wrong.

I would think the syntax would need to be (Enter Company or Enter Company is
null)
and Account between 3000 and 3999 (our revenue accounts).
 
Pardon me for jumping in

SELECT A.[Co], A.[Acct], A.[Sub],
[Acct] & "-" & [Sub] AS [Account Nbr], A.[Category],
A.[Desc], A.[Amount]
FROM [Jan 2005 P&L] AS A
WHERE (A.[Co] = [Enter Company:] And A.[Acct] Between 3000 And 3999) Or
([Enter Company:] Is Null And A.[Acct] Between 3000 And 3999)
ORDER BY A.[Co], A.[Acct], A.[Sub];

IF Co is NEVER NULL, then you can simplify this a bit.

SELECT A.[Co], A.[Acct], A.[Sub],
[Acct] & "-" & [Sub] AS [Account Nbr], A.[Category],
A.[Desc], A.[Amount]
FROM [Jan 2005 P&L] AS A
WHERE (A.[Co] LIKE NZ([Enter Company:], "*") And A.[Acct] Between 3000 And 3999)
ORDER BY A.[Co], A.[Acct], A.[Sub]
Fred:
Following is my SQL.

SELECT [Jan 2005 P&L].[Co], [Jan 2005 P&L].[Acct], [Jan 2005 P&L].[Sub],
[Acct] & "-" & [Sub] AS [Account Nbr], [Jan 2005 P&L].[Category], [Jan 2005
P&L].[Desc], [Jan 2005 P&L].[Amount]
FROM [Jan 2005 P&L]
WHERE ((([Jan 2005 P&L].[Co]) Like [Enter Company:]) And (([Jan 2005
P&L].[Acct]) Between 3000 And 3999)) Or (([Enter Company:] Is Null))
ORDER BY [Jan 2005 P&L].[Co], [Jan 2005 P&L].[Acct], [Jan 2005 P&L].[Sub];
Co is a text field, Acct is a number field.

fredg said:
To answer your direct question, yes you can have criteria on 2 (and 3
and 4, etc.) different fields as criteria.
However, as you haven't posted your SQL, it's not possible to tell you
why your query is not working. If you care to post the SQL back,
include the datatypes of whatever fields are used in the criteria.
Click to expand...
Click to expand...
 
In Design View, if you want the query to return all records when the
parameter is blank put [Enter Company] in the 1st row then on the next row
put [Enter Company] Is Null. Your 2nd criteria Between 3000 and 3999 will
need to be put on both rows if you want this to apply in both situations.
HTH

Sheila (I don't work in SQL view either!)
 
Back
Top