conditional criteria

[geebee]

hi,

I have the following source behind a report:

SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
Tbl_Archive.PrevStatus, Tbl_Archive.Status FROM Tbl_Archive WHERE
(((Tbl_Archive.[Loan Acct #])=IIf(forms!frm_dahd_home!account_number_search
Like "[0-9]*",(Tbl_Archive.[Loan Acct #]) Like "*" &
forms!frm_dahd_home!account_number_search & "*",(Tbl_Archive.[Loan Acct #])
Is Not Null))) ORDER BY Tbl_Archive.[Loan Acct #], Tbl_Archive.Date;

What I am trying to do with the following part:
(((Tbl_Archive.[Loan Acct #])=IIf(forms!frm_dahd_home!account_number_search
Like "[0-9]*",(Tbl_Archive.[Loan Acct #]) Like "*" &
forms!frm_dahd_home!account_number_search & "*",(Tbl_Archive.[Loan Acct #])
Is Not Null)))

is have the query return records which are like the value entered in
[account_number_search], but have it return all rows from the table if there
is a value of "ALL" or something alphabetical in the [account_number_search]

I am having no luck with this.

Can someone lead me in the right direction?

Thanks in advance,
geebee

 

[KARL DEWEY]

This query will return records of accounts that begin with numbers 0-9 when
the complete number is entered. It will return all records if the
[forms]![frm_dahd_home]![account_number_search] is null.

SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
Tbl_Archive.PrevStatus, Tbl_Archive.Status
FROM Tbl_Archive
WHERE (((Tbl_Archive.[Loan Acct #]) Is Not Null) AND ((Tbl_Archive.[Loan
Acct #]) Like IIf([forms]![frm_dahd_home]![account_number_search] Like
"[0-9]*",[forms]![frm_dahd_home]![account_number_search],"*")))
ORDER BY Tbl_Archive.[Loan Acct #], Tbl_Archive.Date;

 

[geebee]

I now have the following:
SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
Tbl_Archive.PrevStatus, Tbl_Archive.Status
FROM Tbl_Archive
WHERE (((Tbl_Archive.[Loan Acct #]) Is Not Null) AND ((tbl_archive.[Loan
Acct #]) Like IIf([forms]![frm_dahd_home]![account_number_search] Like
"[0-9]*",[forms]![frm_dahd_home]![account_number_search] ,"*")))
ORDER BY Tbl_Archive.[Loan Acct #], Tbl_Archive.Date;

but now I am getting prompted for the tbl_archive.[loan account #] when I
run the query.

How can I prevent this?

Thanks in advance,
geebee

This query will return records of accounts that begin with numbers 0-9 when
the complete number is entered. It will return all records if the
[forms]![frm_dahd_home]![account_number_search] is null.

SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
Tbl_Archive.PrevStatus, Tbl_Archive.Status
FROM Tbl_Archive
WHERE (((Tbl_Archive.[Loan Acct #]) Is Not Null) AND ((Tbl_Archive.[Loan
Acct #]) Like IIf([forms]![frm_dahd_home]![account_number_search] Like
"[0-9]*",[forms]![frm_dahd_home]![account_number_search],"*")))
ORDER BY Tbl_Archive.[Loan Acct #], Tbl_Archive.Date;

hi,

I have the following source behind a report:

SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
Tbl_Archive.PrevStatus, Tbl_Archive.Status FROM Tbl_Archive WHERE
(((Tbl_Archive.[Loan Acct #])=IIf(forms!frm_dahd_home!account_number_search
Like "[0-9]*",(Tbl_Archive.[Loan Acct #]) Like "*" &
forms!frm_dahd_home!account_number_search & "*",(Tbl_Archive.[Loan Acct #])
Is Not Null))) ORDER BY Tbl_Archive.[Loan Acct #], Tbl_Archive.Date;

What I am trying to do with the following part:
(((Tbl_Archive.[Loan Acct #])=IIf(forms!frm_dahd_home!account_number_search
Like "[0-9]*",(Tbl_Archive.[Loan Acct #]) Like "*" &
forms!frm_dahd_home!account_number_search & "*",(Tbl_Archive.[Loan Acct #])
Is Not Null)))

is have the query return records which are like the value entered in
[account_number_search], but have it return all rows from the table if there
is a value of "ALL" or something alphabetical in the [account_number_search]

I am having no luck with this.

Can someone lead me in the right direction?

Thanks in advance,
geebee

 

[geebee]

i figured it out...
thanks

This query will return records of accounts that begin with numbers 0-9 when
the complete number is entered. It will return all records if the
[forms]![frm_dahd_home]![account_number_search] is null.

SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
Tbl_Archive.PrevStatus, Tbl_Archive.Status
FROM Tbl_Archive
WHERE (((Tbl_Archive.[Loan Acct #]) Is Not Null) AND ((Tbl_Archive.[Loan
Acct #]) Like IIf([forms]![frm_dahd_home]![account_number_search] Like
"[0-9]*",[forms]![frm_dahd_home]![account_number_search],"*")))
ORDER BY Tbl_Archive.[Loan Acct #], Tbl_Archive.Date;

hi,

I have the following source behind a report:

SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
Tbl_Archive.PrevStatus, Tbl_Archive.Status FROM Tbl_Archive WHERE
(((Tbl_Archive.[Loan Acct #])=IIf(forms!frm_dahd_home!account_number_search
Like "[0-9]*",(Tbl_Archive.[Loan Acct #]) Like "*" &
forms!frm_dahd_home!account_number_search & "*",(Tbl_Archive.[Loan Acct #])
Is Not Null))) ORDER BY Tbl_Archive.[Loan Acct #], Tbl_Archive.Date;

What I am trying to do with the following part:
(((Tbl_Archive.[Loan Acct #])=IIf(forms!frm_dahd_home!account_number_search
Like "[0-9]*",(Tbl_Archive.[Loan Acct #]) Like "*" &
forms!frm_dahd_home!account_number_search & "*",(Tbl_Archive.[Loan Acct #])
Is Not Null)))

is have the query return records which are like the value entered in
[account_number_search], but have it return all rows from the table if there
is a value of "ALL" or something alphabetical in the [account_number_search]

I am having no luck with this.

Can someone lead me in the right direction?

Thanks in advance,
geebee