Trouble With Nulls
Jan 2, 2006
hi,
i am having trouble with a criteria expression that seems to have an issue when it runs into null data in my table.
the query column is as below ...
DateSerial(Year([SomeDate]),Month([SomeDate]),1)
and the criteria for this column is ....
DateSerial(Year(Date()),Month(Date()),1)
So basically I am looking for those fields that match the first date of this current month.
for some reason when there are null values in the table this thing chucks back data type mismatch error but when i take the criteria away its all good
any ideas why this is happening?
View Replies
ADVERTISEMENT
Nov 15, 2005
Hi there,
I have 4 fields in a table(Call_Freq, Call_Week, Call_Day, Call_Sequence)
I have to make a query that finds records with Null values in these fields.
If they are all null that is fine so I only want to return records with either one, two or three of the columns with Null values.
Any help would be appreciated.
View 3 Replies
View Related
Feb 15, 2007
Hey guys,
I'm doing a pretty basic query that
Selects about 5 fields
From Table1
Where
(value=0 AND name <> HOT)
OR
(value=Null AND name <>HOT)
I also have some records (4 specifically) that have a name = nullvalue. When I run this query it picks out all the records with a name, and skips HOT(which is correct), but skips the nulls which I also want to include. Can someone help me in finding the reason for this error?
View 5 Replies
View Related
Feb 23, 2005
I already posted this problem in the query section, but got no where with it... here is the problem now...
I have a form with three combo boxs - cboStatus - cboFunder - cboResource on my form. Depending on what you chose as an option the subform, which is based on a query will show the data with the chosen criteria.
Only the Status field is a required field, so there are lots of nulls in the funders and the resource fields.
In the all the fields I have "ALL" or "*" as an option.
So in the criteria in the query I use this...
Like fCboSearch (forms!form name!combobox)
This calls up this function...
Public Function fCboSearch(vCboSearch As Variant)
If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If
End Function
This works great to show me all the records or just records with a certain criteria selected, BUT BUT BUT I cannot get it to show my the NULLS, they are no where to be found. How can I change this to show me NULLS??
Thanks. Joye
View 5 Replies
View Related
Jul 7, 2005
I have a table which records are appended to in batch format. I would like to update a column called batch number for newly appended records with the Max of that column + 1 but I keep getting errors such as "operation must be an updateable query". Anybody have any suggestions. Mind you I am not doing this in VB or SQL formats, I use the query design format. Thanks, Jim
View 3 Replies
View Related
Oct 25, 2005
In my Accounts table I have a field called Status. This field is populated by single letters. A=Active, P=Paid and so on. I have a status (R) that is occasionally used. It won't show up in my crosstab queries since we currently do not have an account that is in R status. How can I get my crosstab to show the R status as having 0?
View 3 Replies
View Related
Dec 24, 2005
I've been struggling with a problem and I hope one of you can please help.
I created a crosstab query that displays group names on the vertical axis and dates on the horizontal axis. I use a Count for the values. I use a pop-up dialog to get parameters for the dates (between... and), which are organized as Quarters. The query works fine and I can insert it into a report, which also works fine... with one problem. The dates only work when the date parameters request a year (4 quarters), and only work when each quarter has at least one entry. (Only work = I get an error message when one or both of these conditions are violated.)
Sometimes, I want to look at less than a full year, but when I use parameters for three or fewer quarters, I get an error. Same thing if I request a quarter that doesn't have any data. I assume the solution is to convert Nulls to Zeros and have wrestled with both Nz function and IIf, but have not been able to convert the values. I get the correct values in the quary's dataset if they are numbers, and blanks if no data, but no zeros anywhere. I also don't know how to make my report flexible or dynamic, so I can limit the number of quarters if I only want to look at a specific quarter or quarters (less than four).
Any ideas? Thank you so much for your time, and, Happy Holidays!
Stu
View 8 Replies
View Related
Sep 20, 2006
So, I have two tables that, cut down, look like this:
Table1:
Code budget
100 5
110 7
120 3
150 6
Table2:
Code actual
100 4
110 9
130 2
150 1
I have another table that is all the codes plus a description.
I thought I was being clever because I realised that there are items in Table1 that do not appear in Table2 and vice versa. I need a query that is, in effect, Table1 minus Table2.
I linked all three tables via “code”, created my query with the minus calculations and thought it had worked.
One problem. Where there is a “code” in one table but not in the other, the query puts a blank or “null” into that field. Then the minus calculation gives, say:
null - 2 = null
I’m used to Excel where:
blank - 2 = -2
Is there any way to get round this please? This must be a common problem, no?
Thank you.
View 1 Replies
View Related
Feb 7, 2008
is there any way to make a cross tab query default to zero instead of nulls for unpopulated cells
i know i can Nz all the resultant fields, but it seems like treating the symptoms and not the cause...
:confused:
View 5 Replies
View Related
Feb 7, 2008
So, I've been doing doing reading on Append Queries and Nulls. I have the following SQL statement
INSERT INTO tblTestResults ( EnrollmentID, TestType, Result, CompletedDate, BoldFace )
SELECT tblEnrollment.EnrollmentID, tblTests.TestsID, [Forms]![frmHome]![subfrmTesting]![txtResult] AS Result, [Forms]![frmHome]![subfrmTesting]![txtCompletedDate] AS CompletedDate, tblOptionsBF.OptionsBFID
FROM tblEnrollment, tblOptionsBF, tblTests
WHERE (((tblEnrollment.EnrollmentID)=[Forms]![frmHome]![EnrollmentID]) AND ((tblTests.TestsID)=[Forms]![frmHome]![subfrmTesting]![cboSelectTest]) AND ((tblOptionsBF.OptionsBFID)=IIf(IsNull([Forms]![frmHome]![subfrmTesting]![cboSelectBF]),0,[Forms]![frmHome]![subfrmTesting]![cboSelectBF])));
and I can't for the life of me figure out.
IIf(IsNull([Forms]![frmHome]![subfrmTesting]![cboSelectBF]),"",[Forms]![frmHome]![subfrmTesting]![cboSelectBF
Basically what I'm trying to say is that if the cboSelectBf is Null, then leave it null for the the append query or at least leave it blank. If it isn't, use the value that cboSelectBF has according to the combo box selection.
Any help would be greatly appreciated.
View 3 Replies
View Related
Feb 7, 2005
Will someone look at my option group in my form (frmcustomer) and help me figure out why the option group selections for existing records nulls out (visually you cannot see which button had been previously selected).
The option group is working fine in that it is adding the appropriate selection to the record, but when you open the form up in edit mode the selection has been grayed out.
View 1 Replies
View Related
Nov 4, 2014
I have below table in SQL Server which i have linked to MS Access 2010.
Create table test
(
col1 int not null
col2 int not null
)
Insert into Test values (1,2)
Insert into Test values (1,'')
Now when i link the table to access database and open up the table i dont have any issues with that. I tried to copy the test table data from access table and paste it to another access table which is linked to sql server with same schema structure thats when it says "You tried to assign the NULL value to a variable that is not a variant data type"..Its treating the blanks as NULLS when copy/paste the records from one table in access to another.
View 5 Replies
View Related
Oct 5, 2005
Hi!
I want to add two columns a and b. However, there are rows in which EITHER a OR b is NULL. This should be handled as if the cell would be zero. Is there any way besides filling in 0s in the tables?
I tried
SELECT a, b,
IIF( a = NULL, b, IIF(b = NULL, a, a+b))
AS c FROM
but this doesn't work.
Any ideas?
Cheers
Tom
View 1 Replies
View Related
Aug 13, 2013
In the following simplified query, in some months(MonthOf) there were no warranties so I have a null field for AcceptedWarranties:
SELECT qryWarranty.MonthOf, qryWarranty.AcceptedWarranties
FROM qryWarranty;
In order to make my Warranty Trends graph work I need 0s. Other postings show the following statement should get the results I need:
SELECT ISNULL(AcceptedWarranties, 0 ) FROM qryWarranty
But no matter how I try to work this into the original code, the compiler finds reason to reject it.
View 2 Replies
View Related
Sep 16, 2014
I'm using Access 2010 and I'm a novice with databases.I have a table that contains water flow readings taken 4 times a day from multiple wells. I am trying to create a select query that will generate the daily average of the 4 flow readings.
HoleID.....Date....Flow1....Flow2....Flow3....Flow 4....Average
1111.....9-8-14.......0.........null.........7..........4......
1111.....9-9-14.......0.........null.........9..........3......
2222.....9-8-14.......0.........null.........10........7......
Also, my table also has several 0 values and NULLS meaning the well was turned off - I want to exclude the zeros and nulls when I average. How to average these numbers? Do I need to restructure my table?
View 2 Replies
View Related
May 2, 2013
I have a table of around 6000 records comprising 4 fields (A,B,C,D).
- Each field can contain numbers or Nulls.
- Each record can comprise all numbers, a mixture of numbers or Nulls, or all Nulls.
I'd like to build a query that excludes all records that contain any number from a small list of numbers.
This sounds very simple but I am having problems when trying to include records that have Null's in my query output.
For test purposes I tried to exclude all records that contain the numbers 1 or 9 (these numbers can be present in any field).
This works perfectly, in isolation, on Field A (i.e. 1 or 9 but not Null are excluded from field A):
Code:
WHERE Table.A Not In (1,9) OR Table1.A Is Null;
When I try to copy the above, referencing fields B-D, I run into problems - no matter how I try to alter the Boolean operators.
View 2 Replies
View Related
Mar 18, 2013
I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))
DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
View 7 Replies
View Related
Jun 20, 2005
Hello. Recently posted for advice on building an access database on health service training. I had a lot of help but I suppose I haven't really grasped relationships yet. My database looks like this:
tb_staff containing staff_id, first_name, last_name, base, dept_ward, job_title
tb_courses containing course_id and course
tb_training_done containing staff_id, course_id, date_trained, trainer, venue
I have 1:M tb_staff to tb_training_done and the tb_training_done is linked to the tb_courses 1:1 - not sure if this is right but it's the only way access will do it.
Anyway, my idea was to use a form and subform to enter training done but after setting up the lookup it won't allow duplicate courses i.e. same course done each year. Can anyone explain how the lookup works? Where are the details of courses done stored?
Hope that's not too vague?
View 2 Replies
View Related
Aug 6, 2007
im having a bit of trouble generating a field in one of my queries. i think its fairly simple, all i want to do is have the field in my query to show a date, that is just calculated by subtracting 5 days from the current day. i have something that looks like =Now()-5 but that doesnt work obviously. so im just asking whats the imput going to look like if you want to subtract 5 days from a date? oh and im using short dates for the date inputs
View 2 Replies
View Related
Sep 13, 2004
I am trying to build a report that lists all call in's and all tardies on the same sheet. They are two different tables, that I made queries out of because of calculation fields. Do you know how I can include all the needed data in the same report?
What I'm getting when I try is either all call-in's for each name that has tardies or vice versa. I need to see all data on one report
The reason I didn't just put all the data in one table was because we track call-in's by occurence, but we track tardies by pay period. So each call in is logged in the call-in table with the date it happened on, and the tardies are logged in by pay period, so only the total is stored in the field.
Do you have any ideas? I'm really stuck and I've been workin' on it for three days now and I don't think I'm going to find the solution by myself. Sorry. Thanks!
KellyJo
View 5 Replies
View Related
Jul 5, 2005
Gee, I know this is probably so simple...I've never in my life worked with any kind of data base program. I've spent 2 hours trying to make it work and it doesn't. I've tried so many things that I can't even remember what steps I did. I'm trying to create a one column list for phone numbers, no names or anything else...the most important thing is that when I add new phone numbers to this list I want Access to alert me that it is a duplicate and not allow me to add it.....first of all it won't let me add entries with dashes such as 312-5964, I tried selecting "numbers" and also "auto number", both didn't work, what am I doing wrong...can someone give me the steps from the beginning please....I don't have time to figure this out and it's holding me back from finishing a task. I have to get working on my Video Professor Learning CD for Access which I have at home, but right now I'm at work and can't do that...hellllllllp! I'm ready to tear my hair out :eek:
Thanks, Rosey
View 2 Replies
View Related
Mar 13, 2006
I have an Access DB I have to put on numberous laptops. The DB is being transported via a CD. When i try to copy the DB from teh CD to a new folder on the laptop I get messages that the DB may have a virus and it cant be copied, or I need to install a microsoft service bulliten...xxxx ( I am not sure what the number is). The DB does contain VBA code and I am certain it is virus free. Is there some setting in access which enables this file to be copied on laptops which do contain virus or firewall protection or must I disable virus protection on these machines before instlaling the program or is it another issue altogether. Thanks.
View 3 Replies
View Related
Mar 27, 2006
Hi
I have genreated an autonumber in my table, at this is used as key. after importing 135 records it starts to jump 48 records before it creates a new. This means that with every record i import after 135 i have a space of 48 between each. So what sort of thing can cause this? and even better how do i fix it?
Best Regard
Roald
View 2 Replies
View Related
Jul 6, 2006
I have been using the forums for the past month and a half, and they have been extremely beneficial to me. I am working on a database that cross references my companies Steel standards to American standards. I am trying to make it searchable in a couple of different ways. I have borrowed code found on this site to make two different search engines. One works very well but the other I am getting hung up when I try to switch from the previous code to mine. below is an example of my code. It is taken from code placed on here previous called "SampleSearch"
Private Sub cmdSearch_Click()
On Error Resume Next
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
'tblSubject qrySearchCriteriaSub
If Me![Spec] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Spec = """ & [Spec] & """"
End If
If Me![SteelType] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.SteelType like """ & [SteelType] & "*"""
End If
If Me![Group11] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group11 like """ & [Group11] & "*"""
End If
If Me![Group143] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group143 like """ & [Group143] & "*"""
End If
If Me![Substitute1, Substitute2, Substitute3, Substitute4, Substitute5, Substitute6, Substitute7, Substitute8, Substitute9] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Substitute1 = """ & [Substitute1] & """"
End If
sSql = "SELECT DISTINCT [Spec], [SteelType],[Group11],[Group143]from qrySearchCriteriaSub " & sCriteria
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
'[Spec], [SteelType],[Group11],[Group143]
'frmSearchCriteriaSub.Requery
End Sub
I apologize also because I do not know how to post this in a scrollable window. If someone could help me with that too.
The problem with the code is that I am using a form to filter a query, this will then display the table of the steels that meet the requirements. However, I have altered all of the fields to my names and the combo boxes have my information, but they do not seem to place any parameters on the query. I attached the original DB that I got this code from any help would be appreciated.
Riley
View 2 Replies
View Related
Apr 5, 2007
I have a access 2002 database that was handed down to me.. this program does some document control, set up with access rights for different users.I got into it to clean it up a bit and I've hit a snag. I converted the database from a 2000 to a 2002. Amidst playing around with the Log-In screen somehow my Log In button stopped working; except if you type your password and then hit enter.Now the Log In button ( cmdLogIn_Click() ) is pointing at the same exact script as the ( txtpassword_KeyDown(KeyCode As Integer, Shift As Integer) ) function.I don't know any VB and I'm not really familiar with Access, so if anyone could decode this for me I would be really grateful. Here is the script that those two functions are pointing to:Private Sub LogIn_Click() Dim strPassword As String Dim strPasswordAttempt txtPassword.SetFocus strPasswordAttempt = txtPassword.Text cboUsername.SetFocus strPassword = DLookup("[Password]", "User", "[Username] = '" & cboUsername.Value & "'") If strPasswordAttempt = strPassword Then strUsername = cboUsername.Value intAccessLevel = DLookup("[AccessLevel]", "User", "[Username] = '" & cboUsername.Value & "'") bolApprover = DLookup("[Approver]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditDocumentInfo = DLookup("[EditDocumentInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditUserInfo = DLookup("[EditUserInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditReferenceDocuments = DLookup("[EditReferenceDocuments]", "User", "[Username] = '" & cboUsername.Value & "'") bolViewAllApprovers = DLookup("[ViewAllApprovers]", "User", "[Username] = '" & cboUsername.Value & "'") bolDocumentControl = DLookup("[DocumentControl]", "User", "[Username] = '" & cboUsername.Value & "'") ' MsgBox "Your access level is " & intAccessLevel Startup ' run startup sub DoCmd.OpenForm "Welcome", acNormal DoCmd.Close acForm, "Login" LogEvent strUsername, Now(), "Login Successful", "Network Username: " & strNetworkUserName & " on Computer " & strComputerName 'LogEvent strUsername, Now(), "Login Successful", " on Computer " & strComputerName Else MsgBox "That password is incorrect. Try again." LogEvent "Unknown", Now(), "Login Failed", "Attempt: " & CStr(cboUsername.Value) & " , " & strPasswordAttempt & " Network UserName: " & strNetworkUserName & " on computer " & strComputerName End If End SubWhen you click the Log In button it does actually run the script, but it gives me the "The password is incorrect. Try again." message even if the password is correct. I can just click into the txtpassword box and hit enter and it will log me into the database.P.S. I say again, this isn't my code.. its probably super ugly, but I cant' tell :o
View 3 Replies
View Related
May 1, 2007
Hello all active members of this forum,
Like I stated on the title, this forum got me into trouble. By following the reading in this forum and some recommendations from the regular users I have created thre database for the place I work at. now they want me to be the official DB admin! :eek:
So now I'm coming back to you guys for more advice... :rolleyes: what reading should I do to better my Access knowledge over all.
Any recommendation is welcome.
I have already contacted th local college and they do have a distant learning class for Access and I will be taking it the next semester, but I would like to do some reading into becoming a REAL Access programmer with a solid foundation.
There must be some good books out there to purchase to get me started. So all are welcome to give me some feedback.
Thanks!
René
View 14 Replies
View Related
