Criteria Problem

[CallMeAndy]

Hi folks any help appreciated.
I have a combo box displaying a concatenated trio of fields
representing another individual in the same contact table.

Title & " " & Firstname & " " & LastName

I need to use this value with a DLookup or other solution to get the email address from this other record in order to send a report to ths other person

the criteria for the DLookup I was intending to use is
"[Title] + ' ' + [FirstName] + ' ' + [LastName] = '" & Solicitors_ContactName & "'"

Works great accept in the instance when [Title] is a Null value on the other record, and presumably if either of the name fields were.

So is there a variation I can use as the criteria or is it possible to access columns in the combo box which are not the displayed ones, i.e could I include a hidden column for the email address that is wanted.

 

[EMP]

Concatenate in the same way as the combo box i.e. use &

"[Title] & ' ' & [FirstName] & ' ' & [LastName] = '" & Solicitors_ContactName & "'"

^

 

[CallMeAndy]

Concatenate in the same way as the combo box i.e. use &

"[Title] & ' ' & [FirstName] & ' ' & [LastName] = '" & Solicitors_ContactName & "'"

^

Thought I replied to this earlier! Sorry if there is a repeat.

Thanx for your input EMP - did the job fine! but I am lost on the difference between the two concatenation symbols: could you explain?

 

[Banaticus]

+ literally combines the two
5 + 6 = 11
& concatenates the two -- it adds one to the end of the other
5 & 6 = 56

 

[CallMeAndy]

+ literally combines the two
5 + 6 = 11
& concatenates the two -- it adds one to the end of the other
5 & 6 = 56

Yes I see how it might perform addition on numbers but in the example I had here for instance we had three pieces of text:

["Mr"] + " " + ["Andy"] + " " + ["Seabrook"]

In the above example field names are replaced of course with the data they are holding.

Using + on this data works exactly as I expected
yielding "Mr Andy Seabrook"

and this works perfectly in a SQL criteria element, however it failed when the [Title] field had a NULL value.

 

[Banaticus]

Sometimes whatever program you're using is smart enough to read your mind and know how you "really" want to use things. If you use something in a non-standard way, though, you're apt to get non-standard results.

When you do:
"Mr" + " " + "Andy" + " " + "Seabrook"
Access realizes that what you really want is to concatenate strings, as the things that you're adding are all surrounded by double quotes which tells Access that they're strings.

When you do:
[Title] + ' ' + [FirstName] + ' ' + [LastName]
A field can be anything and Access (so as to conserve time/energy) won't look inside the field to see what it is that you're adding unless you specifically tell it to look inside. Sometimes Access has been programmed to expect certain types of variables in fields in certain situation. Sometimes, though, Access has no idea what you're trying to do and it could either throw an error, or do what you expect, or do something unexpected. And, just because it does something one time doesn't mean that, based on other variables, it won't do something different the next time.

In Visual Basic:
+ means "literally add"
& means "concatenate"
Unless you use them in a situation where they mean something else.

 

[CallMeAndy]

There is still something missing in the understanding for me here!

Access attempts to add first when encountering the + operator but if not able to do so will go on to attempt concatenation.
In the ["Mr"] + ' ' + ["Andy"] + ' ' + ["Seabrook"] example then it would not have been able to add, so expectedly it concatenates and gives "Mr Andy Seabrook"

When the values are [] + ' ' + ["Tom"] + ' ' + ["Boot"] it cant add so falling back on concatenation surely should result in " Tom Boot"

When using the + operator if access decides whether to add or concatenate values based on its knowledge of the data types involved but doesnt check the data types of fields why in the example I used did it assume it should concatenate rather than add.

I suppose NULL + ' ' + ["Tom"] + ' ' + ["Boot"] could be the answer - I confess I was thinking that as the field is a character string the value would be considered "" as opposed to NULL

I do accept that the & operator gives the correct results in all instances and should be the one used. My questioning this now however is one of understanding the mechanism.

"Sometimes whatever program you're using is smart enough to read your mind and know how you "really" want to use things"
Sorry I can buy this one!

 

[RV]

Andy,

both the + operator and & operator are well explained in Access Help.
That is, in the Microsoft Visual Basic Help, so you better open a module, click on Help and do a search.
How each of these operators is based on the data types of the expressions you're trying to combine.
Access does look into the data types, so there is standard ruling (in contradiction to Banaticus' input).

I suppose NULL + ' ' + ["Tom"] + ' ' + ["Boot"] could be the answer

Not so.
If at least one of the expressions you want to combine can be null, use this expression

Title + " " & Surname + " " & Lastname

RV

 

[CallMeAndy]

Thanx RV for clearing up the red-herring!

There is always a temptation to assume that people dont read the help. But
of course the reality is that most are forced to reference it as they go and very rarely get time to read the entire contents in such a way as to retain the majority of what they read. The success of referencing the help as you go depends on a degree of luck in using the right keywords. Do a search for + or & on thier own for instance and you wont find the required help.

A search for "+ Operator" however has found what I was looking for and for posterity here are the rules:-

Both expressions are numeric data types (Byte, Boolean, Integer, Long, Single, Double, Date, Currency, or Decimal) Add.

Both expressions are String Concatenate.

One expression is a numeric data type and the other is any Variant except Null Add.

One expression is a String and the other is any Variant except Null Concatenate.

One expression is an Empty Variant Return the remaining expression unchanged as result.

One expression is a numeric data type and the other is a String A Type mismatch error occurs.

Either expression is Null result is Null.