Not null, not ' ' what are they? 2

[JavaToPerlNowVB]

I have these tables and I don't know who did but the problem is that I have a field (Text Field) but have used to store a number. And there are blanks for some records. I want to change some those records to a number. I tried like this

as a example

SQL> select prefix, suffix, wo from gisadm.gndlinefacilitymaintpole where prefix='105110' and
  2  suffix='31' and wo=null;

SQL> select prefix, suffix, wo from gisadm.gndlinefacilitymaintpole where prefix='105110' and
  2   suffix='31' and wo=' ';

I couldn't get the wo='BLANK' data to showup but they are there

SQL> select prefix, suffix, wo from gisadm.gndlinefacilitymaintpole where prefix='105110' and
  2  suffix='31';


PREFIX SU WO
------ -- ----------
105110 31 1049036
105110 31 1041865
105110 31 1041865
105110 31 1049036
105110 31 1049036
105110 31 1043039
105110 31
105110 31

8 rows selected.

THanks

 

[KenReay]

Hi

Null is a strange beast, it does not mean blank or space, it means effectively unassigned or unknown, ie no value has been saved in the column. Null is not equal to anything else including itself !
so a couple of things to try

change your sql to read:

...suffix='31' and IsNull(wo);

or if wo is somethime null and sometimes space, try:

...suffix='31' and Len(Trim(Nz(wo,"")&"")) = 0 ;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[greely]

try:

SELECT prefix, suffix, wo from gisadm.gndlinefacilitymaintpole WHERE prefix='105110' and suffix='31' and isNull(wo);


I developed a function some time ago which checks BLANK fields whether they are null or emptystring:


Function isItBlank(str As Variant)
Dim str1

str1 = " " & str
str1 = Trim(str1)

If Len(str1) = 0 Then
isItBlank = True
Else
isItBlank = False
End If

End Function

This can be used instead of isNull
Just place the function in a module.

SELECT prefix, suffix, wo from gisadm.gndlinefacilitymaintpole WHERE prefix='105110' and suffix='31' and isItBlank(wo);

 

[greely]

Good answer Ken.

Looks like you beat me by a few seconds.

 

[JavaToPerlNowVB]

Thanks, actually what I am doing is I have a field to enter old "WO" number and another field to enter new "WO" number and button to update old-to-new number. SO I have this query which gets the data from the from so I do dolook up to find the date.

in this case, as I mentioned i have date which has blank "WO" number so I want the user to change blank "WO" numbers to a number. In that way user can leave the old work order number field in the form blank and enter a new number to the new work order field in the form. Then I do something like this

if form_old_wo_field(Which should be a blank in this case) = table_wo_wo_field)
do the update

I think form field blank and table field blank are different but I am not sure whats the rason

thanks for all the help - Thanks greely thanks ken

 

[PHV]

Just a note.
The standard SQL way to test if a column holds a null value:
WHERE wo IS NULL
One JetSQL (or VBA) way to test if a field is null, blank or empty:
Trim([wo] & "") = ""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886