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

Second question first, Access and Jet (the native engine) use * as the equivalent wildcard of %. So id you were using ADO and connecting to an SQL server you might use % and _ in place of * and ?

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

The commas outside the quotes separate the arguments of the IIF into its three component parts - Comparison; Response if True; Response if false.

The addition of the commas inside the was to ensure exact matches to Bill and Steve and preclude a record with STE being returned.

I could have used : as the delimiter instead of the comma or a space or just ignored the delimiter if. As follows

Field: IIF([A or B?] ="A","BillSteve","Ted")
Criteria: Like "*" & [FieldName] & "*"

If A or B? was A then the above would match fields with values of
Bill, ill, ills,lst, b,s, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


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: Narrowing query results
    ... It should return ALL items that match criteria unless you enter criteria on the outer query. ... controlname is the Name property of the control ... John Spencer wrote: ...
    (microsoft.public.access.queries)
  • Re: searching for similar records
    ... "John Spencer" wrote: ... anything at all with criteria or joins on partnumber. ... should only the partnumber and customer be ... Open a new query ...
    (microsoft.public.access.queries)
  • Re: Query w/Date & Time in Same Field
    ... John Spencer ... Access MVP 2002-2005, 2007 ... and then did you apply the criteria against this calculated field? ... My table I am using with this query has several ...
    (microsoft.public.access.queries)
  • Re: find orders 90 days past due
    ... Did you add your date field into the design grid a second time? ... Did you put the criteria into a criteria "cell" under the second instance of the date field. ... If i remove the < then the query runs, but shows a date of of 9/26/2009 in the calculation field. ... "John Spencer" wrote: ...
    (microsoft.public.access.queries)
  • Re: Isolate Patterns
    ... Your script was exactly was I've been looking for... ... "John Spencer" wrote: ... Do you want someone to write the SQL for the query? ... using the like criteria versus using criteria on just the stripped result. ...
    (microsoft.public.access.queries)
Loading