null value

[roby258376]

Hi all,

I am trying to figure out how to do something and I'm hoping someone here can tell me how.

I need to show records based on:

1) authorization level = 7
2) the person belonging to committee 1 or committee 2.

If I go into the database and manually set each database record to null for committee_ID and committee_2_ID (unless of course they are on a committee) the following works.

SELECT *
FROM members
WHERE members.auth_ID=7 AND members.committee_ID is not null AND members.committee_2_ID is not null

but I cannot get the db fields for committee_ID and committee_2_ID to default to null if no committee is entered when a member is inserted or updated. So new members and any members who are edited to take them off a committee, is still showing up in my search.

any help is appreciated.

[xag]

Why not just get all memembers and use Secruity Assist Show region to handle it? Make a rule and bam your done. That is what I do throughout my whole site... just made a ton of rules and use session variables to see if a user signed in and what their credentials are then show a region if not don't show it.

[roby258376]

The list is viewable by all, so I don't think that would work. What I need to know is how to set a field in the database to the actual value (null), if no other value is entered, or if an empty value is entered.

I just can't find anywhere that tells me how to do that.

[xag]

Make the default value/binding in the DB (N'Null') so if there is nothing inserted it will be Null

[roby258376]

That's what I'd like to do, but I don't know how. What steps do I need to take to make the default value null instead of empty?

[xag]

well what version os SQL are you using? go into your table design and in the coulmn properties panel under General there is a place where you can insert Default Value or Binding

[roby258376]

Hi again,

I set the default value as (N,'Null') and then tried (Null), ('Null') and anything else I could think of. They all result in the db field being empty which is not what I need, I need it to actually have the (null) value inserted, so that not null works. Any suggestions?

[xag]

well if you put it in your defult binding just as null it should show up but not if you are inserted something. The last idea i have to work around it is to have a default hidden field set to Null and just insert that into your DB when no other input is inserted with DA

[roby258376]

Thanks for the info, I really appreciate the help.

I ended up setting the default value in the form field to a blank space, and rewriting my query to != ' '. that seems to have done the trick.