vbCRLF

[Als Hotmail]

In the attached code I do not go to a new line in a report from the
following
Case 16
firstline = "EA DEG. on = " & DLookup("[ea degree] ", "[Warren
Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
"FC DEG. on = " & DLookup("[FC degree]", "[Warren
Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
"MM DEG. on = " & DLookup("[MM degree]", "[Warren
Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
"Exemplified on = " & DLookup("[Exemplified]",
"[Warren Lodge Membership]", "[GL ID] =GLN")

Any suggestions would be helpful
Thanks Al

[Marshall Barton]

Als Hotmail wrote:

It looks like there are syntax errors in the DLookup
criteria. If GLN is a value to match, then it need to be in
quotes:

. . . & DLookup("[ea degree] ", "[Warren Lodge
Membership]", "[GL ID] = 'GLN' ") & . . .

--
Marsh
MVP [MS Access]

[Als Hotmail]

The DLOOKUP function works. All of the information is printed without a line
feed. the information is on one line.
Thanks Al
Any other suggestions?

"Marshall Barton" <marsh...@wowway.com> wrote in message
news:nrj8v5lrvtf0414sr...@4ax.com...

[Douglas J. Steele]

I'm shocked that your DLookup works, as it's syntactically incorrect.

If you're looking for the row where [GL ID] equals the (numeric) value
stored in a variable named GLN, you need

DLookup("[ea degree] ", "[Warren Lodge Membership]", "[GL ID] =" & GLN)

If you're looking for the row where [GL ID] equals the (string) constant
GLN, you need

DLookup("[ea degree] ", "[Warren Lodge Membership]", "[GL ID] = 'GLN'")

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
(no e-mails, please!)

"Als Hotmail" <alta...@hotmail.com> wrote in message
news:u8YGcg59...@TK2MSFTNGP02.phx.gbl...

[Marshall Barton]

If the DLookup works in your code, then we are wasting a lot
of time debugging the typos you introduced ny retyping
whatever you really have.

I have never seen or heard of a problem with vbCrLf in a VBA
procedure. I can only guess that your post has other
inconsistencies that manifest the problem or you have
somehow set a variable named vbCrLf to something other than
the usual value.

--
Marsh
MVP [MS Access]

Als Hotmail wrote:
>The DLOOKUP function works. All of the information is printed without a line
>feed. the information is on one line.
>

>Any other suggestions?
>
>"Marshall Barton" wrote

[Als Hotmail]

GLN should be [GLN] it is the same value as [GL ID]
Thanks for your response Al

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:#HLADp79...@TK2MSFTNGP02.phx.gbl...

[Als Hotmail]

I am having a lot of strange things happening in this computer. I am using
Window 7 & Office Access 2007.
After some automatic backups some programs disappear, I think when I first
loaded the programs everything worked but after the updates the problems
began. Number Lock keeps unlocking, Trim function gives complier error.
I am about to restore to a earlier time.
Do you have any suggestions?

Thanks Again
Al

"Marshall Barton" <marsh...@wowway.com> wrote in message

news:3p69v51mpte0hktc1...@4ax.com...

[Marshall Barton]

It's going to be impossible to figure out a small
programming problem when your system is not properly
configured and stable. OTOH, you could be doing things that
are causing some of those symptoms. (Eg. using SendKeys
messes with NumLock, a bad reference can cause all kinds of
compile errors, ...)

--
Marsh
MVP [MS Access]

Als Hotmail wrote:
>I am having a lot of strange things happening in this computer. I am using
>Window 7 & Office Access 2007.
>After some automatic backups some programs disappear, I think when I first
>loaded the programs everything worked but after the updates the problems
>began. Number Lock keeps unlocking, Trim function gives complier error.
>I am about to restore to a earlier time.
>Do you have any suggestions?
>
>

>"Marshall Barton" wrote

>> If the DLookup works in your code, then we are wasting a lot
>> of time debugging the typos you introduced ny retyping
>> whatever you really have.
>>
>> I have never seen or heard of a problem with vbCrLf in a VBA
>> procedure. I can only guess that your post has other
>> inconsistencies that manifest the problem or you have
>> somehow set a variable named vbCrLf to something other than
>> the usual value.
>>
>>

[Marshall Barton]

Are you saying that [GLN] and [GL ID] are both fields in
table [Warren Lodge Membership]? If so, odd as that may be,
the Dlookup should be ok. At that point, I can not explain
the vbCrLf problem beyond what I posted earlier.

--
Marsh
MVP [MS Access]

Als Hotmail wrote:
>GLN should be [GLN] it is the same value as [GL ID]
>Thanks for your response Al
>

>"Douglas J. Steele" wrote.

>> I'm shocked that your DLookup works, as it's syntactically incorrect.
>>
>> If you're looking for the row where [GL ID] equals the (numeric) value
>> stored in a variable named GLN, you need
>>
>> DLookup("[ea degree] ", "[Warren Lodge Membership]", "[GL ID] =" & GLN)
>>
>> If you're looking for the row where [GL ID] equals the (string) constant
>> GLN, you need
>>
>> DLookup("[ea degree] ", "[Warren Lodge Membership]", "[GL ID] = 'GLN'")
>>
>>

>> "Als Hotmail" wrote

>>> The DLOOKUP function works. All of the information is printed without a
>>> line feed. the information is on one line.
>>>

>>> "Marshall Barton" wrote

[Dirk Goldgar]

"Als Hotmail" <alta...@hotmail.com> wrote in message

news:74663B82-A4F1-493B...@microsoft.com...

This is in a report? And firstline is either the name of a text box on the
report, or is a variable whose value will be assigned to such a text box?
If so, check to make sure the text box's Can Grow property is set to Yes,
and that the Can Grow property of the report section containing the text box
is also set to Yes.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

[Al Hotmail]

[GL ID] is on the form that is based on qryFormACodeData.
[GLN] is in a Membership Table.
The function returns the proper data to the text box box that calls the
function, however the vbCrLf does not cause the following data to start a
new line, the following data continues on the same line until it fills the
box weith and the box grows and continue the output.

Case 16
firstline = "EA DEG. on = " & DLookup("[ea degree]", "[Warren
Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
"FC DEG. on = " & DLookup("[FC degree]", "[Warren
Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
"MM DEG. on = " & DLookup("[MM degree]", "[Warren
Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
"Exemplified on = " & DLookup("[Exemplified]",
"[Warren Lodge Membership]", "[GL ID] =GLN")

All help is welcome
Thanks Al

"Marshall Barton" <marsh...@wowway.com> wrote in message

news:2qgav5hq96thvpbag...@4ax.com...

[Marshall Barton]

Al Hotmail wrote:
>[GL ID] is on the form that is based on qryFormACodeData.
>[GLN] is in a Membership Table.
> The function returns the proper data to the text box box that calls the
>function, however the vbCrLf does not cause the following data to start a
>new line, the following data continues on the same line until it fills the
>box weith and the box grows and continue the output.
> Case 16
> firstline = "EA DEG. on = " & DLookup("[ea degree]", "[Warren
>Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
> "FC DEG. on = " & DLookup("[FC degree]", "[Warren
>Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
> "MM DEG. on = " & DLookup("[MM degree]", "[Warren
>Lodge Membership]", "[GL ID] =GLN") & vbCrLf & _
> "Exemplified on = " & DLookup("[Exemplified]",
>"[Warren Lodge Membership]", "[GL ID] =GLN")
>

I guess we're back where we started.

The only reasons I can think of that would cause vbCrLf to
fail to do its thing:

1) somewhere in your VBA project you declared vbCrLf as a
variable or constant.

2) the text box has a property (RichText, Html, ??) set
that ignores vbCrLf

3) the form is corrupted.

[John Spencer]

Just out of curiosity and as a sanity check have you tried replacing vbCrlf
with Chr(13) & Chr(10) and see if that works to give you the desired result?

OR use
VBA.vbCrLf
in place of the simple vbCrLf and see if that works.

If those work, then I agree with Marshall's evaluation as to the causes of the
problem.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Al Hotmail]

Thanks John
I tried your suggestions on this code
Case Is = 14
If DLookup("[SendMail]", "[Warren Lodge Membership]", "[GL ID]
=GLN") = -1 _
Then firstline = DLookup("[Address]", "[Warren Lodge
Membership]", "[GL ID] =GLN") & Chr(13) & Chr(10) & _
DLookup("[City]", "[Warren Lodge Membership]",
"[GL ID] =GLN") & ", " & _
DLookup("[State]", "[Warren Lodge Membership]",
"[GL ID] =GLN") & " " & _
DLookup("[PostalCode]", "[Warren Lodge

Membership]", "[GL ID] =GLN")

This is still the results from the text box. the zip is on the next row
because of the weigh of the text box.
3302 E Baltimore Street Apt 2 Baltimore, MD
21224

6918 Yale Rd Middle River, MD 21220
The space between records is 2 records.
The OS is Win7 & Access 2007. Both were reloaded because various problems
that has been corrected.
Thanks Al

"John Spencer" <spe...@chpdm.edu> wrote in message
news:#4TGe5z#KHA....@TK2MSFTNGP04.phx.gbl...

[John Spencer]

I'm a bit confused on something.

[GL ID] is on the form that is based on qryFormACodeData.
[GLN] is in a Membership Table.

IS [GL ID} a field in the table [Warren Lodge Membership]?
Is [GLN] a field in that same table?

For the DLookup to work as it should both those fields would have to be in the
table Warren Lodge Membership. If they aren't both in that table then your
code should not be executing for that case since in all probability the
DLookup is going to return NULL if it doesn't error.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

[Al Hotmail]

[GL ID] is in a query which is the dataset that the form is based on.
[GLN] is in the membership table
The data I get is correct for the condition.
the problem is I cannot cause a carriage return and line feed to put some of
the data returned on a new line.
Thanks Al

"John Spencer" <spe...@chpdm.edu> wrote in message

news:eyjel12#KHA....@TK2MSFTNGP05.phx.gbl...

[Jeanette Cunningham]

You can use a trick that I used a couple of weeks ago when I was unable to
put some info on a new line using a query.
I used Chr(13) & Chr(10) & Chr(13) & Chr(10).
This forced a new line when just Chr(13) & Chr(10) wouldn't.

I don't understand why, but it worked.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Al Hotmail" <alta...@hotmail.com> wrote in message
news:AD31DDD8-4301-4A4E...@microsoft.com...

[John Spencer]

Obviously there is something we are not seeing. From what you say and my
understanding of how things work your code should NOT work. I would expect to
see the following if GLN is a number field

DLookup("[SendMail]", "[Warren Lodge Membership]", "GLN =" & [GL ID]) = -1

Or this if GLN was a Text field
DLookup("[SendMail]","[Warren Lodge Membership]","GLN ='" & [GL ID] & "'")= -1

Also, you could simplify life and speed up the process with only one call to
DLookup to return the value for FirstLine with something like the following.
DLookup("[Address] & Chr(13) & Chr(10) & [City] & "", "" & [State] & "" "" &
[Postal Code]","[Warren Lodge Membership]","GLN ='" & [GL ID] & "'")

I really think that something else is going on and that your case statement
and DLookup are not working as you think. I've not seen the behavior you are
seeing.

[Bob Quintal]

John Spencer <spe...@chpdm.edu> wrote in

news:#K$E0TA$KHA....@TK2MSFTNGP02.phx.gbl:

> Obviously there is something we are not seeing. From what you say
> and my understanding of how things work your code should NOT work.
> I would expect to see the following if GLN is a number field
>
> DLookup("[SendMail]", "[Warren Lodge Membership]", "GLN =" & [GL
> ID]) = -1
>
> Or this if GLN was a Text field
> DLookup("[SendMail]","[Warren Lodge Membership]","GLN ='" & [GL
> ID] & "'")= -1
>

John, the = -1 is redundant, and your closing parenthesis is
misplaced.

Bob

[John Spencer]

I don't believe so. On the other hand I can see the confusion.

The expression was extracted from the bit of code that was an IF statement

IF DLookup("[SendMail]","[Warren Lodge Membership]","GLN=" & [GL ID])=-1 THEN

I was not clear on where I was extracting the expression from. I relied on
the poster to realize that. I also assumed that SendMail was a boolean field.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County