6 Replies Latest reply: May 15, 2014 10:03 AM by drobert _ RSS

    "Null Replacement" option for External Lookup

    drobert _

      In my external lookup I wanted make the value of my source field to be blank if nothing was found and I was able to find the "Null Replacement" option, which I thought would do exactly what I wanted, but it had no effect; it still returned "Null" when no match was found.

       

      My workraound was to create a calculated field to do just that and hide the lookup field.

       

      Am I misunderstanding this option?

        • "Null Replacement" option for External Lookup
          Grant Perkins

          Daniel,

           

          What did tyou set as the alternative value for the 'text' string?

           

          What is the source type of the data file from which you are taking your lookup data?

           

           

          Grant

            • "Null Replacement" option for External Lookup
              drobert _

              What did tyou set as the alternative value for the 'text' string?[/quote]

              I am not sure what you mean. The Null Replacements[/B] section in the External Lookup[/B] dialogue box only has one checkbox option (Replace nulls with default values[/B])

               

              What is the source type of the data file from which you are taking your lookup data?[/quote]

              It's defined as a string/text.

                • "Null Replacement" option for External Lookup
                  drobert _

                  Sorry I gave you wrong information:o. What I did is looked at the field type in my Access database that has links to the SQL database, which I temporarily created to find this information. For some strange reason Access seem to think that it is formated as a Text field:confused:.

                   

                  For the external lookup, I get it directly from MSSQL.

                   

                  I asked our DB Admin and he told me that this field is defined as bigint[/B].

                    • "Null Replacement" option for External Lookup
                      drobert _

                      Does anyone know why my "NULL" values do not change to 0 when I select the Replace nulls with default values[/B] option in the External Lookup[/B]? Am I misinterpreting /FONTits function?

                        • "Null Replacement" option for External Lookup
                          Grant Perkins

                          Does anyone know why my "NULL" values do not change to 0 when I select the Replace nulls with default values[/B] option in the External Lookup[/B]? Am I misinterpreting /FONTits function?[/quote]

                           

                          Hi Daniel,

                           

                          I'm wondering if this NOTE: info from the Help file is in play here. Sounds like it might be.

                           

                          "[B]Replace nulls with default values[/B]

                          Select to replace any null strings, numbers or dates with default values of empty strings, zeros and default dates (1/1/1970), respectively. Note:[/B] This checkbox is applicable to all databases except mdb’s and databases with OleDb or ODBC connection strings."

                           

                          That would leave you with your existing calculated field solution.

                           

                           

                          HTH.

                           

                           

                          Grant

                            • "Null Replacement" option for External Lookup
                              drobert _

                              It's interesting that this note is not at all indicated when you click the help button from the External Lookup[/B] wizard... I think is would be wise to have the same information displayed at that point, then searching through help, wouldn't you think?:confused:

                               

                              This is all you see when you click the help button from the wizard:

                              Replace nulls with default values[/B]

                               

                              Select to replace any null strings, numbers or dates with default values of empty strings, zeros and default dates (1/1/1970), respectively.

                              /quote

                               

                              I guess a side question would be, is there another way to connect to an MSSQL database other then OleDb, ODBC or through an Access MDB using links?:confused: