Set field value to something if nothing is selected

[Richie27]

Hi all,



I'm sure this is easy but it's doing my head in for the last half hour so here goes.

I have an unbound form with several fields relating to an action that is performed on something. One of those fields is actionCompany which pulls it's value from a table called Company via a query.

I have an insert query running off of this unbound form which doesn't work if the actionCompany field is left blank. I won't always have a field for actionCompany so I want to set it so that the database records "null" or "Not applicable as the value stored in the field. I just want to make the insert query happy really.

I am tempted to put the string "null" in the company tables company_name field but feel that is not a healthy habit to get into.

any help appreciated!

Update - I set the default value to 0 (it is an id stored in the actionCompany field) however a msg box appears saying a related field from company is needed.

hmmmm........

[alansidman]

Not sure exactly what you are trying to do, but thought this might be it.

http://www.baldyweb.com/Autofill.htm

[Richie27]

Thanks. Not quite what I am after. I will try and clarify.

I have an insert query running off of an unbound form. I have several fields on that form called actionAuthoriser, actionDate, actionReason, actionCompany which I am inputting into a table via an insert query. Eg: Insert into action (actionAuthoriser, actionDate, actionReason, actionCompany) Values (1,2,3,4) but a lot of the time, there won't be a value coming through for actionCompany i.e: no 4. Therefore access tells me there is an error in my syntax. I have currently put null in as a field in the Company table which is a lookup table and has merely company_id and company_name, the company_id is what is stored int the actionCompany field in the action table. I have put null in for the company_name in this field so as to satisfy my Insert statement. I.E: The user has to select either a company or null in the unbound form.

However, I feel this is bad as null is not the name of a company.

[alansidman]

Look at the NZ function to help make this work. Here is a explanation of how it works. This may be what you need.

http://www.techonthenet.com/access/f...dvanced/nz.php

[Richie27]

I thought that was it but then I realised I am storing a number in the field and I want it to display as the name of the company which makes declaring the varialble as string or integer a little tricky since it is an integer I want to store and the string I want to display.

Cheers.
Interesting and may use again in other circumstances, alas not these..unless I'm missing something.

[alansidman]

Obviously, I am not getting what you are trying to do. Perhaps you should post your db with some sample data in it so that we can get an exact view of what you are attempting and provide you with a valid solution. To try and solve it without the db is like shooting at birds blindfolded. We may get lucky and hit one, but it is unlikely.

Alan