Hi Jenny,
The query below still works.
SELECT iif([flag]=true,[firstname],[lastname]) AS description
FROM employees;
However, if you would like to append a string to iif function, you have to
generate the results (column name) beforehand, it seems in the sql
statement we can't do that.
The following does not work:
SELECT iif([flag]=true,[first],[last]) & name AS description
FROM employees;
We are managing to treat iif([flag]=true,[first],[last]) & name as one
field/column name in the table, however, Jet does not recognize this
format, in other words, this is not the correct format in the sql
statement; a simple query can be more clear:
SELECT first & name AS description
FROM employees;
Firstname is the column name in the table employees, we separate it and try
to combine it again. It does not work, we can't refer to column name in
that way.
Therefore, the query is not valid and you may have to change your query
design to refer to the completed column name in the query statement.
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <
www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Jenny" <
[email protected]>
|
| Alick:
|
| I test my query using your method, it is a good one. It
| can generate a list of correct records.
|
| But I found one \thing that makes your query work for
| locate-record-by-typing but mine can't, which is that in
| the IIF function, my query append a string to it, but
| yours does. Here is my query:
|
| "SELECT ProcID, IIf([CurrentFlag]=True," ","*") &
| [ProcDesc] AS Description FROM dbo_tbl_Process"
|
| In other words, if you try to change your query to :
|
| "Select iif([flag]=true,[firstname],[lastname]) &
| [lastname] as description from employees"
|
| test your form and see if work for locate-record-by-typing.
| Let me know your test result and solution if any.
|
| Thank you very much.
|
| >-----Original Message-----
| >Hi Jenny,
| >
| >If you set a break point in the code line below and check
| the sql value,
| >what is the string for sql? I suspect the query string is
| not valid since
| >testing shows iif can be used.
| >
| >Me.Cmb0.RowSource = sql
| >
| >In addition, you may create a query with the same string
| of sql to check if
| >it returns the correct collection.
| >
| >The following query works by using iif statement. (add a
| Yes/No field in
| >employees table)
| >
| >Select iif([flag]=true,[firstname],[lastname]) as
| description from employees
| >
| >The testing I did (it works)
| >
| >Private Sub Form_Load()
| >
| >Dim sqlstr As String
| >Dim striff As String
| >
| >striff = IIf(2 > 1, "firstname", "lastname")
| >
| >sqlstr = "SELECT " & striff & " FROM employees"
| >
| >Combo0.RowSource = sqlstr
| >
| >End Sub
| >
| >Please feel free to reply to the threads if you have any
| concerns or
| >questions.
| >
| >
| >
| >Sincerely,
| >
| >Alick Ye, MCSD
| >Microsoft Online Partner Support
| >
| >Get Secure! -
www.microsoft.com/security
| >This posting is provided "as is" with no warranties and
| confers no rights.
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Jenny" <
[email protected]>
| >| X-Tomcat-NG: microsoft.public.access.formscoding
| >|
| >| Alick:
| >|
| >| I did a simple test as you recommended. And I found
| what
| >| causes the problem is the "IIF" function in
| the "select"
| >| statement, see my codes as below:
| >|
| >| -----------------------
| >| Private Sub Form_Load()
| >| sql = "SELECT ProcID, "
| >| sql = sql & "IIf([CurrentFlag]=True," & Chr(34)
| & " "
| >| & Chr(34) & "," & Chr(34) & "*" & Chr(34) & ") &
| >| [ProcDesc] AS Description, "
| >| sql = sql & "ProcessType AS Type, "
| >| sql = sql & "CurrentFlag "
| >| sql = sql & "FROM " & TABLE_TBL_PROCESS & " "
| >| sql = sql & "WHERE ProcId <> 0 "
| >| sql = sql & "AND (ProcessType = 'P' OR ProcessType
| >| = 'S') "
| >| Me.Cmb0.RowSource = sql
| >| End Sub
| >| -----------------------
| >|
| >| Here is my question: if for the logic purpose I have to
| >| use "IIF" function in the "select" statement, how can I
| >| still make the location-by-typing still work for me?
| Your
| >| help will be greatly appreciated.
| >|
| >|
| >| >-----Original Message-----
| >| >Hi Jenny,
| >| >
| >| >I think you may need to debug the application if it is
| >| confirmed that the
| >| >problem is not related to any corruption. To confirm
| >| that, you may try to
| >| >create a new form with one combobox, and set its row
| >| source on form load
| >| >event, just like in my previous post.
| >| >
| >| >I can't run/test your code since it seems there are so
| >| many dependencies.
| >| >Perhaps you can post some runable code so that I can
| >| debug it for you.
| >| >
| >| >
| >| >
| >| >Sincerely,
| >| >
| >| >Alick Ye, MCSD
| >| >Microsoft Online Partner Support
| >| >
| >| >Get Secure! -
www.microsoft.com/security
| >| >This posting is provided "as is" with no warranties
| and
| >| confers no rights.
| >| >
| >| >--------------------
| >| >| Content-Class: urn:content-classes:message
| >| >| From: "Jenny" <
[email protected]>
| >| >| Sender: "Jenny" <
[email protected]>
| >| >|
| >| >| Alick:
| >| >|
| >| >| Then I really don't know why my combo box (called
| >| >| cboProcID) does not work. In its parent form, here
| is
| >| the
| >| >| code in the "On Load" event:
| >| >|
| >| >| ----------------
| >| >| Private Sub Form_Load()
| >| >|
| >| >| Dim sql As String
| >| >| Dim Msg As String
| >| >|
| >| >| On Error GoTo FormLoadError
| >| >|
| >| >| sql = "SELECT ProcID, "
| >| >| sql = sql & "IIf([CurrentFlag]=True," & Chr(34)
| >| & " "
| >| >| & Chr(34) & "," & Chr(34) & "*" & Chr(34) & ") &
| >| >| [ProcDesc] AS Description, "
| >| >| sql = sql & "ProcessType AS Type, "
| >| >| sql = sql & "CurrentFlag "
| >| >| sql = sql & "FROM " & TABLE_TBL_PROCESS & " "
| >| >| sql = sql & "WHERE ProcId <> 0 "
| >| >| sql = sql & "AND (ProcessType = 'P' OR
| ProcessType
| >| >| = 'S') "
| >| >| sql = sql & "ORDER BY IIf([CurrentFlag]=True," &
| Chr
| >| >| (34) & " " & Chr(34) & "," & Chr(34) & "*" & Chr(34)
| >| & ")
| >| >| & [ProcDesc] "
| >| >| Me.cboProcID.RowSource = sql
| >| >|
| >| >| Call FormAdd(Me)
| >| >| Exit Sub
| >| >|
| >| >| FormLoadError:
| >| >| Msg = "Error: " & Err.Description & vbCrLf
| >| >| Msg = Msg & "Form: frmPlantInfo" & vbCrLf
| >| >| Msg = Msg & "Event: Load()"
| >| >| DoCmd.Hourglass (False)
| >| >| MsgBox Msg, vbExclamation, MSG_TITLE
| >| >| DoEvents
| >| >| Call FormRemove(Me)
| >| >| Exit Sub
| >| >| Resume Next
| >| >| End Sub
| >| >| ----------------
| >| >|
| >| >| I can't see anything wrong here. Besides, there is
| >| a "On
| >| >| Got Focus" events in this combo box like the
| following:
| >| >|
| >| >| ----------------
| >| >| Private Sub cboProcID_GotFocus()
| >| >| cboProcID.Requery
| >| >| End Sub
| >| >| ----------------
| >| >|
| >| >| Can you see anything suspicious?
| >| >|
| >| >|
| >| >| >-----Original Message-----
| >| >| >Hi Jenny,
| >| >| >
| >| >| >What are the reproduce steps? I did test and it
| >| behaviors
| >| >| the same way;
| >| >| >
| >| >| >1. Create a new form
| >| >| >2. Add one combo box control
| >| >| >3. ON the form's load event, run the code:
| >| >| >
| >| >| >Private Sub Form_Load()
| >| >| >
| >| >| >Combo5.RowSource = "SELECT firstname FROM employees"
| >| >| >
| >| >| >End Sub
| >| >| >
| >| >| >4. Save the form.
| >| >| >5. Run the form.
| >| >| >6. Type the character 's' in the combo box, the
| record
| >| >| item "steven" is
| >| >| >located, which means it is automatically completed.
| >| >| >
| >| >| >Set the rowsource property in the property widow,
| it
| >| >| behaviors the same.
| >| >| >
| >| >| >Please feel free to reply to the threads if you
| have
| >| any
| >| >| concerns or
| >| >| >questions.
| >| >| >
| >| >| >
| >| >| >
| >| >| >Sincerely,
| >| >| >
| >| >| >Alick Ye, MCSD
| >| >| >Microsoft Online Partner Support
| >| >| >
| >| >| >Get Secure! -
www.microsoft.com/security
| >| >| >This posting is provided "as is" with no warranties
| >| and
| >| >| confers no rights.
| >| >| >
| >| >| >--------------------
| >| >| >| Content-Class: urn:content-classes:message
| >| >| >| From: "Jenny" <
[email protected]>
| >| >| >|
| >| >| >| There is one combo box in my form whose
| associated
| >| row
| >| >| >| source is defined in the Onload() function of its
| >| >| parent
| >| >| >| form. Because of this, the records shown in this
| >| combo
| >| >| box
| >| >| >| can't be located by typing partial of its full
| name.
| >| >| >| However if I defined the row source on the combo
| box
| >| >| >| property window, the location by typing is
| >| applicable.
| >| >| Is
| >| >| >| there a way to work around this problem if I have
| to
| >| >| >| define the row source in the Onload() function
| than
| >| in
| >| >| the
| >| >| >| proper window?
| >| >| >|
| >| >| >
| >| >| >.
| >| >| >
| >| >|
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|
