if a field contains some null values, then how to replace each of these null with its previous value ? eg:
Phone No Bill date Amt Pending
222000 2-2-07 3456/-
null 17-2-07 2356/-
224000 12-2-07 1235/-
null 08-2-07 456/
now i want to fill the blank below 222000 with 222000 and 224000 under 224000. how to do it? plz guide me for office 2003.
Hello,
I am putting a check whether the inputted value in a text box(txt_id) is filled or not. If it is 'null' then it should not execute the insert statement but somehow the check for the null value is not working. Can someone tell me why??
Private Sub cmd_inserer_Click()
If txt_id.Value = Null Or txt_id.Value = "" Or txt_id.Enabled = False Then
info = MsgBox("Veuillez remplir champ « Id »", vbInformation)
ElseIf txt_id.BackColor = &HFF& Or txt_libelle.BackColor = &HFF& Or txt_description.BackColor = &HFF& Then
info = MsgBox("Veuillez remplir tous les champs fausses!", vbInformation)
Else
MsgBox txt_id.Value
Set db = CurrentDb
strSQL = "INSERT INTO objectif ( id , libelle,description) values ('" & txt_id.Value & "' , '" & txt_libelle.Value & "','" & txt_description.Value & "') ;"
db.Execute strSQL
db.Close
Form_objectif.Requery
txt_id.Value = ""
txt_libelle.Value = ""
txt_description.Value = ""
cmd_ajouter.Enabled = True
cmd_modifier.Enabled = False
End If
End Sub
I have a query, and it has been working just fine, till today. I have it based on a field being null. This field shows null in the table, it is a text field but it is a 19 digit number if there is anything in there. The fields didnt come up in the query when it was based on Is Null. But if I changed it to a <1 they came up just fine. and After further testing, the change works just how I had it working with the Is Null or so I thought. Any ideas on the WHY behind this, I am really curious about it, thought I would get some opinions on why this happened.
View 8 Replies View RelatedCan anyone see why the sql code below is not working, it works up unitl
StrSQ4 = DLookup("[D Status]", "tbl_Delupdate", "[Cylinder Barcode Label]='" & Stringy1 & "'")
But when i try to get the latest date for that Cylinder Barcode as shown below it does not bring out a value
StrSQ4 = DLookup("[D Status]", "tbl_Delupdate", "[Cylinder Barcode Label]='" & Stringy1 & "'AND [Date of D Status]=#" _
& DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'") & "#")
I have a database that I maintain the history of our football league in. What I am trying to do is count each team and the number of wins in each year
The query works fine except in the cases where the team had ZERO wins. Then of course it returns nothing and screws up the other queries when there is a hole for a year.
2 Tables in the database
INFORMATION
TEAM
YEAR
(other fields not relevant)
SCORES
TEAM
YEAR
RESULT (either WIN, LOSS or TIE)
(other fields not relevant)
I want to count the number of wins and return it to this query EVEN IF THE NUMBER OF WINS IS ZERO (0).
When I run the query I receive
The query returns this:
2000 Westside 6
2001 Westside 3
2003 Westside 1
2004 Westside 3
In 2002 Westside was 0 wins -11 losses and the query not finding any WINS in 2002 did not return a record.
How do you get the query to show
2002 Westside 0
==========================
Current SQL Query
SELECT Information.Year, Information.Team, Count(Scores.Result) AS CountOfResult, Scores.Result
FROM Scores INNER JOIN Information ON (Scores.Year = Information.Year) AND (Scores.Team = Information.Team)
GROUP BY Information.Year, Information.Team, Scores.Result
HAVING (((Information.Year)>2000));
I have an append query created to add files to a table from an imported excel file. The table to which I am appending (SubTBL) has a field names Observation. This field is not required. There is a relationship to another table (ObservationTBL) which has 3 records. The information I am trying to append includes 7000 records, about 4000 have observations assigned and they match the information in the ObservationTBL. The remaining 3000 have blank records for observation. When I run the append query, only those with observations are appended. The rest are not added because of "key violations". I removed the relationship between the ObservationTBL and the SubTBL and the append query runs. Then when I try to re-do the relationship it fails "Violates referential integrity rules".To be clear:
1. The records without an observation are blank. (There are other fields in this append that have blank fields also and they are not causing any problems)
2. The field observation is NOT set to required.
Is it not allowing Null records?
I am using Access 2010 on Windows 7 (64-bit).
I have create a table clients that contains multiple column i.e. Id, First Name, Last Name, Starting Date, Company , while defining the table I did not mark REQUIRED property of any column
I have created a form based on this table "CLIENTS",
I create the company as combo box and taking the list from COMPANIES.ID
I put the validation rule as IS NOT NULL and put validating text Select Company from list.
If I marked REQUIRED property of this column in table definition as YES then it displays system generated message with tablename.controlname, while i want to display my own message.
But when i input the data and leave the Company column blank the validation is not executed.
There are three columns in the form on which I want to apply the same validation.
I have a question about errors on null value.I have made a small database for tryout, it has to be implemented in another one.And the small database is working.I have one table where there is one field called BatchInput.I scan a barcode into it and let two query's breaking it apart. I scan this batch into the table field
BatchInput: 20 MAY 2004H149-082-79 A4147011A05
Then I have my first query (Qrybreak1) extracting the date and deleting H14
Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)
The result is this:
Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05
The second query (Qrybreak2) I look for the first space:
space: InStr([PartCertNr];" ")
Then with the result I cut it into two pieces
PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))
[code]...
And query (QryResult) even wont start, giving a popup with Invalid procedure call..How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?
Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
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
[Code] ....
Hello all,
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?
Thanks.
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.
e.g
myStringVariable = Forms!myform!EmpName
myDateVariable = Forms!myform!EmpDOB
this code is behind the update button which i press when ever i want to shift data to History table
so when the fields are empty the invalid use of null error arrise
any idea how to handle this null specially in date
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
I think the title pretty much sums it up....
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.
The query is refusing to return any results...
Any ideas?
Hi all,
I'm making a simple query to tell me how old a list of members are in a database.
I figure as I have their birthday, armed with todays date access should be able to tell me (in years only) how old they are.
Any help is appreciated
Hi,
I have 2 computers (computer 1 with Office XP and computer 2 with Office XP SP1). An MDE file created from an MDB file on computer 1 will not work on computer 2 (run time error), but an MDE file created on computer 2 also does not work on computer 1 (error message was "database corrupted").
Does anyone know why and what I can do to make the MDE work on both machines?
Hi. I have got a small database, I have split it into front and back end. I then try to make an MDE out of the front end and after going to tools>db utilities>make MDE file, i got the save as box open up and i pressd save to my desktop,the box disappears and the screen is just blank(like there is no database file has been open,at the bottom of Access it says Make MDE/ADE.
And it just crashes.. it doesnt come up with any errors.
If anyone has any information pleeeeeaaaseee help.......
My Combo Box works and displays new entries in text boxes in my form. The selection appears in every record. I would like the selection to be stored only in the displayed record and not for each and every record. Any ideas?
My combo box works on an existing query( Qry1). I would like the data from my query (Qry1) to be displayed in the record (in Form1) which I am viewing. When I go onto a new record (IN Form1) I would like to make a new selection from my query (Qry1) and then store these details in the new record in Form1.
I am a little frustrated - please help.
This seems so simple, but, being a novice, I'm scratching my head.
I'm using an unbound text box in the form footer to sum the value of a column in the form ( =Sum([LineTotal]) ). [LineTotal] evaluates correctly, yet the sum is always #ERROR. Is there something I am missing, or am I correct in thinking that this should work?
Thanks to anyone who can shed some light.
-Don
I open a form that is bound to a linked SQL table. On that form a have a list box that shows a series of records based on a query. The listbox DOES contain the PK field "Record_date" in its query.
I am expecting that if I click on a given record in the list box the form would change to that matching record. What am I doing wrong ?
List box code:
Private Sub List212_AfterUpdate()
' Find the record that matches the control.
Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[Record_date] = #" & Format(Me![List212], "mm/dd/yyyy") & "#"
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
End Sub
If I change the form record and click the list box, its always takes me back to the 1st record - not the matching record selected in the form.
Help ?
Hy,i've got three tables:
products:
product_id,product_name
months:
month_id,name_of_month
calculations:
calculations_id,month_id,amount
Also have this query:
SELECT DISTINCTROW products.product_name, Sum(calculations.amount) AS [Sum Of amount]
FROM products INNER JOIN calculations ON products.product_id=calculations.product_id
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value)
GROUP BY products.product_name;
My problem lies in this part of query :
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value).
I've got problem when i want to get values from my textbox on my form.
If i instead (forms!frmMyForm!txtMyTextbox.value) put (1,2) my query works fine,but when i wanna get this same values from textbox on my form it returns me an error.
I have also noticed one more thing:If i put in my textbox just value 1 it works fine,but if i use more values separeted by comma instead of result it returns me an error.
Why is this,and how to avoide this problem?
Thanks!
Private Sub propertypass_Click()
If [Forms]![UpdatedFullInventory]![PAM_ID] = [Tables]![UpdatedPropPassInventory]![PAM_ID] Then
MsgBox "A property pass for PAM ID " & PAM_ID & " has already been created. Please search the property pass database for more information!", vbOKOnly, "Error!"
End If
My debug message says
Microsoft can not find the field 'l' referred to in your expression.
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.