Creating Criteria when account number is formatted as text

  • Thread starter Thread starter L_E_O_N
  • Start date Start date
L

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

Michel Walsh said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top