criteria failure

A

angie

i have a query consisting of the following fields: description, date and
amount. i have created two more fields-expressions. i.e. Expr1=Month([date])
and Expr2=Year([date]).
i have a main form which consists of three combos that serve as criteria
selection for my query: combo1=description selection, combo2=month selection
and combo3=year selection.
i have entered the following criteria in my query: description field=iif
([forms]![main form]![combo1] is null;[description];[forms]![main
form]![combo1]) which works fine, that is if the combo has no data the query
returns all the records and if the combo has criteria the query filters the
records accordingly.
i have entered the same criteria (with the corresponding combo and field
names in the iif function) for Expr1 and Expr2 and the query does not work
properly.
why is this happening? how can i solve this problem?
 
O

Ofer Cohen

Can you post the SQL?

Alse, try as a criteria for the query something like

Select * From TableName Where (description = [forms]![main form]![combo1] Or
[forms]![main form]![combo1] Is Null) And (SecondFieldName = [forms]![main
form]![combo1] Or [forms]![main form]![combo1] Is Null)
 
A

angie

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ((([ACCOUNT EXPENSES-TBL].DESCRIPTION)=IIf([Forms]![MAIN
FORM]![EXPENSE SELECTION] Is Null,[DESCRIPTION],[Forms]![MAIN FORM]![EXPENSE
SELECTION])) AND ((Month([DATE]))=IIf([Forms]![MAIN FORM]![MONTH SELECTION]
Is Null,[Expr1],[Forms]![MAIN FORM]![MONTH SELECTION])) AND
((Year([DATE]))=IIf([Forms]![MAIN FORM]![YEAR SELECTION] Is
Null,[Expr2],[Forms]![MAIN FORM]![YEAR SELECTION])));

p.s. there are many criteria because the query is based on another query
(that the criteria work fine). i have a problem only with the two expressions.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Can you post the SQL?

Alse, try as a criteria for the query something like

Select * From TableName Where (description = [forms]![main form]![combo1] Or
[forms]![main form]![combo1] Is Null) And (SecondFieldName = [forms]![main
form]![combo1] Or [forms]![main form]![combo1] Is Null)



--
Good Luck
BS"D


angie said:
i have a query consisting of the following fields: description, date and
amount. i have created two more fields-expressions. i.e. Expr1=Month([date])
and Expr2=Year([date]).
i have a main form which consists of three combos that serve as criteria
selection for my query: combo1=description selection, combo2=month selection
and combo3=year selection.
i have entered the following criteria in my query: description field=iif
([forms]![main form]![combo1] is null;[description];[forms]![main
form]![combo1]) which works fine, that is if the combo has no data the query
returns all the records and if the combo has criteria the query filters the
records accordingly.
i have entered the same criteria (with the corresponding combo and field
names in the iif function) for Expr1 and Expr2 and the query does not work
properly.
why is this happening? how can i solve this problem?
Click to expand...
Click to expand...
 
O

Ofer Cohen

Try

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
[Forms]![MAIN FORM]![EXPENSE SELECTION] Is Null) AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or [Forms]![MAIN
FORM]![MONTH SELECTION] Is Null) AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or [Forms]![MAIN
FORM]![YEAR SELECTION] Is Null)
--
Good Luck
BS"D


angie said:
SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ((([ACCOUNT EXPENSES-TBL].DESCRIPTION)=IIf([Forms]![MAIN
FORM]![EXPENSE SELECTION] Is Null,[DESCRIPTION],[Forms]![MAIN FORM]![EXPENSE
SELECTION])) AND ((Month([DATE]))=IIf([Forms]![MAIN FORM]![MONTH SELECTION]
Is Null,[Expr1],[Forms]![MAIN FORM]![MONTH SELECTION])) AND
((Year([DATE]))=IIf([Forms]![MAIN FORM]![YEAR SELECTION] Is
Null,[Expr2],[Forms]![MAIN FORM]![YEAR SELECTION])));

p.s. there are many criteria because the query is based on another query
(that the criteria work fine). i have a problem only with the two expressions.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Can you post the SQL?

Alse, try as a criteria for the query something like

Select * From TableName Where (description = [forms]![main form]![combo1] Or
[forms]![main form]![combo1] Is Null) And (SecondFieldName = [forms]![main
form]![combo1] Or [forms]![main form]![combo1] Is Null)



--
Good Luck
BS"D


