SQL with IIF

[Barry]

I have a form with a list box containing Customer names. Since all customers
may not have an associated company name, I made a column in the query of the
listbox rowsource using an IIF statement if the company name ISNULL.
CustName:IIF(IsNull(CompanyName),[LastName] & ", " [FirstName],CompanyName)
which works quite nicely. I want to add an unbound textbox (strSearch) to
the form to search for names based on the characters in the textbox and
change the RowSource accordingly. This is the code in the OnChange of the
textbox.

Dim strSQL As String, Where As String

Where = "[CustName] like '" & Me!strSearch.Text & "*'"

strSQL = "SELECT tblCustomers.CustomerID, IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) as CustName,
tblCustomers.Phone" & vbCr & _
" FROM tblCustomers" & vbCr & _
" Where " & Where

lstCustomers.RowSource = strSQL

Can anyone tell me where I have gone wrong?

Thanks,
Barry

 

[John W. Vinson]

I have a form with a list box containing Customer names. Since all customers
may not have an associated company name, I made a column in the query of the
listbox rowsource using an IIF statement if the company name ISNULL.
CustName:IIF(IsNull(CompanyName),[LastName] & ", " [FirstName],CompanyName)
which works quite nicely. I want to add an unbound textbox (strSearch) to
the form to search for names based on the characters in the textbox and
change the RowSource accordingly. This is the code in the OnChange of the
textbox.

Dim strSQL As String, Where As String

Where = "[CustName] like '" & Me!strSearch.Text & "*'"

strSQL = "SELECT tblCustomers.CustomerID, IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) as CustName,
tblCustomers.Phone" & vbCr & _
" FROM tblCustomers" & vbCr & _
" Where " & Where

lstCustomers.RowSource = strSQL

Can anyone tell me where I have gone wrong?

Thanks,
Barry

Using the Change event for one thing; its name is somewhat misleading! It
fires at *EVERY KEYSTROKE* (i.e. whenever the textbox content is changed by
the user).

If you use the AfterUpdate event instead you'll have better luck.

 

[Barry]

I have a form with a list box containing Customer names. Since all customers
may not have an associated company name, I made a column in the query of the
listbox rowsource using an IIF statement if the company name ISNULL.
CustName:IIF(IsNull(CompanyName),[LastName] & ", " [FirstName],CompanyName)
which works quite nicely. I want to add an unbound textbox (strSearch) to
the form to search for names based on the characters in the textbox and
change the RowSource accordingly. This is the code in the OnChange of the
textbox.

Dim strSQL As String, Where As String

Where = "[CustName] like '" & Me!strSearch.Text & "*'"

strSQL = "SELECT tblCustomers.CustomerID, IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) as CustName,
tblCustomers.Phone" & vbCr & _
" FROM tblCustomers" & vbCr & _
" Where " & Where

lstCustomers.RowSource = strSQL

Can anyone tell me where I have gone wrong?

Thanks,
Barry

Using the Change event for one thing; its name is somewhat misleading! It
fires at *EVERY KEYSTROKE* (i.e. whenever the textbox content is changed by
the user).

If you use the AfterUpdate event instead you'll have better luck.

John -
Thanks for the reply. However it is the syntax in the SQL statement that is
the problem. I keep getting asked for CUSTNAME. Am I using the IIF
statement correctly in the SQL statement?
Thanks again,
Barry

 

[John Spencer]

You cannot refer to CustName in the WHERE clause. It DOES not exist when the
WHERE portion of the query is executed.


Dim strSQL As String, Where As String

'REVISED WHERE CLAUSE
Where = "IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) like '" & Me!strSearch.Text
& "*'"

'REVISED SIMPLER WHERE CLAUSE
Where = "Nz([CompanyName]),[LastName] & "", "" & [FirstName]) like '" &
Me!strSearch.Text & "*'"

strSQL = "SELECT tblCustomers.CustomerID, IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) as CustName,
tblCustomers.Phone" & vbCr & _
" FROM tblCustomers" & vbCr & _
" Where " & Where

lstCustomers.RowSource = strSQL

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

 

[NG]

Hi,

as Custname is a calculated field, it doesn't yet exist on the moment the
where clause is evaluated. If you decide to do the whole thing in just one
query, you'll have to repeat the complete iif statement in the where clause.
Tip here: you can use the NZ function in stead of the iif when replacing
null values

greetings
NG

 

[Barry]

John & NG:
Thanks so much for your help. I understand what you are saying about the
CustName not existing when the query is executed. Makes tremendous sense
when you think about it. Can you say Duh? Additionally, it makes sense to
use Nz however, it does not return any results when applied to my code. The
IIF function does indeed return the appropriate records when used in the
Where clause. Again, I appreciate your help and quick responses.
Sincerely,
Barry

You cannot refer to CustName in the WHERE clause. It DOES not exist when the
WHERE portion of the query is executed.


Dim strSQL As String, Where As String

'REVISED WHERE CLAUSE
Where = "IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) like '" & Me!strSearch.Text
& "*'"

'REVISED SIMPLER WHERE CLAUSE
Where = "Nz([CompanyName]),[LastName] & "", "" & [FirstName]) like '" &
Me!strSearch.Text & "*'"

strSQL = "SELECT tblCustomers.CustomerID, IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) as CustName,
tblCustomers.Phone" & vbCr & _
" FROM tblCustomers" & vbCr & _
" Where " & Where

lstCustomers.RowSource = strSQL

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

I have a form with a list box containing Customer names. Since all customers
may not have an associated company name, I made a column in the query of the
listbox rowsource using an IIF statement if the company name ISNULL.
CustName:IIF(IsNull(CompanyName),[LastName] & ", " [FirstName],CompanyName)
which works quite nicely. I want to add an unbound textbox (strSearch) to
the form to search for names based on the characters in the textbox and
change the RowSource accordingly. This is the code in the OnChange of the
textbox.

Dim strSQL As String, Where As String

Where = "[CustName] like '" & Me!strSearch.Text & "*'"

strSQL = "SELECT tblCustomers.CustomerID, IIf(IsNull([CompanyName]),
[LastName] & "", "" & [FirstName], [CompanyName]) as CustName,
tblCustomers.Phone" & vbCr & _
" FROM tblCustomers" & vbCr & _
" Where " & Where

lstCustomers.RowSource = strSQL

Can anyone tell me where I have gone wrong?

Thanks,
Barry

.