conditional criteria

G

Guest

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
 
G

Guest

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

Guest

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


KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

i figured it out...
thanks


KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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.

geebee said:
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


KARL DEWEY said:
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 said:
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
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

query problems 5
yikes 7
change to UPDATE query 3
delete query 4
query speed 5
nested query 9
join type not supported 5
DUPLICATE QUERY results 1

Top