Allen Browne said:Is OwnerID a Number field, or a Text field?
Also, is this on a form or a report?
The Criteria is not correct. Ignoring the Left() and UCase() until you get
it working, try this if OwnerID is a Number field:
=DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] " =
Nz([tbOwnerID],0))
Or, for a Text field:
=DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] """ & =
[tbOwnerID] & """")
The Nz() fixes the problem if the tbOwnerID is blank so the 3rd argument
does not resolve to:
[OwnerID] =
For an explanation of the extra quotes, see:
http://allenbrowne.com/casu-17.html
If this is a report, you nee to test the HasData property of the report,
e.g.:
=IIf([HasData], DLookup(...
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Bob said:When OwnerLastName is blank I am getting an #Error can it just be
blank...Thanks Bob
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Bob said:This is what I have for a number field:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
What the 2 codes do is take the first 3 letters of the last name and the
other code gives Record ID number to give them a unique code like WAL23
Walker with a record ID 23.........
I can not get your text code to work.....Thanx Bob
Allen Browne said:Is OwnerID a Number field, or a Text field?
Also, is this on a form or a report?
The Criteria is not correct. Ignoring the Left() and UCase() until you
get it working, try this if OwnerID is a Number field:
=DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] " =
Nz([tbOwnerID],0))
Or, for a Text field:
=DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] """ & =
[tbOwnerID] & """")
The Nz() fixes the problem if the tbOwnerID is blank so the 3rd argument
does not resolve to:
[OwnerID] =
For an explanation of the extra quotes, see:
http://allenbrowne.com/casu-17.html
If this is a report, you nee to test the HasData property of the report,
e.g.:
=IIf([HasData], DLookup(...
Bob said:When OwnerLastName is blank I am getting an #Error can it just be
blank...Thanks Bob
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " &
[tbOwnerID] & ""),""),3))
Allen Browne said:Your Nz() is in the wrong place.
If tbOwnerID is null, then the expression:
"OwnerID = " & tbOwnerID.Value
equates to:
OwnerID =
Naturally Access can't make any sense out of that.
Therefore, you need:
"OwnerID = " & Nz(tbOwnerID,0)
so that when tbOwnerID is null, it becomes:
OwnerID = 0
and so the error does not occur.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Bob said:This is what I have for a number field:
=NZ(DLookUp("OwnerID","tblOwnerInfo","OwnerID =" & tbOwnerID.Value),"")
What the 2 codes do is take the first 3 letters of the last name and the
other code gives Record ID number to give them a unique code like WAL23
Walker with a record ID 23.........
I can not get your text code to work.....Thanx Bob
Allen Browne said:Is OwnerID a Number field, or a Text field?
Also, is this on a form or a report?
The Criteria is not correct. Ignoring the Left() and UCase() until you
get it working, try this if OwnerID is a Number field:
=DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] " =
Nz([tbOwnerID],0))
Or, for a Text field:
=DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] """ & =
[tbOwnerID] & """")
The Nz() fixes the problem if the tbOwnerID is blank so the 3rd argument
does not resolve to:
[OwnerID] =
For an explanation of the extra quotes, see:
http://allenbrowne.com/casu-17.html
If this is a report, you nee to test the HasData property of the report,
e.g.:
=IIf([HasData], DLookup(...
When OwnerLastName is blank I am getting an #Error can it just be
blank...Thanks Bob
=UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = "
& [tbOwnerID] & ""),""),3))
Allen Browne said:In JET (the data engine in Access), the table is not capable of recording
the last date changed for each record.
But you can add a date/time field to your table, and then set it in the
BeforeUpdate event procedure of the form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[MyDate] = Now()
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Bob said:Thanks Allen I will do that, can you tell me where I can find something
about Date Stamping a field when it has changed in a table....Thanx for
your help... Bob
Bob said:But Allen it is only one field I want to track if the data has been
changed, I was then going to make a query on date change then have it
descending to see which record/field had been changed recently...thanx Bob
Allen Browne said:In JET (the data engine in Access), the table is not capable of recording
the last date changed for each record.
But you can add a date/time field to your table, and then set it in the
BeforeUpdate event procedure of the form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[MyDate] = Now()
End Sub
Bob said:Thanks Allen I will do that, can you tell me where I can find something
about Date Stamping a field when it has changed in a table....Thanx for
your help... Bob
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.