Topics Search

Update field1 if Null, if Not Null update field2

Update field1 if Null, if Not Null update field2
Views: 4
I am importing a data extract with grades into a student database and using that to update a "Grades" Table. I need to do this every term. This table is then presented in a nicely organized Form.

I have no problems doing this for required courses, which I made their own separate columns for (ex. Course1-Term, Course1-Grade).

The problem is that there are also three electives the students are required to take. These electives can be taken from other programs or even other schools, and as a result it would be impossible and messy to have columns for every possible course.

I thought it would make sense to organize that section a bit differently. The problem is in figuring out how to update the table with my data extracts.

I can get data extracts in Excel which are organized like this (each is a column):
Sponsored Links:

More topics


Update not overwrite

Can you please give me the code on which I can just update an existing entry in a table?
Suppose I have a table that has field names, field1, field2, field3. field1 and field2 has already a value and field3 = Null (im talking about the value in the database)
what I want to happen is to have a form to be filled up to update a value in field3 without affecting field1 and field2. I hope I made it clear.

Converting text to numbers for limited rows

I have a table in SQL Server, which has two columns one of which contains

* numbers as characters (eg. 214.67)
* the letter U

I need to populate the second column with

* conversion of character numbers to numbers
* zero if the other field contains the letter U
* 999,999 if the other field is NULL

I can select only those records which contain numbers as characters

select field1 from Table
(field1 is not null
and field1 'U')

however when I use this in the update I get an error.

SET Field2 = val(Field1)
(field1 is not null and field1 'U')

Selecting only blank fields in access 2007

Lets say I had a table like below, and I only want to select the name and any field which was blank / null
For example, if the name was John and field1, 2, and 6 were blank I would want my results to be
Name Field1 Field2 Field6
John null null null

Count NULL togerther w/ No Stat records

My table:
1 NULL No Stat
1 No Stat NULL
1 No Stat No Stat
1 otherVal otherVal
The result that I want:
I want to select records that contain either NULL or No Stat in either FIELD1 OR FIELD2
then Count #rec for any field that has NULL or No Stat
The problem is the count function does not count NULL, so I use IIf statement to replace NULL with No Stat. I can display those values on the screen correctly, but cannot count these NULL recs together with the No Stat recs. Please give me the SELECT statement that does this

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?

Delete duplicates with conditions

I have a table with FieldA, FieldB, FieldC.

I want to delete records based on FieldA, leaving me with one unique record. The corresponding data in FieldB and FieldC is sometimes NULL - sometimes not.

How do I DELETE duplicate records based on FIELDA where FieldB and FieldC contain the most amount of data (or contain the least NULL values) I.e.

Field1, Field2, Field3
A , data , null | DELETE
A , data , data | KEEP
A , data , null | DELETE
B , null , null | DELETE
B , null , data | KEEP
B , null , null | DELETE
C , null , null | DELETE
C , null , data | DELETE
C , data , data | KEEP

update to null

I would like to update a field in my update querry to clear its data.i tried NULL in the update to but it didnt work

ORDER BY ... If (SQL syntax question)

I have the following as the Row Source for a list box:

SELECT Field1, Field2 ORDER BY Field1, Field2;

Supposing the possible values for Field1 are 1 and 2, how do I get Field2 to sort in ascending order if the value in Field1 is 1 but in descending order if the value in Field1 is 2? I'm guessing it's something like this, but I don't know enough about SQL to work out the correct syntax:

SELECT Field1, Field2 ORDER BY Field1, If(Field1=1, Field2 ASC, Field2 DESC

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.

Fill in Null values

I have a problem with an Access table, I simplified the issue in the below attached zip file

There is a field that looks like this:




and the final results should look like this

Field1 Field2
a a
b b
e e

I tried using dlookup and dsum in a query but I did not succeed