angie said:
i have a query consisting of the following fields: description, date and
amount. i have created two more fields-expressions. i.e. Expr1=Month([date])
and Expr2=Year([date]).
i have a main form which consists of three combos that serve as criteria
selection for my query: combo1=description selection, combo2=month selection
and combo3=year selection.
i have entered the following criteria in my query: description field=iif
([forms]![main form]![combo1] is null;[description];[forms]![main
form]![combo1]) which works fine, that is if the combo has no data the query
returns all the records and if the combo has criteria the query filters the
records accordingly.
i have entered the same criteria (with the corresponding combo and field
names in the iif function) for Expr1 and Expr2 and the query does not work
properly.
why is this happening? how can i solve this problem?
Click to expand...
Click to expand...
Click to expand...
 
A

angie

i have tried this and it works only when the combos have data. if the combos
are null i still get no data. i want the query to display all the data if the
combos are null. how could i do that?

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Try

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
[Forms]![MAIN FORM]![EXPENSE SELECTION] Is Null) AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or [Forms]![MAIN
FORM]![MONTH SELECTION] Is Null) AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or [Forms]![MAIN
FORM]![YEAR SELECTION] Is Null)
--
Good Luck
BS"D


angie said:
SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ((([ACCOUNT EXPENSES-TBL].DESCRIPTION)=IIf([Forms]![MAIN
FORM]![EXPENSE SELECTION] Is Null,[DESCRIPTION],[Forms]![MAIN FORM]![EXPENSE
SELECTION])) AND ((Month([DATE]))=IIf([Forms]![MAIN FORM]![MONTH SELECTION]
Is Null,[Expr1],[Forms]![MAIN FORM]![MONTH SELECTION])) AND
((Year([DATE]))=IIf([Forms]![MAIN FORM]![YEAR SELECTION] Is
Null,[Expr2],[Forms]![MAIN FORM]![YEAR SELECTION])));

p.s. there are many criteria because the query is based on another query
(that the criteria work fine). i have a problem only with the two expressions.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Can you post the SQL?

Alse, try as a criteria for the query something like

Select * From TableName Where (description = [forms]![main form]![combo1] Or
[forms]![main form]![combo1] Is Null) And (SecondFieldName = [forms]![main
form]![combo1] Or [forms]![main form]![combo1] Is Null)



--
Good Luck
BS"D


:

i have a query consisting of the following fields: description, date and
amount. i have created two more fields-expressions. i.e. Expr1=Month([date])
and Expr2=Year([date]).
i have a main form which consists of three combos that serve as criteria
selection for my query: combo1=description selection, combo2=month selection
and combo3=year selection.
i have entered the following criteria in my query: description field=iif
([forms]![main form]![combo1] is null;[description];[forms]![main
form]![combo1]) which works fine, that is if the combo has no data the query
returns all the records and if the combo has criteria the query filters the
records accordingly.
i have entered the same criteria (with the corresponding combo and field
names in the iif function) for Expr1 and Expr2 and the query does not work
properly.
why is this happening? how can i solve this problem?
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

Another option, mybe the combo are not null but empty

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
Trim([Forms]![MAIN FORM]![EXPENSE SELECTION]) & "" = "") AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or Trim([Forms]![MAIN
FORM]![MONTH SELECTION]) & "" = "") AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or Trim([Forms]![MAIN
FORM]![YEAR SELECTION]) & "" = "")

--
Good Luck
BS"D


angie said:
i have tried this and it works only when the combos have data. if the combos
are null i still get no data. i want the query to display all the data if the
combos are null. how could i do that?

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Try

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
[Forms]![MAIN FORM]![EXPENSE SELECTION] Is Null) AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or [Forms]![MAIN
FORM]![MONTH SELECTION] Is Null) AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or [Forms]![MAIN
FORM]![YEAR SELECTION] Is Null)
--
Good Luck
BS"D


angie said:
SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ((([ACCOUNT EXPENSES-TBL].DESCRIPTION)=IIf([Forms]![MAIN
FORM]![EXPENSE SELECTION] Is Null,[DESCRIPTION],[Forms]![MAIN FORM]![EXPENSE
SELECTION])) AND ((Month([DATE]))=IIf([Forms]![MAIN FORM]![MONTH SELECTION]
Is Null,[Expr1],[Forms]![MAIN FORM]![MONTH SELECTION])) AND
((Year([DATE]))=IIf([Forms]![MAIN FORM]![YEAR SELECTION] Is
Null,[Expr2],[Forms]![MAIN FORM]![YEAR SELECTION])));

p.s. there are many criteria because the query is based on another query
(that the criteria work fine). i have a problem only with the two expressions.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Can you post the SQL?

Alse, try as a criteria for the query something like

