Is there a query that first finds out whether a field is null that then goes in to more If functions?
This is what I have at the moment:
If mixed = "town centre" then
mixed_ratio = "25%"
else
If mixed = "employment" then
mixed_ratio = "100%"
else
mixed_ratio = "75%"
end if
end if
this code works fine, but if delete what is in the mixed field, it automatically places 75% in the mixed_ratio field, which is not what I want. What I want is for the mixed_ratio field to be null if the mixed field is null.
I have tried putting an If Function at the beginning that says If mixed = Not Null then do this and this etc etc and finish it with mixed_ratio = Null but it doesn't seem to work and also stops the rest of the If Functions happening.
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code: Private Sub cmdUpdateDates_Click() '################################### 'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time]. '################################### Dim intCounter As Integer intCounter = 0 Dim rs As ADODB.Recordset
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
I am having problems with setting up a set of combo boxes.
What I am trying to do is if combo Productline is empty then in combo PartNumber would show all products but if combo Productline has a value selected then in the combo partnumber would only be able to select the partnumbers in that productline.
Hi, I have some problem with assigmnet with date and string variable. what i wana do is get data from Forms textboxes into variable and then by insert query send to history table.
the problem occurs when there is blank textbox its says invalid use of null.
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
I have created a data base over the last few months I just kept adding information in the fields that I created but now have a problem I have the sections in a drop down menus of which access will let me ad info but when I com to save I get this message "Index or primary key cannot contain a null value"
Access will not let me save any added info in any field I have tried all the ways that I know to get round this problem to no avail it is most properly some think silly but I am desperate now as I have worked hard on this project.
I am using Access to connect to a sybase (symposium) database... I pull records depending on what state the record was pegged.... This is based apon a user inpuyting a code on a telephone. Of the user puts a number that does not reprsent any of our set codes it will show the code feild as blank or null. I need to make this blank feild something representable as furhter down my querys it removes these blanks or nulls. I would like the blank field to be shown as "Bad Code"
Any help would be great...
p.s I have used iif and as and like and nothing is working..
I would like to know if is any way to inverse the following code: This: if isnull (txt1) then 'recognise if the box is empty to something like this: if isNotNull (txt1) then 'recognise if the box is not empty
In response to: http://www.access-programmers.co.uk/forums/showthread.php?t=131593
I personally believe 'NULL' should be banned as a possibility entirely. :eek: It has been pounded into my head that 'NULL' is not the same as 'EMPTY' or 'ZERO'; and that it literally means 'NOT KNOWN'. Now consider a contact database that has a field for lets say Apt# or Street Direction such as N or S or ?? as an example. There are many, many instances where these fields would be left blank or empy: NULL | NOT KNOWN. When in fact the situation is known: the object has no such attribute; and in query or some other form of program logic the venerable NZ(subject, response) function may be used which would replace the NULL with a value. Or program logic and or error trapping would be used to handle such occasions. Which is an absurd waste of time and talent IMHO.
Therefore, if there is no applicable attribute to enter (something known) why not say so with 'N/A'? Else what we are saying by using 'NULL' is that we are building a dataset containing data that cannot be known. Kind of like saying
In response to: http://www.access-programmers.co.uk/forums/showthread.php?t=131593
I personally believe 'NULL' should be banned as a possibility entirely. :eek: It has been pounded into my head that 'NULL' is not the same as 'EMPTY' or 'ZERO'; and that it literally means 'NOT KNOWN'. Now consider a contact database that has a field for lets say Apt# or Street Direction such as N or S or ?? as an example. There are many, many instances where these fields would be left blank or empy: NULL | NOT KNOWN. When in fact the situation is known: the object has no such attribute; and in query or some other form of program logic the venerable NZ(subject, response) function may be used which would replace the NULL with a value. Or program logic and or error trapping would be used to handle such occasions. Which is an absurd waste of time and talent IMHO.
Therefore, if there is no applicable attribute to enter (something known) why not say so with 'N/A'? Else what we are saying by using 'NULL' is that we are building a dataset containing data that cannot be known. Kind of like explaining:
how do I set an entire colum to not accept nulls. example I have a colum that will be checkboxes. I want all the checkboxes to be in the unchecked state.
When filtering by form I suddenly get the words: Is Null / Is Not Null appearing in the drop down menu of all fields. I can then no longer filter at all? This is carried over to filter by form too!
When I export, or cut and paste the records into my backup database (filters working ok before I do!) the same thing happens!
I new to access and need some help, here is my problem. I have a query that gives data to a table, the only problem is that there is blank fields in the query and from what I read that is not a good thing when doing calculations. Here is the formula that I am using in the query DaystoCloseNonQSF: IIf([QSFNonQSF]<>"qsf",CalcWorkdays([DateReceived],[DateCompleted]),"") Then I need to get the average of the days that this formula returns, since there is blank fields it won’t. I hope that this makes sense.
I am trying to create a query whereby it will only show only those records when the x field is null. I also need a separate query to do the opposite, i.e. to show only those records which have x field not null.
How can I do this?
I know I need a statement on the x field but don't know what it is?
I'm trying to get this to work but is having a few problems. Hope someone can help.
SELECT * FROM MTL_Log WHERE (((MTL_Log.AccountingName) Is Null) AND ((MTL_Log.AccountingID) Is Null) AND ((MTL_Log.AccountingDate) Is Null));
I have the above right now. But it's not working right. I need the statement to pull all the records where AccountingName and AccountingID and AccountingDate are null and also if any of the field is null. Right now, if at least one of the field is filled in, the do not show in my report.
Any ideas on how to get this to where if any of the field is null then the record will show?
I have a table with a name field and five other fields. I want to create a query where it would tell me if the fields have anything in them or not. I created a query and put in the criteria "is null" and I got nothing in return. Then I put "is null" in the first criteria field and "is null" in the remaining field, but on the "or" criteria line. It worked, but I'm missing records because they dont add up between the number of records in the table and between the nulls and not nulls. Can anyone tell me why and what I am doing wrong please?
I guess my brain is locked up....as I can't get the validation code to work correctly. I have a text field txtquantity which if contains a value then txtdesc must too contain a value. BUt if txtquantity is null then txtdesc should be null too. Help would be greatly appreciated
hey guys. i have a field by the name of ISSUE. i am using this afterupdate event procedure which is as follow:
Private Sub ISSUE_AfterUpdate() If Not IsNull(ISSUE) Then ISSUE = Format(ISSUE, "000000") End If End Sub
if a user enter like (23) in my ISSUE field my procedure will run and it will replace the number by (000023) to make it a six digit. i wanted to create a primary key so then a user cannot enter the same number again or cannot duplicate a record but i cannot because primary key cannot take a null value. Can you all tell me anything to add in my procedure so that once the number is entered like (23) another user cannot enter (23) again in the record. Thanks
Attached are screen shots of three queries used to get a percentage of jobs. If there is no jobs within the date range query 3 shows nothing. Can anyone tell me how if there are no jobs within the date range requested to get the query to give me 100%?
Hi. I have a db with a numeric field called Eur_2. This field is populated with data imported from an excel file. When the Eur_2 excel field contain Null, in the correspondent Access Field the value inserted is 0 (zero). I have necessity to put in the Access field Null value. The VBA code that I use for import is:
Eur_2 = XL.Application.Cells(indriga, indcolonna + 10) If Eur_2 = 0 Or _ Eur_2 = Empty Then Eur_2 = Null End If
but when i open the Access table, the value inserted is 0 (zero). There's something to do for change 0 (zero) to Null?
I have field in my table, which is required (in access, or not allow null sql server), and have defaults of 0.
I have a form with a combo box bound (thru query) to this field - how do I let the user choose something from the list and then decide they actually didn't want to choose anything - and so clear the list.
Hear I get an error saying the field cannot accept null - how do I just force it to accept 0 - ie no choice??
