Helmut Kotsch
Helmut.E.Kotsch at t-online.de
Wed Jan 28 09:04:52 CST 2004
Drew,
thanks for your effort, appreciate it very much.
Very good explanation.
Think I have to reread it once and a while to keep it in mind.
Helmut
-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von
DWUTKA at marlow.com
Gesendet: Mittwoch, 28. Januar 2004 02:22
An: accessd at databaseadvisors.com
Betreff: RE: [AccessD] Concatenated stLinkCritria produces Type
mismatcherror.
Let's see if I can break it down for you a bit.
Double quotes OR single quotes are a string delimiter. Which means, that if
either starts a string, that same character has to end the string. Double
quotes is usually the one used (like in VBA, a single quote on it's own
comments out a line....).
So, when building a string, you have nothing to worry about, UNLESS you need
to incorporate the delimiter into the string itself.
So, if you want: Let's test this.
"Let's test this." is the correct string. There is a single quote, but the
delimiter isn't used within the string.
If you want: Susie said, "I want chocolate ice cream!". Then it get's
interesting. However, to represent a double quote, within a string
delimited by double quotes, you just put two of them together, so,
"Susie said, ""I want chocolate ice cream!""." is the correct string
representation.
Once you understand the doubling of the delimiter concept, the rest isn't to
hard to figure out. So far I've only shown 'full' strings. If you need to
put a string together from peices, the SAME rule applies, even though it may
look different. If you want to include the dilimiter, you double it.
So, if you have : Look for "something". where something is a variable, you
would use this:
"Look for """ & strSomething & """." Notice, that you have 3 parts to this
string. The first part starts and ends with the delimiter ("). The last bit
of the first part is a doubling of the double quote, then one more double
quote to end the string. (So the delimiter is at the beginning and end of
the 'string', and again, the doubling rule puts the delimiter within the
string itself....because it's at the end of the segment, there are three in
a row, but just remember, that's a double 'delimiter', followed by the
delimiter itself...thus three in a row.) The second part is a variable, and
is joined to the other parts with the ampersand. The last part, three
double quotes, period, double quote, is the delimiter, a doubled delimiter
(to represent it in the string on it's own), a period, then the closing
delimiter.
Now, let's say you want: Something"somethingelse" you would do this:
strSomething & """" & strSomethingElse & """"
Now you see 4 delimiters. Don't get side tracked. That is really an
opening delimiter, the 'doubled' delimiter, and a closing delimiter. So if
you saw """""", that produces a string of : "" (open delimiter, 2 doubled
delimiters, and a closing delimiter....thus 6 all together, producing 2
double quotes for the contents of the string.)
Does that all make sense? Just remember that to represent a delimiter
within the string itself, you double the delimiter.
Now, not to confuse the issue, but if you look at it even deeper, there is a
perfectly logical explanation on how/why this system was developed. Numbers
and other variable types are all represented by a specific number of bytes.
To you and me 10,300 is 10,300, but to the email program, it's a chr(49),
chr(48), chr(44), etc. When I put 10300 in code, the compiler changes the
string into 4 bytes (if I am using a long integer variable type). However,
a string can be any length (I believe the maximum number of characters for a
string in VB/VBA is 2 million, but not positive on that. Since the length
is variable, the code has to know where the string starts, and where the
string stops, so it knows how much memory it is going to use (since in
ASCII, each character takes up 1 byte, and in Unicode they take up 2
bytes.). Because of this, the code needs to know where the string starts
and where it stops, so it uses a 'delimiter'. Quite frankly, the reason
double quotes seem so difficult to put into strings is because they chose a
delimiter normally used within a string. If they had used something like
chr(150), which isn't 'typable', it would have been easier to make strings,
because when are you going to use chr(150) in normal text, however, it would
make looking at most strings hard to see for a programmer. Even if they had
used a non-typable character, they couldn't exclude the fact that someone
MAY want to use that within the string itself. Thus, no matter what
delimiter they were going to use, they had to provide a method of letting
that delimiter fall within the string it was delimiting. To do that, they
took the double approach. If the code finds the end delimiter, and finds it
again, as the next character, it knows that the string isn't REALLY over,
because it's been doubled, thus the code simply puts the delimiter within
memory as part of the string itself. If it finds the end delimiter, and
anything BUT the delimiter afterwards, then it knows it has found the end of
that string. Make sense?
Drew
-----Original Message-----
From: Helmut.E.Kotsch at t-online.de [mailto:Helmut.E.Kotsch at t-online.de]
Sent: Tuesday, January 27, 2004 6:23 PM
To: Access Developers discussion and problem solving
Subject: AW: [AccessD] Concatenated stLinkCritria produces Type
mismatcherror.
Charlotte you are great. Thank you very much. Here I'm sitting at midnight
in Germany defining my problem and there you come back within minutes with a
workable solution.
What is the alternative to using multiple double quotes? (I don't like them
either)
Where in the world would someone learn / read about the solution you
provided. Even though it works, I don't understand it.
To be honest, this whole "quotes syntax" (single and double) is a nightmare
to me.
Again, thanks a lot.
Regards and have a nice day.
Helmut Kotsch
(Helmut.E.Kotsch at t-online.de)
-----Ursprungliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Charlotte
Foust
Gesendet: Mittwoch, 28. Januar 2004 00:49
An: Access Developers discussion and problem solving
Betreff: RE: [AccessD] Concatenated stLinkCritria produces Type
mismatcherror.
I don't like to use multiple double quotes like that because it's so
hard to read. Your problem is in this line:
stLinkCriteria = "[KundenName]=" & "'" & Me![KundenName] & "'" And
"[VerbandNr]=" & Me![VerbandNr]
Which should be:
stLinkCriteria = "[KundenName]=" & "'" & Me![KundenName] & "'" & "
And [VerbandNr]=" & Me![VerbandNr]
Charlotte Foust
-----Original Message-----
From: Helmut Kotsch [mailto:Helmut.E.Kotsch at t-online.de]
Sent: Tuesday, January 27, 2004 3:11 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Concatenated stLinkCritria produces Type mismatch
error.
Hi,
I am trying to open a form where 2 conditions have to be met. When only
one conditon (as shown in code below as sample#1 and sample#2) is set
than it works ok.
Sample#1 ==> OK
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "VERDI-Kunden"
stLinkCriteria = "[KundenName]=" & "'" & Me![KundenName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Sample#2 ==> OK
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "VERDI-Kunden"
stLinkCriteria = "[VerbandNr]=" & Me![VerbandNr]
DoCmd.OpenForm stDocName, , , stLinkCriteria
However when concatenating both conditions as shown in the following
code I get a "Type mismatch" error.
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "VERDI-Kunden"
stLinkCriteria = "[KundenName]=" & "'" & Me![KundenName] & "'" And
"[VerbandNr]=" & Me![VerbandNr]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Hope someone can help before this one drives me grazy.
Helmut Kotsch
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com