conditional criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geebee - 11 Aug 2006 21:48 GMT

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

Reply to this Message

KARL DEWEY - 12 Aug 2006 00:23 GMT

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,
>
[quoted text clipped - 23 lines]
> Thanks in advance,
> geebee

Reply to this Message

geebee - 14 Aug 2006 16:20 GMT

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
[quoted text clipped - 35 lines]
> > Thanks in advance,
> > geebee

Reply to this Message

KARL DEWEY - 14 Aug 2006 16:34 GMT

I would recommend you check your report for this. Click on menu TOOLS -
Analyze - Documenter. Click on Reports and select your report name. Under
Options select Names and Properties. Under Properties select Data
Properties. Click OK and then on the icon bar to Publish it with Word.

Use Word to search for "loan account". I think you may find where it is.

> I now have the following:
> SELECT Tbl_Archive.[Loan Acct #], Tbl_Archive.Date, Tbl_Archive.PopEnterDt,
[quoted text clipped - 52 lines]
> > > Thanks in advance,
> > > geebee

Reply to this Message

geebee - 14 Aug 2006 16:27 GMT

i figured it out...
thanks

Reply to this Message