Re: Using If expressions with an Or expression, and a Count questi

OK, forget the second umbrella question. I figured it out from your code.

Thanks again!

"Amin" wrote:

Thanks a lot John! So I have learned a lot in between my last post thanks to
you guys (and a cool SQL website) so I am now coding exclusively in SQL. I do
have two umbrella questions though.

1st questions:

WHERE IIF([A or B?] ="A",",Bill,Steve,",",Ted,") Like "*," & [FieldName] &
",*"

OK, I do not follow this code. Why do the quotations go on the outside of
the commas ( ie "Bill,Steve,",",Ted,")?
And what does this part do (Like "*," & [FieldName] & ",*")?
I only know how to use LIKE for similar words using %. Why are ampersands
necessary? Essentially, I have no idea what this part did.

2nd question:

What if I had wanted the following (using my horrible code)

WHERE [Field]=IIf([A or B?]="A","Bill" OR "Steve","Ted OR Billy")

So if A happens, input Bill or Steve into that Field, otherwise put Ted or
Billy into that Field.

"John Spencer" wrote:

2nd question first (as the politicians often say)

The code was an SQL Statement. IT is the ENTIRE query. You did not give us
any table or field names so what I wrote was a generic example of a query. If
you were doing this in Design view you would have followed these steps in a
new query

-- Add your table
-- Add your number field twice
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under one of the number field
-- Change the other Number field to read
Interval: 1 + (([Name of Number Field] -1) \10000
-- Run the query

Your first question is a bit more complex. With you simple example, you can
get the desired results using this in the criteria field.

IIF([A or B?]="A","Bill","Ted") OR IIF([A or B?]="A","Steve","Ted")

You cannot use IIF to set the conjunction or a comparison operator.

Another method might be to use a WHERE clause like the following.

WHERE IIF([A or B?] ="A",",Bill,Steve,",",Ted,") Like "*," & [FieldName] & ",*"

To do this in design view (the query grid), you would put the IIF statement
into a field "box" and the Like into a criteria box under the IIF.
Field: IIF([A or B?] ="A",",Bill,Steve,",",Ted,")
Criteria: Like "*," & [FieldName] & ",*"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Amin wrote:
Wow, thank you all for your help. Let me clarify what I was asking.

1st question:

IIf([A or B?]="A","Bill" OR "Steve","Ted")

So at the prompt, if the user puts in "A", I want my criteria to be: "Bill"
Or "Steve". Otherwise, I want my criteria to be "Ted". But I did learn a new
trick from your code Kelvan.

2nd question:
I can use Network Trades idea because its simplest for me (and I DO
appreciate that), but I would like to understand how John and Kelvan wrote
it. Should I have entered all of your code as is into the criteria query? Was
this a macros? I really do want to learn about this. I've bought a book and
it will arrive in a few days.

Thank you so much,
Amin





"John Spencer" wrote:

SELECT 1 + ((SomeNumberField-1) \ 10000) as Interval
, Count(SomeNumberField)
FROM SomeTable
GROUP BY (SomeNumberField-1) \ 10000

If you want the interval to be something like 10000, 20000, 30000 then
multiply the calculation by 10000

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Lord Kelvan wrote:
what you said makes no sence to me

try this if this isnt what you want please tell me what you want
directally

IIf([Strategy]="4DECIDE" OR [Strategy]= "4URGENT",
[Strategy],"4REPLY")


as for the counting this should do it

is isnt the most efficant method but i cannot think of another method
at the moment

SELECT "1-10000" AS range, Count(datefield) AS countofdatefield
FROM thetable
WHERE (((numberfield) Between 1 And 10000) AND ((datefield) between
[enter start date] and [enter end date]))
union
SELECT "10001-20000" AS range, Count(datefield) AS countofdatefield
FROM thetable
WHERE (((numberfield) Between 10001 And 20000) AND ((datefield)
between [enter start date] and [enter end date]))
union
... etc ...

hope this helps

Reagrds
Kelvan

.

Relevant Pages

  • Re: Corstabquery or normal query
    ... This is my SQL for Query: ... "John Spencer" wrote: ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Trim Data
    ... The query does run and returns all other fields correctly except this one ... "John Spencer" wrote: ... please post the SQL of the query. ... "KARL DEWEY" wrote: ...
    (microsoft.public.access.queries)
  • Re: Query expression help
    ... Yes your SQL wors great! ... SQL into a query and try to look at it in design view it gives me an error ... "John Spencer" wrote: ... quarter for that one employee. ...
    (microsoft.public.access.gettingstarted)
  • Re: Query for Left(String,5) - Access 2007
    ... instead of the linked excel file only because I can't think of anything ... "John Spencer" wrote: ... the query is going to try to link every ... The Query design view actually creates SQL. ...
    (microsoft.public.access.queries)
  • Re: Statistics and Index Choice
    ... About your second query, I think because the table is ... Amin ... > the optimizer uses PK_Orders ... Again, SQL ...
    (microsoft.public.sqlserver.server)