Using If expressions with an Or expression, and a Count question

[Amin]

Hi,
I am new to Access, so forgive my ignorance. I'm trying to do two separate
things. In my Criteria cell, I set an IIF expression like so,

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

What I want is that if that field has either "4DECIDE" or "4URGENT" in it, I
want it included. However, I keep getting the error that my expression is too
comlex.

Secondly, what I would like to do is count the number of records in a
certain interval of time. So this field contains numbers that go from
1-100,000. I want to count how many records are between 1-10000, 10001-20000,
20001-30000... However, I can only get it to Count the number of records in
the entire range. Right now I have to build a separate inquiry for each
interval and then count that.

Any help would be VERY appreciated.

Thanks,
Amin

 

[Lord Kelvan]

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

 

[NetworkTrade]

another way to do the counting is using calculated fields; make a query and
in design view set up a column for each block ie:
10ks: iif(thenumbers>=10000 AND thenumbers<20000,1,0)
20ks: iif(thenumbers>=20000 AND thenumbers<30000,1,0)
30ks: iif(thenumbers>=30000 AND thenumbers<40000,1,0)
etc.

this will put a value of 1 in the appropriate block for each record in the
record set.

you can then apply a sum query to this query and add the 1s of each column.

 

[John Spencer]

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
'====================================================

 

[Amin]

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

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
'====================================================

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

 

[John Spencer]

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

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

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
'====================================================

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

 

[Amin]

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.

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

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

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

 

[Amin]

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

Thanks again!

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.

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

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





:

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

 

[John Spencer]

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
'====================================================

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.

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

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





:

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

 

[Amin]

Could I have written this:

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

like this:

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

Lastly, what do the ampersands do?

Thanks for all your help!

Amin

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
'====================================================

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.

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





:

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

 

[John Spencer]

No, you can't write it that way. You could write

WHERE INSTR(1,[FieldName],IIF([A or B?] ="A","Bill Steve","Ted"))> 0

Ampersands concatenate strings together. If FieldName is Alfred then
the expression becomes
... Like "*,Alfred,*"

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

Could I have written this:

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

like this:

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

Lastly, what do the ampersands do?

Thanks for all your help!

Amin

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
'====================================================

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.

:

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





:

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