query problems

[Guest]

I have the followng:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not Null AND
[loan acct #] = [loan acct #]") AS Expr1
FROM Tbl_archive
GROUP BY Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not Null AND
[loan acct #] = [loan acct #]")
HAVING (((Tbl_archive.PopEnterDt)=Date()));

but the value entered in the Expr1 column is the same for ALL rows. How can
I make it to where the value for the Expr1 column looks at the value in the
[loan acct #] column for each row?

thanks in advance,
geebee

 

[John Spencer]

You need to change the criteria on each step through the records. Currently
your DLookup is returning the first record it finds where status is not null
and the loan acct# in the row is equal to the loan acct# in the row. Which
is basically the same as where status is null and loan acct # is not null

Assumption
Loan Acct # is a text field.

DLookUp("status","tbl_archive","[status] Is Not Null AND [loan acct #] ="""
& [loan acct #] & """") AS Expr1

If loan acct # is a number field then change """ to " and """" to nothing.

 

[Guest]

SELECT Tbl_archive.[Loan Acct #],
Tbl_archive.PopEnterDt,
Tbl_archive.status
FROM Tbl_archive
WHERE Tbl_archive.PopEnterDt = Date()
AND Tbl_archive.status Is Not Null;

 

[Guest]

the column is a number column. I now have the following:

DLookUp("status","tbl_archive","[status] Is Not Null AND [loan acct #] ="""
& [loan acct #] & """") AS Expr1

but the syntax is bad. whats wrong?

thanks in advance,
geebee

You need to change the criteria on each step through the records. Currently
your DLookup is returning the first record it finds where status is not null
and the loan acct# in the row is equal to the loan acct# in the row. Which
is basically the same as where status is null and loan acct # is not null

Assumption
Loan Acct # is a text field.

DLookUp("status","tbl_archive","[status] Is Not Null AND [loan acct #] ="""
& [loan acct #] & """") AS Expr1

If loan acct # is a number field then change """ to " and """" to nothing.

I have the followng:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not Null
AND
[loan acct #] = [loan acct #]") AS Expr1
FROM Tbl_archive
GROUP BY Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not Null
AND
[loan acct #] = [loan acct #]")
HAVING (((Tbl_archive.PopEnterDt)=Date()));

but the value entered in the Expr1 column is the same for ALL rows. How
can
I make it to where the value for the Expr1 column looks at the value in
the
[loan acct #] column for each row?

thanks in advance,
geebee

 

[Guest]

hi,

Jerry, your query is only a simple query and I want to include a domain
aggregate function, like:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive", "[popentrerdt] = Date()
AND [loan acct #] = '' & [loan acct #] & ) AS Expr1;
FROM Tbl_archive
GROUP BY Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt, Tbl_archive.status
HAVING (((Tbl_archive.PopEnterDt)=Date()));

but I am having NO luck!

please help

thanks in advance,
geebee

SELECT Tbl_archive.[Loan Acct #],
Tbl_archive.PopEnterDt,
Tbl_archive.status
FROM Tbl_archive
WHERE Tbl_archive.PopEnterDt = Date()
AND Tbl_archive.status Is Not Null;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the followng:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not Null AND
[loan acct #] = [loan acct #]") AS Expr1
FROM Tbl_archive
GROUP BY Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not Null AND
[loan acct #] = [loan acct #]")
HAVING (((Tbl_archive.PopEnterDt)=Date()));

but the value entered in the Expr1 column is the same for ALL rows. How can
I make it to where the value for the Expr1 column looks at the value in the
[loan acct #] column for each row?

thanks in advance,
geebee

 

[John Spencer]

As I said in my earlier post, if your Loan Acct # field is a number field
you must get rid of the delimiters around it

DLookUp("status","tbl_archive","[status] Is Not Null AND [loan acct #] = " &
[loan acct #] )AS Expr1

the column is a number column. I now have the following:

DLookUp("status","tbl_archive","[status] Is Not Null AND [loan acct #]
="""
& [loan acct #] & """") AS Expr1

but the syntax is bad. whats wrong?

thanks in advance,
geebee

You need to change the criteria on each step through the records.
Currently
your DLookup is returning the first record it finds where status is not
null
and the loan acct# in the row is equal to the loan acct# in the row.
Which
is basically the same as where status is null and loan acct # is not null

Assumption
Loan Acct # is a text field.

DLookUp("status","tbl_archive","[status] Is Not Null AND [loan acct #]
="""
& [loan acct #] & """") AS Expr1

If loan acct # is a number field then change """ to " and """" to
nothing.

I have the followng:

SELECT Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not
Null
AND
[loan acct #] = [loan acct #]") AS Expr1
FROM Tbl_archive
GROUP BY Tbl_archive.[Loan Acct #], Tbl_archive.PopEnterDt,
Tbl_archive.status, DLookUp("status","tbl_archive","[status] Is Not
Null
AND
[loan acct #] = [loan acct #]")
HAVING (((Tbl_archive.PopEnterDt)=Date()));

but the value entered in the Expr1 column is the same for ALL rows.
How
can
I make it to where the value for the Expr1 column looks at the value in
the
[loan acct #] column for each row?

thanks in advance,
geebee