yikes

G

Guest

hi,

i have been working on this query forever. looks like u cant use dlookup
functions within a query. is that right? i have:

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

getting an error# for the [Expr1] column. by the way, the [loan acct #] is
a number datatype.

thanks in advance,
geebee
 
S

strive4peace

Yes, but you need to delimit dates with #

SELECT Tbl_archive.[Loan Acct #],
Tbl_archive.PopEnterDt,
Tbl_archive.status,
DLookUp("status","tbl_archive","[popenterdt] = #" & Date() &
"# AND [loan acct #] = " & [loan acct #] ) AS StatusLookup
FROM Tbl_archive;

you can substitute Expr1 for whatever you want to call the
column

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
 
G

Guest

hi,

I now have the following:

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

still not working. getting an error# for each row in the Expr1 column.

geebee


strive4peace said:
Yes, but you need to delimit dates with #

SELECT Tbl_archive.[Loan Acct #],
Tbl_archive.PopEnterDt,
Tbl_archive.status,
DLookUp("status","tbl_archive","[popenterdt] = #" & Date() &
"# AND [loan acct #] = " & [loan acct #] ) AS StatusLookup
FROM Tbl_archive;

you can substitute Expr1 for whatever you want to call the
column

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

hi,

i have been working on this query forever. looks like u cant use dlookup
functions within a query. is that right? i have:

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

getting an error# for the [Expr1] column. by the way, the [loan acct #] is
a number datatype.

thanks in advance,
geebee
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear GeeBee:

Could it be you don't want "status" in quotes? Is it the name of a column?

Tom Ellison


geebee said:
hi,

I now have the following:

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

still not working. getting an error# for each row in the Expr1 column.

geebee


strive4peace said:
Yes, but you need to delimit dates with #

SELECT Tbl_archive.[Loan Acct #],
Tbl_archive.PopEnterDt,
Tbl_archive.status,
DLookUp("status","tbl_archive","[popenterdt] = #" & Date() &
"# AND [loan acct #] = " & [loan acct #] ) AS StatusLookup
FROM Tbl_archive;

you can substitute Expr1 for whatever you want to call the
column

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

hi,

i have been working on this query forever. looks like u cant use
dlookup
functions within a query. is that right? i have:

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

getting an error# for the [Expr1] column. by the way, the [loan acct
#] is
a number datatype.

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

yes. it is the name of a column, and here is what i now have:

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

getting an #error in the row values still, and also getting an error message:

"syntax error (missing) operator) in query expression 'popenterdt] =
#3/17/2006# AND [loan acct #] = '.

i dont know what to do



Tom Ellison said:
Dear GeeBee:

Could it be you don't want "status" in quotes? Is it the name of a column?

Tom Ellison


geebee said:
hi,

I now have the following:

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

still not working. getting an error# for each row in the Expr1 column.

geebee


strive4peace said:
Yes, but you need to delimit dates with #

SELECT Tbl_archive.[Loan Acct #],
Tbl_archive.PopEnterDt,
Tbl_archive.status,
DLookUp("status","tbl_archive","[popenterdt] = #" & Date() &
"# AND [loan acct #] = " & [loan acct #] ) AS StatusLookup
FROM Tbl_archive;

you can substitute Expr1 for whatever you want to call the
column

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


geebee wrote:
hi,

i have been working on this query forever. looks like u cant use
dlookup
functions within a query. is that right? i have:

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

getting an error# for the [Expr1] column. by the way, the [loan acct
#] is
a number datatype.

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

"syntax error (missing) operator) in query expression 'popenterdt] =
#3/17/2006# AND [loan acct #] = '.
Click to expand...

Do you have any records where Loan Acct # is NULL (blank)? They'd
cause this error since the DLookup criterion would end with an equals
sign.

Try

DLookUp("[status]","tbl_archive","[popenterdt] = #" &
Date() & "# AND [loan acct #] = " & NZ([loan acct #]))

to look up the (presumably nonexistant) account number 0 if it is
NULL.

John W. Vinson[MVP]
 
T

Tom Ellison

Dear GeeBee:

I must have been overtired. This actually makes no sense. Very sorry!

Tom Ellison


Tom Ellison said:
Dear GeeBee:

Could it be you don't want "status" in quotes? Is it the name of a
column?

Tom Ellison


geebee said:
hi,

I now have the following:

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

still not working. getting an error# for each row in the Expr1 column.

geebee


strive4peace said:
Yes, but you need to delimit dates with #

SELECT Tbl_archive.[Loan Acct #],
Tbl_archive.PopEnterDt,
Tbl_archive.status,
DLookUp("status","tbl_archive","[popenterdt] = #" & Date() &
"# AND [loan acct #] = " & [loan acct #] ) AS StatusLookup
FROM Tbl_archive;

you can substitute Expr1 for whatever you want to call the
column

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


geebee wrote:
hi,

i have been working on this query forever. looks like u cant use
dlookup
functions within a query. is that right? i have:

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

getting an error# for the [Expr1] column. by the way, the [loan acct
#] is
a number datatype.

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

to tag on to what John said, it would be good to use NZ
around your control reference

ALSO, make sure that you are using the NAME of the control
-- turn on the properties window (from the menu --> View,
Properties) and click on [loan acct #]

click on the ALL tab in the properties window, scroll to the
top and see what the NAME property is

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


John said:
"syntax error (missing) operator) in query expression 'popenterdt] =
#3/17/2006# AND [loan acct #] = '.
Click to expand...


Do you have any records where Loan Acct # is NULL (blank)? They'd
cause this error since the DLookup criterion would end with an equals
sign.

Try

DLookUp("[status]","tbl_archive","[popenterdt] = #" &
Date() & "# AND [loan acct #] = " & NZ([loan acct #]))

to look up the (presumably nonexistant) account number 0 if it is
NULL.

John W. Vinson[MVP]
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
conditional criteria 4
change to UPDATE query 3
delete query 4
append only if [status] is different 3
query speed 5
delete query 1
join type not supported 5

Top