Select * From TableName Where (description = [forms]![main form]![combo1] Or
[forms]![main form]![combo1] Is Null) And (SecondFieldName = [forms]![main
form]![combo1] Or [forms]![main form]![combo1] Is Null)



--
Good Luck
BS"D


:

i have a query consisting of the following fields: description, date and
amount. i have created two more fields-expressions. i.e. Expr1=Month([date])
and Expr2=Year([date]).
i have a main form which consists of three combos that serve as criteria
selection for my query: combo1=description selection, combo2=month selection
and combo3=year selection.
i have entered the following criteria in my query: description field=iif
([forms]![main form]![combo1] is null;[description];[forms]![main
form]![combo1]) which works fine, that is if the combo has no data the query
returns all the records and if the combo has criteria the query filters the
records accordingly.
i have entered the same criteria (with the corresponding combo and field
names in the iif function) for Expr1 and Expr2 and the query does not work
properly.
why is this happening? how can i solve this problem?
Click to expand...
Click to expand...
Click to expand...
 
A

angie

it works when criteria are selected in the combos but when there are no
criteria in all three combos the query displays data based on the most recent
criteria selection, it somehow stores the last criteria selection instead of
returning all the data.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Another option, mybe the combo are not null but empty

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
Trim([Forms]![MAIN FORM]![EXPENSE SELECTION]) & "" = "") AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or Trim([Forms]![MAIN
FORM]![MONTH SELECTION]) & "" = "") AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or Trim([Forms]![MAIN
FORM]![YEAR SELECTION]) & "" = "")

--
Good Luck
BS"D


angie said:
i have tried this and it works only when the combos have data. if the combos
are null i still get no data. i want the query to display all the data if the
combos are null. how could i do that?

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Try

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
[Forms]![MAIN FORM]![EXPENSE SELECTION] Is Null) AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or [Forms]![MAIN
FORM]![MONTH SELECTION] Is Null) AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or [Forms]![MAIN
FORM]![YEAR SELECTION] Is Null)
--
Good Luck
BS"D


:

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ((([ACCOUNT EXPENSES-TBL].DESCRIPTION)=IIf([Forms]![MAIN
FORM]![EXPENSE SELECTION] Is Null,[DESCRIPTION],[Forms]![MAIN FORM]![EXPENSE
SELECTION])) AND ((Month([DATE]))=IIf([Forms]![MAIN FORM]![MONTH SELECTION]
Is Null,[Expr1],[Forms]![MAIN FORM]![MONTH SELECTION])) AND
((Year([DATE]))=IIf([Forms]![MAIN FORM]![YEAR SELECTION] Is
Null,[Expr2],[Forms]![MAIN FORM]![YEAR SELECTION])));

p.s. there are many criteria because the query is based on another query
(that the criteria work fine). i have a problem only with the two expressions.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Can you post the SQL?

Alse, try as a criteria for the query something like

Select * From TableName Where (description = [forms]![main form]![combo1] Or
[forms]![main form]![combo1] Is Null) And (SecondFieldName = [forms]![main
form]![combo1] Or [forms]![main form]![combo1] Is Null)



--
Good Luck
BS"D


:

i have a query consisting of the following fields: description, date and
amount. i have created two more fields-expressions. i.e. Expr1=Month([date])
and Expr2=Year([date]).
i have a main form which consists of three combos that serve as criteria
selection for my query: combo1=description selection, combo2=month selection
and combo3=year selection.
i have entered the following criteria in my query: description field=iif
([forms]![main form]![combo1] is null;[description];[forms]![main
form]![combo1]) which works fine, that is if the combo has no data the query
returns all the records and if the combo has criteria the query filters the
records accordingly.
i have entered the same criteria (with the corresponding combo and field
names in the iif function) for Expr1 and Expr2 and the query does not work
properly.
why is this happening? how can i solve this problem?
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

Its very hard know without seeing the actual database,
Check the values returned from the combo's

Run the form, the open the immidate window (Press Ctrl + G) and type

?[Forms]![MAIN FORM]![YEAR SELECTION]

Press Enter and see which value is returned.
Do the same for each text box

--
Good Luck
BS"D


angie said:
it works when criteria are selected in the combos but when there are no
criteria in all three combos the query displays data based on the most recent
criteria selection, it somehow stores the last criteria selection instead of
returning all the data.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:
Another option, mybe the combo are not null but empty

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
Trim([Forms]![MAIN FORM]![EXPENSE SELECTION]) & "" = "") AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or Trim([Forms]![MAIN
FORM]![MONTH SELECTION]) & "" = "") AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or Trim([Forms]![MAIN
FORM]![YEAR SELECTION]) & "" = "")

