Related Topics

Null As Default Value For Yes/no Field

updating field whose value is the word NULL and Not a typical 'null' value
I was given a table where in a text field, the value is NULL. (Actually spelled out like that.)
I need to convert that field to a date/time one. Which is being hindered by these values of NULL.

The problem is that when I put the word NULL in the field criteria spot, Access believes that I am referring to a value of 'null' and will not do the update.
I have done "NULL", NULL, =NULL, ="NULL", is NULL. and no success.
Please remember that the actual value is not 'null'. It is a text field that has the word NULL in the field.


Select a Null value in Unbound Combobox
Using Access 2010.
I've got an unbound combobox with a default value, but I still would like users to enter a Null value. I've got the Null value in the list of drop down items, but when it is selected, the combox value does not go to null, but instead goes to the default value.
Any easy way to allow null values to be selected? I know it was possible with older versions of Access, but not any more.


Executing MySQL Statement
I have a variable that contains a perfectly acceptable MySQL statement (as in, I can copy it into mysql directly and it works) yet when I run it through CurrentDb.Execute VariableName I get "Too few parameters. Expected 1."

[CODE]

Which the makes

INSERT INTO ticketchanges VALUES (NULL,46,DEFAULT,'few af ae a',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2);

The only thing I can think of is that I'm not declaring the column names because as you can see by the code, it's rather dynamic. So.


Setting default value of textbox to Environ("username") not working
I am trying to set the default value of a textbox (for new records of course) to Environ("Username") by placing the following in the default value field in the textbox property sheet:

=Environ("Username")

When I go to a new record, my textbox is Null. I've tried with the equals sign and without the equals sign and both result in a Null value in the textbox.

I can type ?Environ("Username") in the immediate window and I get the expected result (my network username).

I can set the default value for the textbox in the On Current event and it works fine, just wondering why I can't use the default value field in the property sheet to do this. Other functions such as =Date() work fine in the default value field.

Any idea why this might not be working


About Default Value in yes/No Field
I am working on a project that collects data about the children. I am trying to put yes/no button on the form using table values. So far everything is going on good. I have a table call childgender where the default value needs to be empty but for some reason even though I delete default value access keeps showing the No value.

What I did is I created a field in table called Child1Gender with yes/no button which I changed value to Boy/Girl using ;"Boy";"Girl" as a format with combobox which values are -1 and 0 as access assigns as default value for Yes/No option. On the form I used bound multicheckbox which values are boy=-1 i.e. yes default value and Girl = 0 i.e.

No default value as access assigns. When I run table it fills all the ChildGender fields with no = 0 (Girl) as a default value. I wanted to make my default value as null so that people can select Child gender.


Concatenate multiple fields that might have a null value
Hope someone can help. I'm trying to concatenate many fields into one field and some have a null value. And some need to have a description added in front of the value.
Field1 = Bldg Name (maybe null)
Field2 = Street
Field3 = Floor (maybe null)
Field4 = Room (maybe null)
Field5 = City

My problem is that in front of [Floor] should be "FL:" only if that field is not null. However, I keep getting the "FL:" in front of everything. How do I not include that when that specific field is null?


Preventing a Null error
Due to database design, I am not allowing Null values in my tables. On my main form, the default value for the text boxes is " ". However, if the user removes the zero-length string, they will get a Null error.

I've tried many different things to capture the Null entry and convert it using the Nz() function, but I can't get rid of it!


Access: How to intercept "You must enter a value in the [Field-Name] field"
I have a table and a textbox referring to it that requires a default value of 0 (zero) if a value is not entered. The 0 is displayed on first opening my data entry form, but if I enter avalue, then delete it leaving a NULL value, when I try to update the field I get a standard Access error dialog advising "You must enter a value in the '[Textbox Field-Name]'field".

How can I trap this standard message and automatically insert a 0 in lieu of the null as the field is updated?

using either a validation rule or an event procedure.


Having trouble setting composit primary key
I am trying to set a primary key on two fields. the problem is that the one field is null for some records. they will eventualy fill them in. However, because it is null now it will nowt let me set it as a primary key.

I tried setting the field, in the properties, with a default value; but it would not take it.


Updating Null Fields with values
I've received a csv file from a client that I've imported into Access 2010 in order to manipulate it into a format useful for another program I use.

A number of records contain what appear to be null fields that I'd like to set to default values, as they'll cause me grief down the track if they are left as nulls.

I've tried setting up an update query to set the default values for the fields of those records that appear to have null values.

The fields in the file that I want to change are called RD, YLD, CV, I'm, Ash, VM, FC and TS.

If RD is null the rest are as well. So I've setup the Criteria for the Update Query using isNull("rd") which produces the following SQL:


