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:

Similar posts...

 

Converting text to numbers for limited rows

Converting text to numbers for limited rows Icon
I have a table in SQL Server, which has two columns one of which contains

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

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
where
(field1 is not null
and field1 'U')

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

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

Delete duplicates with conditions

Delete duplicates with conditions Icon
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
 

VB Help update is supposed to be null.

VB Help update is supposed to be null. Icon
I currently have the following vb code, which works if you are trying to update a record, but it still gives the message prompt when I am trying to add a new record and the field that it is trying to update is supposed to be null.

How can I have this code function when trying to update a record already filled out and not pop up when adding a new record?
 

duplicate alias ??

duplicate alias ?? Icon
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
 

Update where query not working

Update where query not working Icon
This should be simple but I can't figure it out.

I have a large table that contains NULL values and values like €0,00 in a field with the name price. I need to replace these values with €0,01

So I thought my rusty sql knowledge and google could help me solve this, but I am stuck.

To remove the null values I used the query:

UPDATE tblItem
SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is Null));

and that worked well to remove the NULL values and changed them to €0,01

00 to €0,01

UPDATE tblItem
SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is "€0,00"));

But it does not and I am puzzled why. It must be something very obvious that I am overlooking, but what?
 

Executing MySQL Statement

Executing MySQL Statement Icon
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.
 

Updating Null Fields with values

Updating Null Fields with values Icon
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:
 

updating access db problem

updating access db problem Icon
I am using to followin code to connect to a db and update it.

[CODE]

However, I am getting the following error:

Syntax error (missing operator) in query expression '(((? = 1 AND Surname IS NULL) OR (Surname = ?)) AND ((? = 1 AND First_Name IS NULL) OR (First_Name = ?)) AND ((? = 1 AND Title IS NULL)OR (Title = ?)) AND (Card_No = ?) AND ((? = 1 AND Address_1 IS NULL) OR (Address_1 = ?)) AND ((? = 1 AND Address_2 IS N'.
 

How to filter form when value selected from combobox is null?

How to filter form when value selected from combobox is null? Icon
I have a combobox which has null as a valid value.I want the form to get filtered when user selects null as a value also.However no filtering happens when the value is null.I would liketo know is whether it is possible to filter if the record selected is null as the filter criteria will be empty string then?I am not posting any code as my question is just that can criteriabe null and still filter?
 

Trouble with UPDATE query in VBA

Trouble with UPDATE query in VBA Icon
I'm trying to create a "Select All" check box using an update query that uses the same criteria as the query that is the data source for the subform I'm viewing. However, it isn't working quite right.

If any of the four controls in the criteria are null, then I get an error message that says "invalid use of Null". If all four of the controls have something in them, then I get an error message that says "Too few parameters.

Expected 1. I had originally copied and pasted the criteria out of the query that is the data source for the subform (that works whether or not the search controls are populated) and then to shorten the code, I used the Replace function. No change.
...
Oh, one more thing. When I get the error message that says "Invalid use of Null", it highlights whichever line in the WHERE clause that has the control that is null.