I am creating a query to scrub out certain records from a table. How would I exclude records where BOTH the "Phone" and "Email" fields are null? I do not want to exclude them if "Phone" field is not null but the "Email" field is, or vice versa.
I currently have tried WHERE ((([RAWCancels]![Phone number] And [RAWCancels]![Email]) Is Not Null)), but it is not working.how to write the criteria.
If I have about 9 Text fields, How would I go about checking to see if all of the text fields are empty, then hide all the fields, but if any of them have anything in it, show them all. Here is my code, but it doesn't work:
Code:If SpecAgent = "" And SpecArea = "" And SpecBenefit = "" And SpecCompany = "" And SpecCSR = "" And SpecDoctor = "" And SpecHospital = "" And SpecPlan = "" And SpecRx = "" ThentglNo = TruetglYes = FalselblSpecAgent.Visible = FalseSpecAgent.Visible = FalselblSpecArea.Visible = FalseSpecArea.Visible = FalselblSpecBenefit.Visible = FalseSpecBenefit.Visible = FalselblSpecCompany.Visible = FalseSpecCompany.Visible = FalselblSpecCSR.Visible = FalseSpecCSR.Visible = FalselblSpecDoctor.Visible = FalseSpecDoctor.Visible = FalselblSpecHospital.Visible = FalseSpecHospital.Visible = FalselblSpecPlan.Visible = FalseSpecPlan.Visible = FalselblSpecRx.Visible = FalseSpecRx.Visible = FalseElsetglNo = FalsetglYes = TruelblSpecAgent.Visible = TrueSpecAgent.Visible = TruelblSpecArea.Visible = TrueSpecArea.Visible = TruelblSpecBenefit.Visible = TrueSpecBenefit.Visible = TruelblSpecCompany.Visible = TrueSpecCompany.Visible = TruelblSpecCSR.Visible = TrueSpecCSR.Visible = TruelblSpecDoctor.Visible = TrueSpecDoctor.Visible = TruelblSpecHospital.Visible = TrueSpecHospital.Visible = TruelblSpecPlan.Visible = TrueSpecPlan.Visible = TruelblSpecRx.Visible = TrueSpecRx.Visible = TrueEnd IfEnd Sub
Hello, I am reposting a question submitted some time ago to which I did not get much help, hoping for additional advice.
My database has two tables. Table1 and Table2. Table2 is a copy of Table1. Both tables have 50 fields. Data is imported into Table1 once a week. Before importing data I run a delete query which removes all data. Data imported adds records only to the following fields: FNAME, LNAME, SSN. This means that I am left with 47 empty fields.
Users can see data in Table1 using Form1. They will see the FNAME, LNAME and SSN. These fields are locked and cannot be edit or deleted. All the other 47 fields are blank and user can add data to them.
Currently I am using two buttons to:
1) Close the form (with the code Me.Undo, Me. CLose)
2) Run and append query to Table2 and deleting all added data.
I would like to combine these two functions in one button which would:
If no data has been entered in the remaining 40 fields, than close the form.
If data has been entered it should run the following procedure (Append data into Table2):
Dim MyDb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String
Set MyDb = CurrentDb
Set MyRs = MyDb.OpenRecordset("Table1")
stDocName = "MyForm"
stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.SetWarnings False
DoCmd.OpenQuery ("AppendtoTable2")
DoCmd.Close
The problem I am having is declaring all the 47 empty records. Any suggestion or code? Thanks.
Does anyone know a nicer way of writing this. I basically want the form to quickly check all the fields to make sure they are filled in when the print button is clicked, different fields need to be filled in for different print buttons so i used this which works but seems very messy.
Private Sub Command0_Click()
If IsNull(reg) Then
MsgBox "You must enter Reg", vbCritical, "Data Required"
Else
If IsNull(make) Then
MsgBox "You must enter Make", vbCritical, "Data Required"
Else
If IsNull(model) Then
MsgBox "You must enter Model", vbCritical, "Data Required"
End If
End If
Exit Sub
End If
End Sub
Hi,
I have 2 tables... one with 'answers' (on questions) and one with these 'questions'.
Now I want to show the questions with their answers on a rapport
(based on a query) .. but only these whereby the answer-field is NOT NULL..
So I have already:
ansques1: IIf([answers.1] Is Null;Null;[questions.1]+" "+[questions.1])
But now if the answer-field is NULL, then the ansques-field is also NULL...
I want if the answer-field is NULL, to have no new field.. like ansques1.. just want to leave it out of the query... can someone help me with this?
Thanks in advance,
Ankie
I'm working on a db logging replies to a questionnaire. Judging by the responses I am receiving it appears that some sections are not completed at all. I need to be able to include these blank responses when it comes to analysis.
My design splits the questionnaire into sections, each section has a data entry form with its own underlying table. Each table has a primary key (autonumber field) which relates to each organisation that has replied.
If an organisation has failed to complete a section, I still need to create a new record in that section(table), triggering the autonumber field, hence referring back to the organisation.
I know I haven't explained this very well, but if anyone can make sense of what I'm saying and can give me any suggestions on how to make this happen, I would be most grateful.
In anticipation of your replies
Ride
I have a main form with multiple combo boxes that filter a subform datasheet. In my main form combo box [PR_Filter] I added a selection titled "<Blanks>". When I select "<Blanks>", I want it to filter my subform field [PR] for NULL values. If I select anything else I want it to filter on that selection. I cannot get the NULL filter to work.
Here is the code that I have (Red is the field I need the NULL values):
Private Function PurchaseFilter()
Dim strFilter As String
Dim bFilter As Boolean
bFilter = False
strFilter = ""
[Code] ....
I have a search form that has 8 criteria fields, of which the user can fill (from combo boxes and txt boxes) any number of them to narrow or widen the search.This by the way works perfectly. What I want to have is a message box pop up if the user does not enter any criteria and click search.
I tried stringing multiple if isnull statements together but realised that because I have an onload function that sets all fields to "" the isnull function won't work, so have changed this to the Len function.But when I run it, it just skips straight past the len line and opens the search detail form regardless. I suspect I have the order of things wrong, as it does not throw up any errors when run.
Code:
Private Sub Form_Load()
Me.PCNumberIn.Value = ""
Me.SerialNumberIn.Value = ""
Me.DeviceTypeIn.Value = ""
Me.DeviceModelIn.Value = ""
[code]...
I have a table that has several fields that contain a date if a user is licenced to use a certain piece of equipment and is null if not. Each recor has other data in it such as name, and a unique identifier (numeric). What I want to do is make a list of the fields which have a date in them to create a licence for what they can operate. Any help on which way to go...pretty new at access.
View 14 Replies View RelatedHi,
i have created a query from just one table, which is basically a list of Financial transactions.
There is a field called supplier code, in this query.
Most of the transactions have a supplier code, but there are a few that don't.
i've got another table that has the supplier name against the supplier code.
i want to bring this table into the query, so that i can show the supplier names against those records in the query that have a supplier code.
when i add this table and link the 2 tables up and put the supplier name into the query. The query excludes the records with no supplier code.
This is presumably, because they contain null values.
I've been reading about the Nz function and i think this is what i need to use.
i've tried this, but it still only brings through the records with a supplier code and not all of them.
How i've done this is to go into the design mode of the query and in the column that would have null values i have click "build"
in here i have the following formula.
Nz([Supplier Name],"No")
Am i doing anything wrong?
how to run a report to show a Client if one or of the following fields are blank or unchecked(Yes/NO?
Fields
Client
Assessment Date(Null)
Placement Fee Paid(Check Box)(No)
TJ Completed(Check Box)(No)
So basically, if one of the above fields are null or unchecked, please show in the report. I'm sure it's easy but to do, but not up on coding.
I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses]
FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
Calculation : In the event the null, is will become 0.
OT Qty: Nz([Qty(Overtime)-Nz([Qty(Work Hour)])
Is work to calculated the OT amount. How to set the value become zero in the event the the Qty(Overtime) was null.
My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:
RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")
would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).
I have a large amount of field data that was taken by a data logger. Specifically, The datalogger has five temperature probes embedded in soil, and takes a measurement automatically once every hour for each probe. So the table has a Time field, and then a Temp1, Temp2,... Temp5 field. I need to be able to average the temperature fields. This within itself is easy enough, I just tell the query to find (Temp1+Temp2+...+Temp5)/5. However, unfortunately, because it is field data is is very common for one (or more) of the ports to be malfunctioning at any one measurement time for a number of reasons.
If, for instance, Port one does not have a value, then the formula I entered will not work, as it can't add a null value to numbers, and otherwise It would be dividing by five whereas there were only four values to average.
Hi all. I want to check for not null value constraint for all tables in database . I tried the following but when i run it i get the following error:
Compile error:
Invalid Next control variable reference
I be happy if some one one help me fix this error.if i remove the bold lines the program workes well but it does not put not null for table fileds that requries value.
picture of the output without the bold part
http://i5.photobucket.com/albums/y1...007/notnull.jpg
Code:Option Compare Database''This module displays field name and type in a massage boxFunction ShowFields(pTable As String) As StringDim db As DatabaseDim rs As RecordsetDim I As Integer''Dim j As IntegerDim n As Integer''Dim NL As StringDim strHold As String, ST As String''Dim x As Variant''NL = Chr(13) & Chr(10) ' Define newline.Set db = CurrentDbSet rs = db.OpenRecordset(pTable)n = rs.Fields.CountReDim x(n - 1, 1) As String'''ST = "Create Table " & pTable & vbCrLf''adding Create table and table name to statementST = "Create Table " & pTable & vbCrLf & "("For I = 0 To (n - 1)For Each fld In tbl.Fields ST = ST & rs.Fields(I).Name & " " & FieldType(rs.Fields(I).Type) & "," & vbCrLf If fld.Required = True Then ST = ST & " NOT NULL" & " " Else ST = ST End IfNext Irs.Closedb.CloseSet db = NothingShowFields = ST '' returns the fields name to main functionEnd Function
on click even code
Code: For Each T In db.TableDefs '''Skip the system tables If Left(T.Name, 4) <> "MSys" Then ''' this line determines the primary key of the table ''' by calling GetPk function from module pk = Left(GetPK(T, db), InStr(1, GetPK(T, db), "<-") - 1) cont = cont & ShowFields(T.Name) & vbCrLf & " primary key " & "(" & pk & ")" & vbCrLf & ")" & vbCrLf End If Next T ''' This line of code post the generated table statment to a massage box MsgBox cont
I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.
What I have tried:
1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.
2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.
3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.
The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.
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] ....
I am trying to create a clean database and code to generate a report.
I am trying to count the number of null fields in one of my queries:
However, because of this expression, I cannot carry other fields with it. So the end result looks like:
But I would really like it to spit out the following information:
Total Not Fixed: 241
Department: Sustaining Eng
is there a way to create an SQL query to simply add data: I have tried the following:
Code:
ALTER TABLE qrySustainingEngNotFixed2 ADD Dept TEXT(25)
Insert Into qrySustainingEngNotFixed2 (Dept) Values (Sustaining Eng)
SELECT TotalNotFixed, Dept
FROM qrySustainingEngNotFixed2;
The above isn't working. Keep in mind that I want this is just for display purposes. I pondered making a custom table and then making a Union Query, but I'm trying to do this all in one SQL statement.
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 want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE,
Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
The CSV data set attached
tHANK YOU
SIMON
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies View RelatedAccess 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.
On a form, I want to disable the save button, 'cmdSave' whilst the form's mandatory fields have been left blank.
I've put in a smart tag, called, 'Req' against each required field and have used the following code on the forms current event.
Code:
Private Sub Form_Current()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
[Code] ....
The save button is disabled, but it won't enable again after each field has data entered against it.
I also have this code in the AfterUpdate event in each required field:
Code:
Private Sub cboErrorID_AfterUpdate()
Call Form_Current
End Sub