filling a table
I have a table with one record that is sometimes filled and sometimes null. I'd like to fill the table "downwards" along the lines
HAVE WANT

a a
null a
null a
b b
null b
c c
d d
null d
Quasi-program:
FillStr="xx" (default)
loop while not EOF
if rst.string="null" then set rst.string=FillStr
else FillStr=rst.string
loop
Now all I need is the exact syntax to do this.


Null value in field
CompanyName
Data Type = Text
Default Value =
Required = Yes
Allow Zero Length = Yes
Indexed = Yes (Duplicates


Auto Populate Field Based on other fields
I am a total access newbie when it comes to designing a database. I have managed to create a simple web database for project management but have run into a snag.

I want to have a field (field 3) auto-populate based on what is entered into two other fields (field 1 & field 2).

Field 1 is a date field (when a presentation is due). Field 2 is a yes/no checkbox (if checked, it means no presentation is necessary). Field 3 would be a calculated text field (?) because text cannot be entered into Field 1.

If field 1 is null and field 2 is checked, then I want "NA" to auto populate into field 3. However, if field 1 is null and field 2 is not checked, I want "TBD" to auto populate into field 3. If field 1 is not null, then I want field 3 to be null


duplicate alias ??
I have what might be a simple problem if I knew access/sql better. I have 12 fields (field1 through field12)which may or may not be populated. I know how to do this with other languages with if/else structures but it looks like access/sql doesn't have an ELSE structure.

I have to create a new table, with a new field populated with the contents of the last populated field.

in other words. if the 12th field is not null, populate fieldnew with the contents of field12
else
if field12 is null, then if field11 is not null,populate fieldnew with the contents of field11 |
else
if field11 is null, then if field10 is not null,populate fieldnew with the contents of fieldl10
else
if field10 is null, then if field9 is not null,populate fieldnew with the contents of fieldl9
else
if field9 is null, then if field8 is not null,populate fieldnew with the contents of fieldl8


Null Values in queries
This has probably been discussed but I can't find the answer.

I want to query using a form. If a form field is empty I want the query to return all results from the table including null fields.

If I query 'Is Null' then it does return the null fields (indicating that the fields are, in fact bona fide null fields).

If I query Like IIf(IsNull([Forms]![Form1]![Date]),Is Null,"*12") - then, when the form field is null, it doesn't return the Is Null fields. (I've checked by substituting another expression that the Iif is returning 'True


Passing criteria "NULL" or "IS NOT NULL" to a query
I want to pass the criteria "NULL" or "IS NOT NULL" to the underlying query in a report from a field on a form where all criteria for the particular report is selected before running the report. Typicially the procedure I use is to place the name of the control from the form in the criteria field of the query. example. if I'm providing a date to the query, I put a date field on the form and put the full name of the field "forms!myform!fieldname" in the criteria field of the query underlying the report. This is pretty bullet proof, except when I want to pass a value of "null", or "not null" as the criteria for a given field in the query. How can I set up a combo box, or command button, or selection box to feed the values "NULL" or "NOT NULL" as criteria the underlying query


Set default values for fields or controls
This article explains how to set a default value for a table field or for a control on a form in a Microsoft Office Access 2007 database. The default values that you set will appear in the field or control whenever you create a new record in your database.
In this article
* Understand default values
* Set a default value for a table field
* Set a default value for a control
* Set a default row for a list box or combo box
* Examples of default values


If/Then Null based on Null Value of Another Field
I am having trouble creating an expression whereby the value of [ThisExpr] is conditioned upon whether (NZ[OtherExpr]) is Null. So, If (NZ[OtherExpr]) returns a null value then 0, or If[OtherExpr] is not Null then [ThisExpr].


Field Visible If other fields are null
I have a row of fields (call them 1-4) set up and have a field (with label) below (5) it that is not visible. I want field 5 to be visible if any of 1-4 are not null but I want it to go back to being non-visible if all of 1-4 are null.

So far I've tried many ways of addressing this with the latest being:

Private Sub IiF()
IiF OtherDeliveriestoUNK.Value = Null
IiF OtherAlternatePort.Value = Null
IiF OtherNameofAltPort.Value = Null
IiF OtherPreferUNK.Value = Null
ListOtherSpeciesUNK.Visible = False
Label445.Visible = False
Else
ListOtherSpeciesUNK.Visible = True
Label445.Visible = True
End If
End If
End If
End If


Background Text (Not Default)
In other applications (For example Quickbooks Pro) individual fields contain the name of the field in a light gray text. This makes it possible to not have a caption while permiting the user to know what that field represents.

I understand if you place a default value the field will accomplish this, but the problem is that the default value will be entered which is not what I want. Is there an easy way of doing it besides doing some VBA to deactivate a caption underneath a transparent field once that field is updated