What is wrong with this query?
=IIf(IsNull([OffPeakDay],IIf(IsNull([OffPeakHour],Sum([Import]-[Export]),0)))
What I want is to sum the field (Import minus Export which are numeric) on the form if the OffPeakDay AND OffPeakHour fields are null.
I have a form called PAF_Assignment and on this form there are many textboxes for editing. After all fields are entered, the user clicks on a button and this button populates the PAF_Issued_Date field with the actual date.
Then I want to disable (enable=False) all fields on this form once there is a date on PAF_Issued_Date as I don't want any user to make any changes after submitting it, although the user would still be able to read the information submitted.
I have tried the following...
PAF_Assignment Form AfterUpdate Event
If Me.PAF_Issued_Date=Not null Then
Me.FieldName1.Enabled=False
Me.FieldName2.Enabled=False
Me.FieldName3.Enabled=False
[Code] ......
However this is not working, there is no error message or anything but the fields remain enabled with the date...
I have several combo box fields in a bound form where they are selecting values from a list (values stored in a separate table) and then loading a number into the bound table field when selected.
How can I put a text prompt in these fields when loading the form which gets removed when focused and of course is not permitted to attempt a save into the bound numeric fields? I've done quite a bit of searching but can only find materials about doing this on bound text fields. I've also seen solutions using Nz which don't seem to work.
Hi friends,
I am new to this forum, and I am facing a problem while learning MS-Access.
I am preparing a database that stores the marks obtained by students in a particular paper in addition to their particulars in tables.
A query is then generated to canculate the aggregate marks and result (Pass/Fail) for the student. It is then presented in a report fomatted as a marksheet, as well as one tabulation chart.
The database is very crude in format, but I am happy with it since I do not know programming at all, and still I could make it.
I have used in built menu of "ms-access" and mouse clicks only for generating query. It was a learn by doing method.
The problem is that my marksheets require absent students to be marked as "ABSENT" or something of that kind. If I convert my Null values to :
Expr2: IIf(IsNull([E_1]),"ABSENT",[E_1])
It shows ABSENT for null value, but refuses to make column total for E_1; On the other hand if I do not enter such expression, it makes column total in tabulation report, but fails to mark "ABSENT" to those who did not appear. So I have to lose one thing to gain another, while I need both.
Can anybody help me at this?
pc
Hi All,
I have a field called PRICE and obviously it is a NUMERIC field as it need to perform calculations such as calculating Total Quantity * Price etc. At the moment the PRICE is inputted manually by the User on a Form and when it has no Price it is simple left blank.
What I wish to include is that on the REPORT when the field is empty it writes the text FOC instead of leaving it empty.
Can you please anyone suggest a way of doing this.
Thanks any help will be much appreciated :o
I have a bound form that is used to enter company info (address, name, category...etc). When the user closes the form, if Company name, province or category are left blank, I warn them with a message box asking if they want to exit and undo changes, or return to the form to fill in the missing info.
When they choose the option of returning to the form, I get 2 errors. You must enter a value in "tblCompanies.category", and "You can't save the record at this time, do you want to exit without saving?". If I click "No" on the second warning, focus is set to the missing data control and I can continue working.
How can I prevent the record from being saved when I choose to return to the form to fill in the blank records?
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Error
Dim Response As Integer
' Determine if required fields are populated.
If IsNull(Me.txtCompanyName) Then
Response = MsgBox("Company name is a required field. Do you wish to discard changes and exit?", vbYesNo,
[Code] ....
The code below always displays opens form even when field being tested is null.
Private Sub Assign_Classes_Click()
On Error GoTo Err_Assign_Classes_Click
Dim stDocName As String
Dim msgboxstring As String
Dim stLinkCriteria As String
Me.name_found.Requery
[code]....
I've been trying to populate a field in a form with information, but only if the field is blank.
What I am trying to do is this.
I have a continuous form displaying all of our principals, however when our principals are on leave I want to populate the field with the "acting principal". I have set it up so the field grabs the acting principal first but it is blank for several schools. For these schools where there there is no "acting principal" I want it to grab the substantive principal automatically and populate the field.
I have tried Is Null, Is Not Null, NZ, IF, Where and Then statements but to no avail.
I have a subform with continuous records. One of the fields in the recordset of the subform is a field named "Remarks". This field does not need to be visible on the continuous subform as it is rare that this field will have any entries.
I plan to apply conditional formatting on another field (IDcardNo) in the record line of the continuous subform so that when field "Remarks" contains any data it will show as a different format on the field IDcardNo.
I would like to make a small form appear when one points to the IDcardNo field with the different format, so that the data in the field "Remarks" pops up when one points to the field IDcardNo with altered format, showing that there is data in the field "Remarks".
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
how to control the numeric field range..e.g. number 00.01< and > 99.99...I am looking for some simply code under On Change() event...how I can restrict the field should only take input number no character or special characters.
View 5 Replies View RelatedI have a form with a textbox, where one inputs a number and then I run a query with the form criteria. The query is
Code:
Select Blah blah from dbo_temp where A>textbox value OR B>textbox value OR C>textbox value OR D>textbox value
The columns which are linked to the textbox some times contain null values. So, A, B, C, D columns do contain null values.
The expression in the "Criteria" column of Columns A, B, C, D (all on different lines - to make sure OR criteria is fullfilled) is as follows
Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),-100,[Forms]![MainForm]![Criteria])
I've put in -100 as an arbitrary never possible number. Obviously, this does not return Null values.
Question: How can I return both Null & Numbers when the Textbox in the form is left blank? in all the columns. Currently, I am not getting Null values
Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),SHOW ME EVERYTHING INCLUDING NULL VALUES & NON-NULL NUMBERS,[Forms]![Material Finder]![txtPS])
or in other words
If the textbox is blank, show me all the data available, else if it is not blank then show me only the values that are greater than the number entered in the textbox from within column A, B, C, D ....
I have a table (Table 1) with a field (F1) that has a numeric value. The data in this table is imported trough an excel and is going to be updated regularly.
F1 consists of a four digit number (1234) but if the first one, two or three numbers are a zero (ie. 0033) Access only display "33". I have solved this in Querys by using: Format([Table 1.[F1];"0000").How do i use Format([Table 1.[F1];"0000") or similar when i display data from Table 1 -> F1 in a form?
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'm working on a form that I want to insert a plus button/minus button.I am aware of the vba code numbers 107 and 109 if you want to use the actual keys but I would rather use a button.
I made a button and here is the code I used now. This code is numeric box specific. So it only adds one to the MileageStart numeric field. (Additionally you can put a minus in to subtract one).
Private Sub Command153_Click()
MileageStart = MileageStart + 1
End Sub
Instead of having a bunch of these buttons all over boxes on my form is there some code that I can use to "select" a text box and just have the Plus/Minus button on the form header that I would hit and it would add or subtract (and then I could go to another box select it and hit Plus/Minus and so forth).
The following works when I am searching a form on a value in a sub-form where the value is text
Private Sub find_boats_by_enq_no_Click()
Dim stDocName As String
Dim strSQL As String
Dim trz As String
trz = InputBox("Enquiry number??")
stDocName = "find_boats_by_enq_no"
strSQL = "SELECT * FROM boats INNER JOIN prices ON boats.[boats-prices#] = prices.[boats-prices#] " _
& "WHERE prices.[stn-enquiry-number-1]= '" & trz & "'"
Me.RecordSource = strSQL
End Sub
What changes do I need to make if "prices.[stn-enquiry-number-1]" is a number rather than text. I'm thinking I dont require some of the "dims" and I'm not sure if I don't need to do something with the quote marks...
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
View 14 Replies View RelatedI 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?
I want to calculate the average of 16 anodes from text box into the "average drop" box and simultaneously want the data to be saved in the table too as one of the fields. Also, How to load form view while the database loads?
View 10 Replies View RelatedI have a table form (I want to have many records available in the screen)
I want to disable fields based on a value of another (main) field.
The fields are check boxes, so the automate formatting is not available.
I use the VBA code below:
Select Case name1
Case "POINT"
Me.sf1.Enabled = False:
Case "SIDE OUT"
Me.sf2.Enabled = False
Case Else
Me.sf3.Enabled = False
End Select
but the fields become disable to all records, not only to the records under the right condition. Why?
I have a Customers table and an Orders table. My Customers table is a bit different than the norm because I couldn't figure out any other way to do this.
My client's customers are either businesses (companies) with a contact person, or individual customers. So, in the Customers table, I have the following fields (there are others but do not apply to my problem):
Customer ID
Company
Contact
Customer
So, if it is a company the data entry person would enter the company name and the full name (EX: Doe, John) in the contact field, but if it is an individual customer, then they would leave Company and Contact blank and enter just the Customer name (Ex: Doe, Jane).
So, in the Orders table, I have combo boxes for look ups for the fields Company and Customer.
That works okay, but I would really like for the Customer ID to post into a text box once the Company or Customer has been selected. In other words, the input person would enter the name, then it would show that Customer's ID #.
The table ProductInfo has an ItemID, and its corresponding Quantity.The table Orders has a PO column, an Item# column, a OrderQuantity column, and the column Item#CurrentStock which holds the Quantity of the selected item, for 3 items.I have created a form that has a ComboBox populated with the ItemID's from ProductInfo, that when selected populate the corresponding Item# in the Orders table.
I would love if the Quantity(stock) from ProductInfo would automatically fill the Item#CurrentStock field on the form when the ItemID is selected from the ComboBox.But all of the fields only relate to Item# from the first ComboBox!?!?I have tried =dlookup,
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies View RelatedI am wanting to grey out/disable a field on a form based on entries in two other fields being the same. One of these fields that contains the data to be referenced in on a sub-form.The form is called 'Payment_Information-Form', the field to be greyed out/disabled is called 'SUF A Paid?'
The fields that are being referenced are: 'Set up fee in s (SUF) Recrt Restriction A <=' which is on the main form, and 'SUF_Letter' which is on a sub-form (within the main form). The sub-form is called 'Costings_11_Previously_Paid_SUF subform'.If 'Set up fee in s (SUF) Recrt Restriction A <=' equals 'SUF_Letter' then grey out.I tried the following formula in both the On Current and After Update sections but it hasn't work.
=IIf([Forms]![Payment_Information-Form]![Costings_11_Previously_Paid_SUF subform].[Form]![SUF_Letter]=[Set up fee in s (SUF) Recrt Restriction A <=],[SUF A Paid?].[Visible]=True,[SUF A Paid?].[Visible]=False)
We still use Visual dBase 5.6 for most all of our database operations. We were considering the change to Access 2007.
I was wondering about numeric fields. There are like four different options of numeric fields. Which would I choose for just a plain number field? Also how do I set a fixed field width?
Thanks for any help. I hope this is in the correct spot.
Bob
I have an address box that I would like to auto populate with a double click event. Basically, if the location address is the same as the main company address, and there isn't any data already in the location field, then I would like the user to be able to just double click the location address and the field gets automatically populated with the main company address.
Private Sub Address_DblClick(Cancel As Integer)
If IsNull(Me.Address) Then
Me!Address = Me.CompanyName.Column(1)
End If
So this works fine if the main company address actually has an address in it. But if it doesn't then double clicking the location address produces a run time error 3315 for trying to populate a null value.