Nulls in SQL and Access
-
Hi
I've just exported all the tables of an Access database to SQL Server so we can use them as linked tables. Unfortunately, it looks as though numeric fields that had '0' default in Access are now 'NULL' and I think this is interfering with the calculations in the Access front end. (eg, a Total field doesn't get populated if one of it's components is empty).
I haven't been able to find anything that discusses differences in handling nulls etc between Access and SQL - any suggestions please? In particular - would setting all the (relevant) column defaults to '0' fix it? And is there a quick and easy way of doing this? (I have about 150 tables, don't know how many columns are affected)
Many thanks
Janet
-
It sounds like when you proted the Acess tables to SQL the DEFAULT setting did not make it.
Your Access calculations will need to be modified to be something like ......
Nz(Field1, 0) this way if there is a NULL value it will be changed to 0. Inside SQL the code is ISNULL(Field1, 0)
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Also,
If you have a calculation in a field such as
Balance:Invoices-Payments
you can rewrite it like this in your client
Balance:if(isnull(Invoices),0,Invoices)-if(isnull(Payments),0,Payments)
-
Thanks to both - sadly, this is a sorry tale of inheriting a complex and undocumented system, and I have only a fairly superficial experience of Access so far. My problem is working out where all these calculations and assignments are. That's why I'd hoped that 'cleaning' the underlying data might solve the problem - sadly it didn't. Ah me ...! I suppose there's no global search facility for processing that's buried in forms and queries? Otherwise, I guess I just have to slog on with it.
By the way - was there something I could have done to make sure the defaults got ported with the tables?
Thanks anyway
Janet
-
Hi Janet,
Rather than going through all of your code and changing SQL statements, I think you'd be better off fixing your data: run update queries to change the field values from NULL to zero.
You can also add a DEFAULT constraint to the SQL tables so that records added in the future will have zero value instead of NULL.
To add a default constraint for value zero, use this syntax:
ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> DEFAULT 0 FOR <fieldname>
FYI, if you ever need to add a new column with a default value and you want to populate all existing records with the default value, you can do it with a single SQL statement:
ALTER TABLE <tablename> ADD <fieldname> <tinyint,char etc> NOT NULL
CONSTRAINT <constraintname> DEFAULT <default value> WITH VALUES
HTH,
Susan
-
Thanks, Susan that looks much more manageable. Just to check - can I use the same constraint-name for every column I change? or do they each have their own name? and if there's several columns to change in the same table, can I use a list of column names in the statement, or must I do a separate clause for each column:
ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> DEFAULT 0 FOR <fieldname1>, <fieldname2>, <fieldname3>, ...
or
ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname1> DEFAULT 0 FOR <fieldname1>
ADD CONSTRAINT <constraintname2> DEFAULT 0 FOR <fieldname2>
... etc
-
Gibbers,
You can do the 2nd statement (the OR) and EACH DEFAULT must have a unique name unfortunately.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Setting the defaults will help for new records, not for exisiting records.
run an update
update MYTABLE
set PROBLEMATIC_FIELD = 0
where PROBLEMATIC_FIELD is null
Good luck.
-
Thanks to all for your advice - it was a bit of a slog, but all sorted now.
Regards
Janet
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply