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?
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:
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).
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've a button that position the table on a new record:
DoCmd.GoToRecord , , acNewRec
And I create a button to cancel that action:
Me.Undo
But the code field (autoincrement) continues autoincrementing! Explaning:
1. I click on "Add" button 2. The table's positioned on autoincrement code #1 3. I click on "Cancel" button 4. The register is cancelled, it isn't includded on table 5. I click on "Add" button 6. The table's positioned on autoincrement code #2 and the #1 isn't exists...
Hai guys Iam looking out for a sample DB for employees leave management. # With leave application form # Leave approval form # Leave status # All with username and password
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 have an unbound text box that displays when certain criteria exits, which I placed in the Form's properties, "On Current" ...
If [Decor6Date] <= Date - 30 And [NonrecommendQuestion] = 0 And IsNull([DateDecReceived]) Then
[LateDecoration2] = "Late Decoration"
Else
[LateDecoration2] = ""
End If
I then made a timer event to show/flash the message
Me!LateDecoration2.Visible = Not (LateDecoration2.Visible)
Everything works fine ... the problem is the message will only flash if I exit the record then return to the record. How can I fix that problem? I tried Refresh ... no luck.
I have a database with shifts in for staff. They have a bunch of times in and times out over a four week period. I have gotten an average weekly amount of hours for each staff member based on this but I need another equasion to work out their leave entitlement. It breaks down like this...
Average weekly hours x 5.6 x number of days working in this period (ie start date and end of financial year day count) divided by number of days in the financial year (ie 1/4/2015 - 31/3/2016 day count)
I'm just wondering of a way to do the day count based on me keying in the start date of the staff member (default 1/4/2015) and that access can work out the days in that financial year left and the actual days in that financial year.
It sounds simple enough but I want to get it to automate based on my start date.
I am trying to create a database to record employee sick leave and to indicate when an employee has surpass they allotted sick leave yearly.
I have the following tables and attributes
Employee Table ID#, FName, MInitial, LName
Status Table Category Allocation
Transaction Table EntryDate StartDate
EndDate TotalDays Certify? DaysBalance
Category indicates if a person is Temporary, Appointed or Substitute and Allocation indicates how many sick days is allocated per category yearly. I place both in the same table so when the status is selected the allocation is autofill
I am thinking of creating a form with the Employee Table info at top and a subform with the status and the transaction information.
I would like when a new year begins the sick days not used is discarded and a new allocation of days per employee is generated also when an employees status is change how to deal with that. When an employee used all of there sick days is there a way to have all those names display when you open the database.
Trying to modify this calendar database to track employee leave. I need the calendar form (frmCalendar) to show all employee leave on the calendar using the "Show All" command button on click event. This works if I specify a uID (UserID from employee table) in the code, but only for that specific employee. I need all employees at the same time up on the calendar so I can see if there is more than one employee off on any given day.
Events or leave is populated on the form through "Private Sub DisplayEvents" and the mdlCalendar module.
I inadvertently deleted my previous thread. Sargeant reply to my previous thread recommending using the 'Union' statement. I did and below are the results. Below is what I used. Please advise. Thanks again ! ! !
SELECT Table1.Column1, Table1.Column2, "" FROM Table1 UNION SELECT Table2.Column1, "", Table2.Column3 FROM Table2;
I have a form with an image embedded in it. I want to be able to click on the image and leave a "dot" or "mark" where i have clicked, allowing me to click in multiple places on the image and leave multiple "dots"
I am assuming i need to crate a new shape each time i click, how is this done?
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"
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
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.
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.
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;
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.
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,
