Problem with IIf in combination with a db Query 1

[Overdoos]

Hi,

I'm back with yet another problem. I think the solution is quite simple, but I fail to see it, so maybe you can help me out.

For a certain program, I use a translation-table (Access, with ODBC/ADO connection... works fine).

The layout of the table:

<key> | <langF> | <langN> | <langE> | <langD>

Now when I load my program, I check a config-file and load all the labels in the language selected by the user. In order to do this, I have written a function called 'Translate' which requires two strings as parameters. First one is the 'key', the next is the language-code. This also worked fine but then I decided to add an extra safety-check: if there is no translation defined for the 'key', the function would return the 'key' instead of just halt everything.

In order to do this, I changed this line:

Translate = adoRecordset(strLanguageField) & ""

into this one:

Translate = IIf(adoRecordset(strLanguageField) & "" = "", strKey, adoRecordset(strLanguageField))

Since I though that IIf would only look at the part it needed, this would work like a charm.

Big surprise, it didn't.

Now... is there a way to get this done without having to resort to error-trapping and such?

(so basically what I want is ... when a null-value is returned from the DB, the function would return a custom value...)

 

[mmilan]

You could use the isnull() function.

Something else you should be aware of is that when you use IIF, both of the potential results are "evaluated". This means that if one of them contained an error (null, division by zero etc.) the function always fails, regardless of whether the problem was in the result that is actually going to be used.

Using if ... then ... else ... allows you to get around this issue.

mmilan

 

[Overdoos]

thanks.

I threw out the IIF and replaced it with a fullblow if-clause. (I assumed that would have been the problem, but refused to face facts )

Just for information... the isnull is of no use in this case but BOF and EOF are very needed.

The structure that helped me out? Posted below...

If adoRecordset.BOF Or adoRecordset.EOF Then
  Translate = strKey
Else
  Translate = adoRecordset(strLanguageField) & ""
End If

This does the trick for me!

 

[mmilan]

You might also consider putting an index on the key field and then using a seek.

Even if you don't bother with the seek, merely indexing the field and using your where clause should still give an improvement in performance.

 

[mmilan]

Thanks thanks for the star btw...