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
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.
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).
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));
Hello, is there any way I can have my single-parameter query bring up a lookup list of options from a table field rather than relying on the user to correctly type in the criteria? Then when this query runs I will know the user is picking one of the correct options from the lookup list.
Hello, all! Can you please look at the following code and tell me why it throws a compile error of user type not defined. I'm trying to use this code to automatically pull in the previous room # and associated data into the next record, so that I don't have to enter the data every time, as sometimes there are fifty entries with the same room # and associated data. Can you tell me what it's wrong with it? Thank you!!
VB Code: Original - VB Code Private Sub Form_Current()Dim s As StringDim db As New ADODB.ConnectionDim rs As New ADODB.RecordsetSet db = CurrentProject.Connections = "SELECT Last(Room #) AS Room # FROM [tblFF&EBudget] WHERE Budget Line=(select max(Budget line) from [tblFF&EBudget])"Set rs = New ADODB.Recordsetrs.Open s, db, adOpenDynamic, adLockOptimistic'add to the tableWith rsMe![Room #] = .Fields("Room #")End WithSet rs = NothingSet db = NothingEnd Sub Private Sub Form_Current() Dim s As StringDim db As New ADODB.ConnectionDim rs As New ADODB.RecordsetSet db = CurrentProject.Connection s = "SELECT Last(Room #) AS Room # FROM [tblFF&EBudget] WHERE Budget Line=(select max(Budget line) from [tblFF&EBudget])" Set rs = New ADODB.Recordsetrs.Open s, db, adOpenDynamic, adLockOptimistic'add to the table With rsMe![Room #] = .Fields("Room #")End WithSet rs = NothingSet db = Nothing End Sub Thanks!
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.
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
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)?
I have a query that I made for about five fields, where the criteria is
Like "*" & [Forms]![DATA SEARCH]![Box] & "*"
Where box is the name of the field that I am searching for.
However, some of the fields in my form are left blank, which makes this refuse to give any results when I try to query it with a form. The other problem that I have is that the fields are bland in different parts of the 1,000 some-odd row table, which was imported from MS Excel.
Maybe this should be posted under VBA.... not sure. Havent seen answer that works yet. Its a form with an option group "PickWO" When you pick an option, different fieilds become visible-non visible. I created a field "PickWOvalue" to store the option value "1" or "2". That part works fine.(visible) The problem is when you reopen the form to view the records the option box value does not show or change the fields visible properties. And the on open cuauses an error. Anyone see the problem?
Private Sub Form_Open(Cancel As Integer) Me.PickWOvalue.Value = Me.PickWO.Value End Sub ````````````````````````````````````` Private Sub PickWO_AfterUpdate()
Call NotVisible
Select Case Me.PickWO
Case Is = 1 Me.ReInspectionDate.Visible = True Me.Price.Visible = False Me.WOPreview.Visible = True Me.PrtWO.Visible = True Me.WBMInvoice_.Visible = False
Case Is = 2 Me.ReInspectionDate.Visible = False Me.Price.Visible = True Me.cmdPreTag.Visible = True Me.cmdPrtTag.Visible = True Me.WBMInvoice_.Visible = True
I have an access database that links to a SQL server. My problem is when I made a copy of the Access database, made changes to one sub report and mapped to test tables and now I have VB code that runs the overall report where the SQL gets jammed on the join, (which I noticed through debugging) but when I take the same SQL and run it in the Query view - the SQL works.
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 have Header form and subform subform in it with following fields
Subform fields are :
1. MQE_No : (Text field as it will be like MG-120, MG230), Duplicate OK) 2. RPO_No : (Number field – Double, Duplicate OK ) The are project no. 3. ForemanNo (Number field – Long Int – Duplicate OK)
Header form fields are ;
1.ForemanNo – Long Int – No Duplicate 2.Other fields…
The subform field have ForemanNo One-To-many relationship with Header form.
Following project MG-411 need to enter (assign) to foreman no. 641
Actual data to store in subform
MG-411 (MQE_NO) 5421654 (RPO_NO) 641 (FOREMANNO)
I started to enter data into subform selecting the ForemanNo at header so records to enter in specific foremans account. Everything is fine here.
What I need is:
I would like to select / add another Foreman into the header and start entering same above data for another Foreman. Bcz sometimes we need to do like this in case previous Foreman may go on to a week vacation. We can not stop project for a week. So need to assign project to other foreman.
When selected the Project (MG-411) thru a combo to assign it with other foreman, it should give a short msg to user that this project has already been assigned to “XYZ” forman. Need to assign again “ (Y/N box )
If Y then data entered otherwise cancelled.
To achieve this, I did the following but only 50% success. (Used Cmbo CboRPO2 select MQE_NO). Tried to satisfy both condition but no use.
Private Sub CboRPO_AfterUpdate() If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO = " & ENO) Then MsgBox "RPO ALREADY ASSIGNED TO SOMEONE / FOREMAN", vbOKCancel, "WARNING!!!" Me.Undo Exit Sub Else
Dim Msg, STYLE, TITLE, HELP, CTXT, Response, MYSTRING If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO <> " & ENO) Then Msg = "RPO ALREADY EXIST WITH OTHERFOREMAN, ASSIGN AGAIN TO ANOTHER ?" STYLE = vbYesNo + vbInformation + vbDefaultButton2 TITLE = "!! ATTENTION !!" HELP = "TEST FILE" CTXT = 1000 Response = MsgBox(Msg, STYLE, TITLE, HELP, CTXT)
Else Me.MQE_NO = Me.CboRPO.Column(0) Me.RPO_No = Me.CboRPO.Column(1) Me.WORKSHEET_NO = Me.CboRPO.Column(2) Me.WORKORDER_NO = Me.CboRPO.Column(3) Me.WORK_DESC = Me.CboRPO.Column(4) Me.PL = Me.CboRPO.Column(5) Me.PipeLineKM = Me.CboRPO.Column(6) Me.DiaMeter = Me.CboRPO.Column(7) Me.PipeLength = Me.CboRPO.Column(8) Me.PipeLineArea = Me.CboRPO.Column(9) Me.P = Me.CboRPO.Column(10) Me.RPO_AMOUNT = Me.CboRPO.Column(12) Me.INV_AMOUNT = Me.CboRPO.Column(13) Me.Status = "WIP" Me.StatusID = 2 Me.CboStatus.SetFocus End If End If End Sub
I tried with following code also
'Dim cdn As String 'cdn = "[MQE_NO] = '" & Nz(CboRPO, "") & "'" 'cdn = cdn & " And ENO = " & ENO
'If DCount("*", "T_RPO_Footer", cdn) > 0 Then 'MsgBox "THIS RPO ALREADY ASSIGNED TO THIS FOREMAN", vbOKOnly, "WARNING!!!" 'Me.Undo 'Exit Sub 'Else ‘do something ‘End if
just wondering why randomly certain modules in the form code stop working..
when you accidentally press return.. or do something in the wrong order.. or apply one rule to one control.. and then another control rule stops working..
so you have to delete the code.. right click on the control's event.. and re-insert the code into the event section.. and then it works..
Hi all I have another code Problem, Can someone help me with this. I have two fields One called cust_birthday ( date field ) And one called cust_age ( text field ) The code below I put in a module name age function.
The Problem, is I get no return from this code.
What is wrong with this code ????
Option Compare Database
Public Function Age(cust_birthday As Date, Optional cust_age As Variant) As Integer Dim dteBase As Date, intCurrent As Date, intEstAge As Integer If IsMissing(SpecDate) Then dteBase = Date Else dteBase = SpecDate End If cust_age = DateDiff("yyyy", dteDOB, dteBase) cust_age = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB)) Age = cust_birthday + (dteBase < cust_age) End Function
I have an issue with a code that I have in a form which adds rows in a table as many times as categories chosen from a list. However, the code is not working correctly: it adds the information and creates a row with a category in blank, in addition to the rest of the rows with one of the chosen categories. I would like for this not to happen, to add only as many rows as the categories chosen.
My code is the following:
Private Sub cmdUpdate_Click() Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset Set MyDB = CurrentDb() Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset) MyRS.MoveFirst
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database Public Function ExportAdvanced() Dim strWorksheet As String Dim strWorkSheetPath As String Dim appExcel As Excel.Application Dim sht As Excel.Worksheet Dim wkb As Excel.Workbook Dim Rng As Excel.Range Dim strTable As String Dim strRange As String Dim strSaveName As String Dim strPrompt As String Dim strTitle As String Dim strDefault As String
I have witten a code in the close event of a "Primary" form that would update a Combo boxe on a "secondary" form only if the "secondary" form is open. ie:
Private Sub Form_Close() If Forms!frmEnquiry.Open Then Forms!frmEnquiry!CboCustomer.Requery Else DoCmd.Close End If End Sub
This code keeps giving me an error, is anybody has got any idea why? Thanks in advance.
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 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?
