2 questions: DLookup and Chr(13)/Chr(10)

[tina]

hi folks, two issues here:

1) i'm having trouble with the syntax to pull two text fields' values in a
DLookup() function. never tried to do *two* fields before, maybe it's not
possible? or i'm missing something obvious? here's what i've tried:

DLookup("eFirst", "tblEmployees", "eUserID = '" & strID & "'")
pulls value from one field (eFirst), no problem.

DLookup("eFirst" & " " & "eLast", "tblEmployees", "eUserID = '" & strID &
"'")
returns runtime error 3075: Syntax error (missing operator) in query
expression 'eFirst eLast'.

DLookup("eFirst" + " " + "eLast", "tblEmployees", "eUserID = '" & strID &
"'")
again, returns runtime error 3075: Syntax error (missing operator) in query
expression 'eFirst eLast'.

DLookup("'eFirst' + ' ' + 'eLast'", "tblEmployees", "eUserID = '" & strID &
"'")
doesn't err out, but doesn't return the field values, instead returns the
following string:
eFirst eLast

2) i'm entering data into an unbound textbox on a form. when the record is
saved, the data is added to a memo field in the underlying table, using the
following code:

..p_Comments = strNew & Chr(13) & Chr(10) & Chr(13) & Chr(10) & .p_Comments

the above code works to add the strNew value, then a blank line, then the
existing value, back into the memo field - which is exactly what i want. i'm
just wondering if there is a better (shorter?) way of getting the equivalent
of two carriage returns, instead of
Chr(13) & Chr(10) & Chr(13) & Chr(10)
i tried 13, 10 and 10...and 13, 13, 10...but neither gave me a clean blank
line.

tia, everyone!

 

[Allen Browne]

Try:
DLookup("[eFirst] & "" "" & [eLast]", "tblEmployees", "eUserID = '" & strID
& "'")

The first argument resolves to:
[eFirst] & " " & [eLast]
which JET should be able to figure out.

Your basic idea for adding carriage returns is correct. In VBA code, you can
use the constant vbCrLf instead of Chr(13) & Chr(10), so:
.p_Comments = strNew & vbCrLf & vbCrLf & .p_Comments

 

[tina]

the DLookup() works perfectly, so cool! and the vbCrLf, always love to learn
something better! thanks, Allen!
btw, already using your audit solution in this db - you da man, as always!
<g>

Try:
DLookup("[eFirst] & "" "" & [eLast]", "tblEmployees", "eUserID = '" & strID
& "'")

The first argument resolves to:
[eFirst] & " " & [eLast]
which JET should be able to figure out.

Your basic idea for adding carriage returns is correct. In VBA code, you can
use the constant vbCrLf instead of Chr(13) & Chr(10), so:
.p_Comments = strNew & vbCrLf & vbCrLf & .p_Comments

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hi folks, two issues here:

1) i'm having trouble with the syntax to pull two text fields' values in a
DLookup() function. never tried to do *two* fields before, maybe it's not
possible? or i'm missing something obvious? here's what i've tried:

DLookup("eFirst", "tblEmployees", "eUserID = '" & strID & "'")
pulls value from one field (eFirst), no problem.

DLookup("eFirst" & " " & "eLast", "tblEmployees", "eUserID = '" & strID &
"'")
returns runtime error 3075: Syntax error (missing operator) in query
expression 'eFirst eLast'.

DLookup("eFirst" + " " + "eLast", "tblEmployees", "eUserID = '" & strID &
"'")
again, returns runtime error 3075: Syntax error (missing operator) in
query
expression 'eFirst eLast'.

DLookup("'eFirst' + ' ' + 'eLast'", "tblEmployees", "eUserID = '" & strID
&
"'")
doesn't err out, but doesn't return the field values, instead returns the
following string:
eFirst eLast

2) i'm entering data into an unbound textbox on a form. when the record is
saved, the data is added to a memo field in the underlying table, using
the
following code:

.p_Comments = strNew & Chr(13) & Chr(10) & Chr(13) & Chr(10) & ..p_Comments

the above code works to add the strNew value, then a blank line, then the
existing value, back into the memo field - which is exactly what i want.
i'm
just wondering if there is a better (shorter?) way of getting the
equivalent
of two carriage returns, instead of
Chr(13) & Chr(10) & Chr(13) & Chr(10)
i tried 13, 10 and 10...and 13, 13, 10...but neither gave me a clean blank
line.

tia, everyone!