Creating Criteria when account number is formatted as text

[L_E_O_N]

I am a newbie and cant get my head around this problem although probably
quite a simple solution.

I have a database in which I enter details on a form which then used in a
query to update a subform below it.

The field name is NZDAccountNumber
table name is tblANZ_Statement
query name is qryANZ_Unmatched
form name is frmANZ_UNMATCHED and form field is chooseANZAccount
(ComboBox generated from original tblANZ-Statement grouping by
account to be able to select different accounts from the table.)

At the moment I have in the Criteria of the query

Like
IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-0053700-03","01-0297-0053700-03",IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-0053700-04","01-0297-0053700-04",IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-007448-03","01-0297-007448-03",IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-0074448-04","01-0297-0074448-04",'*'))))

This works fine when I select an account number but if I dont have any
account number it doesnt display everything which I want it to do.

Any help would be greatly appreciated as loosing hair quick.

Leon

 

[Michel Walsh]

Edit the query in SQL view, spot the WHERE clause, you should have something
like:

.... WHERE ... fieldName LIKE iif(....) ....

change it to:

..... WHERE ... ( Forms!frmANZ_UnMatched]![ChooseANZAccount] IS NULL OR
fieldName LIKE iif(... ) ) ....


(note that I enclosed your initial fieldName LIKE iif( ... ) with an
extra pair of ( ) and, inside them, added the

Forms!frmANZ_UnMatched]![ChooseANZAccount] IS NULL OR


It is preferable to experiment while having a copy of your original query
left undisturbed, so you can come back to it if something really wrong
happen.



Vanderghast, Access MVP

 

[L_E_O_N]

Hi Michel

Thankyou for your support and found that adding the below resolved the
problem.

Like IIf(Len([Forms]![frmANZ_UnMatched]![ChooseANZAccount])=" - -
- " Or LIKE iif(... ) ) ....

Your a fantastic help.

Many Thanks

Leon

Edit the query in SQL view, spot the WHERE clause, you should have something
like:

.... WHERE ... fieldName LIKE iif(....) ....

change it to:

..... WHERE ... ( Forms!frmANZ_UnMatched]![ChooseANZAccount] IS NULL OR
fieldName LIKE iif(... ) ) ....


(note that I enclosed your initial fieldName LIKE iif( ... ) with an
extra pair of ( ) and, inside them, added the

Forms!frmANZ_UnMatched]![ChooseANZAccount] IS NULL OR


It is preferable to experiment while having a copy of your original query
left undisturbed, so you can come back to it if something really wrong
happen.



Vanderghast, Access MVP

I am a newbie and cant get my head around this problem although probably
quite a simple solution.

I have a database in which I enter details on a form which then used in a
query to update a subform below it.

The field name is NZDAccountNumber
table name is tblANZ_Statement
query name is qryANZ_Unmatched
form name is frmANZ_UNMATCHED and form field is chooseANZAccount
(ComboBox generated from original tblANZ-Statement grouping by
account to be able to select different accounts from the table.)

At the moment I have in the Criteria of the query

Like
IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-0053700-03","01-0297-0053700-03",IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-0053700-04","01-0297-0053700-04",IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-007448-03","01-0297-007448-03",IIf([Forms]![frmANZ_UnMatched]![ChooseANZAccount]="01-0297-0074448-04","01-0297-0074448-04",'*'))))

This works fine when I select an account number but if I dont have any
account number it doesnt display everything which I want it to do.

Any help would be greatly appreciated as loosing hair quick.

Leon