--
Good Luck
BS"D


angie said:
i have tried this and it works only when the combos have data. if the combos
are null i still get no data. i want the query to display all the data if the
combos are null. how could i do that?

Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Try

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ([ACCOUNT EXPENSES-TBL].DESCRIPTION = [Forms]![MAIN FORM]![EXPENSE Or
[Forms]![MAIN FORM]![EXPENSE SELECTION] Is Null) AND
(Month([DATE])=[Forms]![MAIN FORM]![MONTH SELECTION] Or [Forms]![MAIN
FORM]![MONTH SELECTION] Is Null) AND
(Year([DATE])=[Forms]![MAIN FORM]![YEAR SELECTION] Or [Forms]![MAIN
FORM]![YEAR SELECTION] Is Null)
--
Good Luck
BS"D


:

SELECT [ACCOUNTS*CRITERIA-QRY].BANK, [ACCOUNTS*CRITERIA-QRY].[Account
NUMBER], [ACCOUNTS*CRITERIA-QRY].IBAN, [ACCOUNTS*CRITERIA-QRY].[Account
HOLDER], [ACCOUNTS*CRITERIA-QRY].[Account TYPE],
[ACCOUNTS*CRITERIA-QRY].Currency, [ACCOUNTS*CRITERIA-QRY].Establishment,
[ACCOUNTS*CRITERIA-QRY].Notes, [ACCOUNTS*CRITERIA-QRY].[Account Status],
[ACCOUNTS*CRITERIA-QRY].[Cheque Notebook],
[ACCOUNTS*CRITERIA-QRY].[Credit(Cash) Card(s)], [ACCOUNTS*CRITERIA-QRY].Web,
[ACCOUNTS*CRITERIA-QRY].Extrait, [ACCOUNTS*CRITERIA-QRY].Bankbook, [ACCOUNT
EXPENSES-TBL].DESCRIPTION, [ACCOUNT EXPENSES-TBL].DATE, [ACCOUNT
EXPENSES-TBL].AMOUNT, Month([DATE]) AS Expr1, Year([DATE]) AS Expr2
FROM [ACCOUNTS*CRITERIA-QRY] INNER JOIN [ACCOUNT EXPENSES-TBL] ON
[ACCOUNTS*CRITERIA-QRY].[Account NUMBER] = [ACCOUNT EXPENSES-TBL].[Account
NUMBER]
WHERE ((([ACCOUNT EXPENSES-TBL].DESCRIPTION)=IIf([Forms]![MAIN
FORM]![EXPENSE SELECTION] Is Null,[DESCRIPTION],[Forms]![MAIN FORM]![EXPENSE
SELECTION])) AND ((Month([DATE]))=IIf([Forms]![MAIN FORM]![MONTH SELECTION]
Is Null,[Expr1],[Forms]![MAIN FORM]![MONTH SELECTION])) AND
((Year([DATE]))=IIf([Forms]![MAIN FORM]![YEAR SELECTION] Is
Null,[Expr2],[Forms]![MAIN FORM]![YEAR SELECTION])));

p.s. there are many criteria because the query is based on another query
(that the criteria work fine). i have a problem only with the two expressions.

Ο χÏήστης "Ofer Cohen" έγγÏαψε:

Can you post the SQL?

Alse, try as a criteria for the query something like

Select * From TableName Where (description = [forms]![main form]![combo1] Or
[forms]![main form]![combo1] Is Null) And (SecondFieldName = [forms]![main
form]![combo1] Or [forms]![main form]![combo1] Is Null)



--
Good Luck
BS"D


:

i have a query consisting of the following fields: description, date and
amount. i have created two more fields-expressions. i.e. Expr1=Month([date])
and Expr2=Year([date]).
i have a main form which consists of three combos that serve as criteria
selection for my query: combo1=description selection, combo2=month selection
and combo3=year selection.
i have entered the following criteria in my query: description field=iif
([forms]![main form]![combo1] is null;[description];[forms]![main
form]![combo1]) which works fine, that is if the combo has no data the query
returns all the records and if the combo has criteria the query filters the
records accordingly.
i have entered the same criteria (with the corresponding combo and field
names in the iif function) for Expr1 and Expr2 and the query does not work
properly.
why is this happening? how can i solve this problem?
Click to expand...
Click to expand...
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

IIf statement in query criteria 5
Query Not Updating 1
Query with Combo Boxes on a Form 1
list box criteria 4
Default Query Criteria 1
Combo box contents doesn't change when filtering combo box is chan 2
Compare results of two expressions within query 1
query from combo 1

Top