Code:
Public Function DegreeChecker(AGShrs As Integer, ASBAhrs As Integer, ASCJhrs As Integer, AAhrs As Integer) As String
Dim Degree As String
If ((AGShrs = 0) And (ASBAhrs = 0)) Then
Degree = "ASBA"
ElseIf ((AGShrs = 0) And (ASCJhrs = 0)) Then
Degree = "ASCJ"
[code]...
The problem is, the information that is populating the query which is running this code may not be there. As in some of the fields, AA, AGS, ASBA, and ASCJ might be left blank. Unless these four fields are populated by something, it returns #Error instead of the specified number.Is there a way to say, If variable is blank, ignore and continue on to check if the next thing is 0, or if none are 0, state Potential.The information in this query is to be exported out to excel to be used as a Mail Merge file.
They are all numerical values that I need to format into percentages however Field3 could contain zero so I can't replace Null with zero. I simply want to return nothing if Field1 is equal to Field2.
I've set up a simple query that returns 6 columns of data.This query then shows on a sub-form elsewhere in my DB.The problem is that the query always appears with a horizontal slider. Allowing the user to scroll across to see the other columns in the query...The problem here is that there ARE no other columns of data. They are just empty. I want to restrict the output of this query to ONLY show the 6 columns that i have specified.
I have tried deleting the columns in Query Designer, then save the Query. But every time i re-open it half a dozen or so blank columns are stuck on the end.
I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.
This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code: Private Sub Command18_Click() On Error GoTo errorcatch Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True Exit Sub errorcatch: MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code: SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact FROM tbl_Contacts WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company])) ORDER BY tbl_Contacts.ContactForename;
Hello, Run-time error ... No value given one or more required paramete
UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");
basically i need to create an update query to update the OptionCombo field where: 1. OptionCategory=BODY 2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion) 3. For each InvoiceNumber AND GuitarItem.
NOTE: some invoices will not have any BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.
How should I rewrite the SQL to achieve this without getting this error?
I have a left-join query where I know that some of the values in the left-hand table have no corresponding values in the right-hand table...that's why I used a left-join: I want all the values in the left-hand table to show up. I would expect that records without corresponding values in the right-hand table would show up as nulls (I swear I have dozens of other queries that work this way) but in this one, they show up as "#error" instead.
This is a problem because I want to do some calculations on this field. Normally I would use the nz function to change the nulls to zeros.
So, 1. Any idea why this is happening? 2. Is there a way to change the #error values to zeroes, similar to using the nz function?
HEre's the SQL: SELECT FeederInventorySummary.machine, FeederInventorySummary.Size, FeederInventorySummary.Leaf, FeederInventorySummary.Type, FeederInventorySummary.CountOfFeederID,FeederNeeds Summary_7.FeederQty FROM FeederInventorySummary LEFT JOIN FeederNeedsSummary_7 ON (FeederInventorySummary.Type = FeederNeedsSummary_7.Type) AND (FeederInventorySummary.Leaf = FeederNeedsSummary_7.Leaf) AND (FeederInventorySummary.Size = FeederNeedsSummary_7.FeederSize) AND (FeederInventorySummary.machine = FeederNeedsSummary_7.ActualMachine);
For every record without a corresponding record in FeederInventorySummary_7, the FeederQty field shows up
I have a table with a list of Students. While the academic year is in progress those students are in an "Active" status. Once the year is finished those students are moved into (hopefully) a "Passed" status or some other status that is not considered "Active".
Throughout the rest of the database I have many forms that refer to the Student Listing. As an example we have a Test so we select the Student performing the test using a Drop Down List and go from there.
I have set search criteria to the Drop Down List via the Combo Box's Query to only display active students and it works fine during the Academic Year. However if I have to review a test from a previous year then the Student Drop Down List no longer displays the name but their ID# (Primary Key) because the Student's status has now changed and no longer meets the query criteria.
Is there a way to use the Filter/Search Criteria to chose from a listing of Active Students but once selected it will display the Student name regardless of their status?
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code: Dim excelApp As Object Set excelApp = CreateObject("Excel.Application") excelApp.screenupdating = False excelApp.Visible = False Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
I have a small bug here which I can't seem to find a way round.
I have a subform setup to show Engineers assigned to a call. In the subform header I have a Command to add new Engineers. When this is pressed the form is changed to a Data Entry form to allow additions. This all works perfectly where there is an existing engineer on the Database.
The problem is with calls where there is no current engineer assigned.
With these calls I get an error popup saying "No Current Record" before the Data Entry screen comes up. Once the Data Entry screen comes up then an ID is assigned correctly and you can input fine so I am really just looking to see if there is a way to suppress the No Current Record popup.
I am getting the following error when I run my report:
"The Object type argument for the action or method is blank or invalid"
I am running the following code from a report.
Thanks
Private Sub Report_Open(Cancel As Integer) Dim intRetValue As Integer intRetValue = MsgBox("CREATE EXCEL SPREADSHEET?", vbYesNo)
cswSetReportOptions MsgBox "cswSetReportOptions complete" 'mac - new record source to filter blank company name Me.RecordSource = "SELECT * FROM qry2 WHERE strCompanyName <> """" "
If intRetValue = vbYes Then MsgBox "Creating excel spreadsheet" DoCmd.OutputTo acOutputReport, , acFormatXLS, "Book1.xls", True DoCmd.Close End If MsgBox "Creating Access Report" End Sub
Hi. I'm unable to work out how to set up a query which will filter records according to what is returned by one control, but will also list all records if the value of the control is null.
Situation: I have a CONSULTATION table linked many to many via a CONSULTATIONLIST relation table to an ADVISOR table. (An Advisor may be present in any number of Consultations, and a Consultation may include any number of Advisors.)
I have a CONSULTATIONMANAGE form enabling the user to select an Advisor from the table ADVISOR via a combo box called SELECTADVISOR which returns Advisor ID (adID).
My CONSULTATION form, displaying the list of consultation records, currently has, as record source, the following query:
SELECT Consultation.*, Advisor.adID FROM Consultation INNER JOIN (Advisor INNER JOIN ConAdList ON Advisor.adID = ConAdList.adID) ON Consultation.conID = ConAdList.conID WHERE (((Advisor.adID)=[Forms]![ConsultationManage]![selectadvisor]));
This query correctly lists only those Consultations in which the selected Advisor was present.
Problem: What I also need to do is show ALL Consultation records if the user does not select an Advisor from the combo box.
I am using Access 2007. Second, I am using two tables, Inventory and Service Request. Inventory is a list all the Inventory, with has things like location (building & room number), type (desktop, laptop, etc.) and access tag number. Service Request also has Access Tag Number (should link back to Inventory) & date of reported/resolved problem and description of problem/solution.
On my form for Service Request, I have the access tag number as a fill text box, when you double click on that text box, it runs a Query that asks for the 1) Building, 2) Room Number & 3) Type . . . Query currently opens in a the spread sheet view that shows those three things but also the Access Tag Number associated to them.
I would like that Access Tag Number to just return into the field that was double click to start the query.
I have a date in "date to engineering" of 13/ 01/2010 but I am not getting a value in my field which should be 1479 my field is just always returning an empty field
Code: =IIf(IsDate([date to engineering]),"",IIf(IsDate([date from engineering]),Now()-[date to engineering],[date from engineering]-[date to engineering]))
So I have built a form and added a field using the expression builder which is returning #Name? There is a relationship between the table on which the form is based and the record I am adding to a control.
I have a numeric field in a form which initially is set to 0. When the user clicks or tabs to this field, it is blanked out in the got focus event by setting the field to "". This works great. Now, if the user exits the field by clicking elsewhere or tabing out of it, without entering a value, I would like the 0 value to be inserted again. I have tried many things to make this work without any success. Is there someone who knows the right way to do this?
What I need to do sounds simple but I am losing my what hair I have left to do it. I have a single table where staff rotate a responsibility every 3 months or so. What I have in one field is the surname of the person currently performing the duty and another with the surname of the person who is next to take on the duty. What I need to query is those posts where a replacement has not been identified for the next say 12 weeks.
e.g. I need to have a query that finds extracts all posts where the field (surname) of the person taking on the role is blank between (date) and (date).
I am running some code to work on a table and I want to blank a date field. It is a stock control system where when a vessel is emptied I would like the updated field to become blank as it is empty and therefore has no history (this is stored in another table.)
The code I have tried to us is; (where updated is a date/time field)
Else rst![Updated] = " " rst![Volume] = "0" End If rst.Update rst.Close
The message I am getting is 'Data type conversion error'.
I know this is a simple question. I searched but could not find what I was looking for. Or it is handled in differently than what I am thinking. I have a field where I enter a dollar amount. But if I do not enter a dollar amount the field displays $0.00. How can I make this field blank if no dollar amount is entered.
I have a form in access 2010 that prints a document rougly 500 pages in length. I want to know if there is a way to tell the form not to print a particular page if one of the fields on the page returns the word "suspended" and or the field is blank. The page has other data on it, as do they all, but if this particular field comes back null or "suspended" I would rather it just did not print that particular page in its entirety.
