null value (visable on form)

[eggwater]

hi comrades

I want to have a value stored in a form yet expoprt as a null - I have a combo list - Deluxe, Standard, None - on the export the bound column exports a code letter (i.e. W, O, Null) - however on the form I want it to show up as 'none' - at the moment it looks like the field is completely empty -

the row source propert is set as follows - W;"Deluxe";O;"Standard";;"None" - none does appear in the combo list but does not appear on the form if it is selected from the list.

I can't export a space as this messes up the SAP import program.

is this possible?

 

[dcx693]

In the query that feeds the form, just add a calculated field that changes the Null values to "None" by using Nz([yourfield],"None") . You can continue doing your export using the actual field and not the calculated one.

 

[eggwater]

null = "none"

but my form's fed by a table not a query?!

 

[dcx693]

Hmm....then I think your only solution is to create a new query that contains all the required fields from your table. You can then create the calculated field in the query and feed that query into your form.

 

[eggwater]

I think I'll just ...

explain to my users that if they choose 'none' it's a null and won't show up - it makes no difference to the export - I just don't want people to feel like they have to make a choice that actually appears on the form...

the problem I have by changing the form source now is that various part of the form a fed by Dynamic SQL and VBA so it'll be a nightmare to start altering - the database is already live and we want to export to SAP as soon as possible (e.g. tonight or tomorrow morning)...

ho hum

 

[Mile-O]

Re: null = "none"

but my form's fed by a table not a query?!

eggwater, it's a better practice to use queries to populate your forms and reports because, for summarisation purposes:

• the more records a table has the slower you'll database will begin to run if you bind the table directly to the form;
• more than one user might lock records/tables, etc if you bind tabls to forms.

 

[eggwater]

i realise this but...

when yr data is from a flat table that was made by someone else and the people using the old database are still using that data whilst you are working on a complete overhaul - it's difficult