Prev: Is it possible to have If statements in a query?
Next: Tricky one...”Select”
From: alhotch on 11 May 2010 11:36
All is well now, John. Once again, you have been MOST helpful in solving
problems. I used the last ("or, even better") solution as it will indeed
match records where the FirstName is blank. I was trying to concatenate the
"controlname" portion of the Forms! statement BUT forgot that I could string
two Forms! statements together. Learning something new EVERYTIME I research
and use these forums !!!

Much Thanks, Again !

"John W. Vinson" wrote:

> On Mon, 10 May 2010 08:56:01 -0700, alhotch
> <alhotch(a)discussions.microsoft.com> wrote:
>
> >Let me restate my question in more simpler terms. I want to know if I can
> >concaenate a [FirstName] & " " & [LastName] value and use it in the
> >"controlname" parameter of a "Forms!" statement. In other words, will the
> >following SQL statement work ?
> >
> >SELECT ALL [tblNames].[NamesID],[tblNames].[Address] FROM tblNames WHERE
> >[tblNames].[FirstName] & " " &
> >[tblNames].[LastName]=Forms![frmNames].[FirstName] & " " & [LastName] ORDER
> >BY [tblNames].[Address] DESC
> >
> >If not, why ? When I run this statement, I get the FirstName displayed (I
> >use this SELECT statement as the RowSource control in a ComboBox) but NOT the
> >concatenation of both FirstName and LastName. I've tried to put "( )" around
> >the two values - [FirstName] & " " & [LastName] but get syntax errors.
>
> You can't assume that Access will understand the isolated reference to
> [LastName]; and SELECT ALL is not valid SQL. Try
>
> SELECT [tblNames].[NamesID],[tblNames].[Address] FROM tblNames WHERE
> [tblNames].[FirstName] & " " & [tblNames].[LastName] =
> Forms![frmNames]![FirstName] & " " & Forms![frmNames]![LastName]
> ORDER BY [tblNames].[Address] DESC
>
> Or search the two name fields independently:
>
> SELECT [tblNames].[NamesID],[tblNames].[Address]
> FROM tblNames
> WHERE [tblNames].[FirstName] = Forms![frmNames]![FirstName]
> AND [tblNames].[LastName] = Forms![frmNames]![LastName]
> ORDER BY [tblNames].[Address] DESC
>
> or, even better,
>
> SELECT [tblNames].[NamesID],[tblNames].[Address]
> FROM tblNames
> WHERE ([tblNames].[FirstName] = Forms![frmNames]![FirstName]
> OR Forms![frmNames]![FirstName] IS NULL)
> AND ([tblNames].[LastName] = Forms![frmNames]![LastName]
> OR Forms![frmNames]![LastName] IS NULL)
> ORDER BY [tblNames].[Address] DESC
>
> to match all the records for "Zybrowski" if the FirstName control is left
> blank.
> --
>
> John W. Vinson [MVP]
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Is it possible to have If statements in a query?
Next: Tricky one...”Select”