Empty field to set to zero
Solved
rjl
Posted messages
591
Status
Membre
-
rjl Posted messages 591 Status Membre -
rjl Posted messages 591 Status Membre -
Hello everyone,
It's been a long time since I last worked with ACCESS and I'm impressed by how one can lose previously obvious concepts.
I'm working with direct update queries (not SQL or VBA, so the simplest) and I can't seem to test a field that appears to be empty in order to replace it with 0 in a column (declared as numeric), but I've also tried with text!) since I resumed work on the database.
I'm trying to set an empty field to 0 but to no avail: by trying the selection before the update, I end up with an empty sheet facing the 37,000 records present (all empty for this column) as this is just a test set against a database of 1,000,000 lines
In the Criteria line for the considered column, I simply put IsNull([All].[Field14]) but all other attempts have also failed!
I must be making an obvious, unforgivable mistake... yet I can't seem to see it or know how to correct it!
Thanks for your help and see you soon
RJL2b10
Configuration: Windows 7 / Firefox 16.0
It's been a long time since I last worked with ACCESS and I'm impressed by how one can lose previously obvious concepts.
I'm working with direct update queries (not SQL or VBA, so the simplest) and I can't seem to test a field that appears to be empty in order to replace it with 0 in a column (declared as numeric), but I've also tried with text!) since I resumed work on the database.
I'm trying to set an empty field to 0 but to no avail: by trying the selection before the update, I end up with an empty sheet facing the 37,000 records present (all empty for this column) as this is just a test set against a database of 1,000,000 lines
In the Criteria line for the considered column, I simply put IsNull([All].[Field14]) but all other attempts have also failed!
I must be making an obvious, unforgivable mistake... yet I can't seem to see it or know how to correct it!
Thanks for your help and see you soon
RJL2b10
Configuration: Windows 7 / Firefox 16.0
8 réponses
Hello Castours, and thank you for your response,
Yes, of course, I describe it in digital form, but it’s a table I inherited, and it wasn’t originally like that! Nevertheless, I’ve tried using text thinking it would be easier to correct, especially for empty fields.
In fact, for me, this field is generally empty because when I want to filter in the table, "Empty" is suggested and it brings up a lot of rows!
Curiously, if I do a direct sort of the table, everything is nicely organized, including the rows with empty fields, whereas through my query, they are ignored or more precisely, the rows do not appear either on-screen or in counting!
Thank you for your thoughts if it inspires you.
Yes, of course, I describe it in digital form, but it’s a table I inherited, and it wasn’t originally like that! Nevertheless, I’ve tried using text thinking it would be easier to correct, especially for empty fields.
In fact, for me, this field is generally empty because when I want to filter in the table, "Empty" is suggested and it brings up a lot of rows!
Curiously, if I do a direct sort of the table, everything is nicely organized, including the rows with empty fields, whereas through my query, they are ignored or more precisely, the rows do not appear either on-screen or in counting!
Thank you for your thoughts if it inspires you.
Hello
If you want to retrieve empty fields, in a conditionWhere query use Is Null or Is Not Null. It's this syntax that you need to use.
You modify this query in table creation.
Once that's done, either you transform it into a numeric value with a default of 0, or you recreate a new one directly as numeric.
Currently, is your field text?
I don't know if Access can handle a million rows?
Which version of Access?
If you want, can you upload the table to the site with cjoint.com?
If you want to retrieve empty fields, in a conditionWhere query use Is Null or Is Not Null. It's this syntax that you need to use.
You modify this query in table creation.
Once that's done, either you transform it into a numeric value with a default of 0, or you recreate a new one directly as numeric.
Currently, is your field text?
I don't know if Access can handle a million rows?
Which version of Access?
If you want, can you upload the table to the site with cjoint.com?
For a better understanding of the problem, I present the first few records of the table (in reality, 119,000 as an excerpt from the complete table of over 1,000,000 rows!).
So, of course, I know what’s wrong... without knowing how to fix it!
[URL=https://imageshack.com/][IMG]http://img11.imageshack.us/img11/2320/imagtable.jpg[/IMG][/URL]
In fact, the records are ignored as soon as I test a field that appears to be empty!
However, I couldn’t stay without a solution for this test (119,000 lines) and initially, I removed the test that plunges me in searching to replace these "empty" fields with zero! The same problem occurred; since I wasn’t interested in selections for codes > 99, I was able to replace these codes with zero... but the same problem arose, the lines containing "empties" were abandoned as soon as I tested a field from one of the 6 columns involved in the selection! As in this excerpt, Field14 contains no value; my selection resulted in 0 records instead of the expected 37,000!
Because I was able to make the selection on the sample, but certainly in a brutal manner!
For that, I filtered my file successively with each of the 6 selection columns, retrieving only the fields deemed "empty" or less than 100 (checking each of the 99 values of each relevant field).
It’s cumbersome, it worked on this excerpt without solving my underlying problem!
I hope this insight will be constructive.
Have a good Sunday and thank you again
@See you soon RJL2b11
So, of course, I know what’s wrong... without knowing how to fix it!
[URL=https://imageshack.com/][IMG]http://img11.imageshack.us/img11/2320/imagtable.jpg[/IMG][/URL]
In fact, the records are ignored as soon as I test a field that appears to be empty!
However, I couldn’t stay without a solution for this test (119,000 lines) and initially, I removed the test that plunges me in searching to replace these "empty" fields with zero! The same problem occurred; since I wasn’t interested in selections for codes > 99, I was able to replace these codes with zero... but the same problem arose, the lines containing "empties" were abandoned as soon as I tested a field from one of the 6 columns involved in the selection! As in this excerpt, Field14 contains no value; my selection resulted in 0 records instead of the expected 37,000!
Because I was able to make the selection on the sample, but certainly in a brutal manner!
For that, I filtered my file successively with each of the 6 selection columns, retrieving only the fields deemed "empty" or less than 100 (checking each of the 99 values of each relevant field).
It’s cumbersome, it worked on this excerpt without solving my underlying problem!
I hope this insight will be constructive.
Have a good Sunday and thank you again
@See you soon RJL2b11
Hello,
The fields causing this issue are indeed numeric; I've tried various methods with IsNull but the lines tested this way are not selected and are even ignored! I then tried with IsEmpty since it works in direct filtering (selecting rows marked as "Empty") but, to my surprise, it doesn't work in the query! I unsuccessfully attempted (for the same reasons) to turn my empty fields into zero... but they are equally ignored (Num of records to modify: 0)!
I have 119,000 rows in the test and over 1,000,000 in reality.
A "detail" is blocking normal operation, but what?
See you later and of course, thanks again. RJL2b11
The fields causing this issue are indeed numeric; I've tried various methods with IsNull but the lines tested this way are not selected and are even ignored! I then tried with IsEmpty since it works in direct filtering (selecting rows marked as "Empty") but, to my surprise, it doesn't work in the query! I unsuccessfully attempted (for the same reasons) to turn my empty fields into zero... but they are equally ignored (Num of records to modify: 0)!
I have 119,000 rows in the test and over 1,000,000 in reality.
A "detail" is blocking normal operation, but what?
See you later and of course, thanks again. RJL2b11
Good evening,
Thanks to your observations, I was able to correct a major problem; the selection is done in part:
Having been long out of touch with Access, I had forgotten essential points for selecting by Expr: fields with expression such as
It would be perfect... if I didn't have to reproduce this selection with 5 other fields (by And) and that's where it gets tricky!
I tried the normal process, but also SQL, for which I thought stacking would be easier, but nothing worked!
I also unsuccessfully tried to put zeros for the values that didn't interest me (Null or > 99) because it seemed simple to associate the selection by And of 6 ranges... but I didn't have any lucky choices that day and again... missed!
I'm still looking a bit, but I will reach out again if I really can't find a solution!
See you soon, whether it's for support or to share how I resolved my difficulty.
Thanks again for your support RJL2b11
Thanks to your observations, I was able to correct a major problem; the selection is done in part:
Having been long out of touch with Access, I had forgotten essential points for selecting by Expr: fields with expression such as
Expr:IsNull([Field14])with True as a criterion associated with a value test in Or, which is more classic and simply placed on the next line in its own column.
It would be perfect... if I didn't have to reproduce this selection with 5 other fields (by And) and that's where it gets tricky!
I tried the normal process, but also SQL, for which I thought stacking would be easier, but nothing worked!
I also unsuccessfully tried to put zeros for the values that didn't interest me (Null or > 99) because it seemed simple to associate the selection by And of 6 ranges... but I didn't have any lucky choices that day and again... missed!
I'm still looking a bit, but I will reach out again if I really can't find a solution!
See you soon, whether it's for support or to share how I resolved my difficulty.
Thanks again for your support RJL2b11
Hello castors,
I was afraid I would have to come back to ask for help again!
I searched through the tutorials, the forums, and did multiple trials... without success!
I have 6 fields (always the same); I want to set each field to 0 (impossible coding allowing for an easy choice later) when it is null.
[URL=https://imageshack.com/][IMG]http://img687.imageshack.us/img687/9944/imagtable2b12.jpg[/IMG][/URL]
This is just an example, but one of the most common responses is an invalid name (I tried without the table name, neither on line 2 (Table), nor as part of the name ([All][....)
Of course, I also attempted unsuccessfully to directly put the table name on the second line or to remove it from the tested name: nothing works!
Thanks for your help if you see how I can get out of this mess!
See you soon RJL2b12
I was afraid I would have to come back to ask for help again!
I searched through the tutorials, the forums, and did multiple trials... without success!
I have 6 fields (always the same); I want to set each field to 0 (impossible coding allowing for an easy choice later) when it is null.
[URL=https://imageshack.com/][IMG]http://img687.imageshack.us/img687/9944/imagtable2b12.jpg[/IMG][/URL]
This is just an example, but one of the most common responses is an invalid name (I tried without the table name, neither on line 2 (Table), nor as part of the name ([All][....)
Of course, I also attempted unsuccessfully to directly put the table name on the second line or to remove it from the tested name: nothing works!
Thanks for your help if you see how I can get out of this mess!
See you soon RJL2b12
https://www.cjoint.com/?BKnl2UmNBP8
Here is an example in a search query for nulls on the postal code.
Here is an example in a search query for nulls on the postal code.
Hello castours,
I haven't forgotten you, but caught up between my own problems and those of my son, whom I support for this work, it sometimes takes me a while (especially when I add the loss of my prepared response at a very late hour and not revisited at the moment!).
A final example allowed me to finalize this work (at my level). In practice, I was confusing Est Null with EstNull(), which forced me to mention a field name, and then nothing else matched!
I simply followed the example, resulting in an immediate response that exactly addressed the request: [URL=https://imageshack.com/][IMG]http://img9.imageshack.us/img9/3261/img2b131a.jpg[/IMG][/URL]
So, I completely abandoned Nz, which required a second query for the selection, even if it was quick, and then, a complement to remove the zeros that complicated reading.
Thanks again and have a great weekend RJL2b16
I haven't forgotten you, but caught up between my own problems and those of my son, whom I support for this work, it sometimes takes me a while (especially when I add the loss of my prepared response at a very late hour and not revisited at the moment!).
A final example allowed me to finalize this work (at my level). In practice, I was confusing Est Null with EstNull(), which forced me to mention a field name, and then nothing else matched!
I simply followed the example, resulting in an immediate response that exactly addressed the request: [URL=https://imageshack.com/][IMG]http://img9.imageshack.us/img9/3261/img2b131a.jpg[/IMG][/URL]
So, I completely abandoned Nz, which required a second query for the selection, even if it was quick, and then, a complement to remove the zeros that complicated reading.
Thanks again and have a great weekend RJL2b16