Blank Column in List Box

[Frink]

Hallo,

I have a list box where the first column is the autonumber primary key of my table. The rest of the columns are fields in the same table. One of the fields is an integer and I want to format it to two digits so have Format$([intNumber],"00") in the RowSource for that column.

My problem is that sometimes the listbox displays this a Null, not the correct value. The actual data is not null.
Is this a known problem? I'm guessing it is because it is a calculated control.

I am running MS Access 2000, but with the 2.5/3.5 compatability library as I use some DAO.Recordset objects.

When I paste the rowsource into a query it always works.
When I requery the recordset it makes no difference. If they are Null then they stay null.

Any ideas?

- Frink

 

[alfalf]

Try to refer to column in SQL like:

ABBERV: Format([tablename].[intNumber];"00")

If designing this column in QBE grid, make sure that You have semicolon like ...[intNumber];"00"

where ABBERV is just an random name (a name of column is mandatory, if data in column is formated or calculated).

 

[Frink]

Hallo,

Thanks alfalf, I was writing the SQL into the RowSource on-the-fly. Is ; better to use than , for functions in SQL statements?
I changed the "s to 's in the row source when the code was running and that made it work, although I don't know whether this was just because it sorted itself out after the rowsource update. Doing a simple requery didn't sort it out though.

As the problem is intermittent, I shall have to wait 'til it happens again before trying your suggestion.

- Frink

 

[alfalf]

Yap. ; should be used in QBE grid for formating - same for criteria instead of ,

Another thing, if formating or critering STRINGS in QBE grid, always use singlequotes '

for example, if You have criteria like:

" & forms!someform!textinputname & "

You won't get any results for strings. But if You put it like this:

'" & forms!someform!textinputname & "'

the result for strings is OK.

Conclusion: if filtering Numbers, use DoubleQuotes, if filtering Strings, add SingleQuotes to doublequotes like above.

 

[alfalf]

Addition: This applies to SQL code in VBA coding (set recset = db.openrecordset("...")). And also, You avoid To few parameters... error triggering in code execution.