MS Access, Text Fields and Null.

[Zvoni]

I don't understand what you are referring to:

If IsNull(rsDataSet.Fields(0)) Then
   txtWhatever.Text = ""
Else
   txtWhatever.Text = rsDataSet.Fields(0)
End If

This won't suddenly crash. It's clean and it's obvious how the special case IsNull is handled.
If you have multiple columns in table which are allowed to be NULL then you have to deal with this in your code.
I really don't see the problem.

Or use the IIF in the SELECT-Statement, then it doesn't matter.
Consequence: He has to fully formulate his SELECT-Statements.
No "lazy" way with "SELECT * FROM"

If OP doesn't want to change away from the "SELECT * FROM" then i'm done with him. Then he's on his own.....

[cafeenman]

Or use the IIF in the SELECT-Statement, then it doesn't matter.
Consequence: He has to fully formulate his SELECT-Statements.
No "lazy" way with "SELECT * FROM"

If OP doesn't want to change away from the "SELECT * FROM" then i'm done with him. Then he's on his own.....

First, I only use * when I need the majority of the fields. It's not a thing I just do.

Second, you haven't answered my question of how you're going to handle a query with a boatload of text fields using your iif method. I mean it's fine if it's just one field you're checking.

But if you're pulling in a couple dozen text fields, that's a couple dozen iif statements in your query. Even if there isn't a character-limit on the query, it's still going to be a nightmare to write, debug and maintain.

Can you post any query you've written that pulls in a bunch of text fields please?

[Zvoni]

Can you post any query you've written that pulls in a bunch of text fields please?

My last "Monster" is a fully formulated SELECT-Query, involving 15 CTE's, 30 Base-Tables, some 50 JOINS (all flavours - INNER, LEFT) and some 50 Output-Columns, the Majority of them Text-Columns

In my Query-Designer (for IBM DB2) it's some 200 lines long......

That's for my day-job.

For my own projects (mainly Lazarus/FreePascal with SQLite), i only ever have a single SELECT-Statement in my Frontend:
"SELECT SQLStatement FROM tbl_sql_statements WHERE ID=@paramID"

I store my fully formulated SQL-Statements (all of them. SELECT, UPDATE, INSERT, DELETE) in the Database itself.

In my Frontend i just pull the Statement i want to use from the DB and assign it to the Command-Property (or whatever it's called in vb), set the params i have to set, and fire it off
All my SQL-Statements are tested. I don't debug SQL-Statements in my Frontend-Project.
I write them, i test them, i debug them, BUT ONCE IT WORKS, i rarely touch them again

I can even change my Statement in the DB itself without having to recompile/redistribute the Frontend.
Hell, i can even change Column-Names (for displaying in a Grid) that way

EDIT:
Excerpt from one of my Queries. 144 Lines long