I have created a form to select criteria for a table query (which ultimately will be used in a report). If a field in the form is left blank, how can I cause the query to not filter on that field?
For example, the form contains fields for Customer, Date Range, Item Number, and Sales Rep. If the user wants the report to include all sales reps how can the query criteria for this field be omitted or nullified when the query is run?
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.
Database query. I need the query to count the records of a field and display a number for the records of the field. For instance, one field is [Genre] and the other is [Show]. The query needs to list the Genres along with the number of shows for each genre. I've been able to just use the query design and add the genre field and I can add the show field, use totals count which gives me the genre counts the number of shows. My problem is the null. Some genres don't have a show listed so the genre doesn't even show up in the result. If I could get the the genres that have null shows to result a 0 it would be perfect.
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.
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?
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.
I have a downtime database that tracks units down, time on, reason down, etc. When a unit goes down, I log the name of the unit and the time down in separate fields. When a unit goes back on line, I log the time on and the reason in separate fields. All this is in a form. When the unit goes on line, I want the user to be forced to enter a reason only after he has entered a time on, not before. The “reason” field must be left blank until the unit is on. How do I do that? I have searched the forum for this and have not found leaving a field blank based on another field’s data. I will supply whatever you need to help me. Thank you.
I'm sure this one is easy, but I can't seem to get the code to work right. I have a field called "Discharged" and "ProposedDischargeDate". What I want to do is if the "Discharged" field is empty, or null, I want the "ProposedDischargeDate" to be visible. Otherwise, I want it to remain hidden. I want this on either the AfterUpdate or the OnExit event of the "Discharged" field. Here's what I've tried:
If [Discharged].Value = "" Then [ProposedDischargeDate].Visible = False End If
or
' if the field is empty, show the proposed discharge date field If Me.Discharged = null then Me.ProposedDischargeDate.visible = true ' if the field is not empty, hide the diagnosis field If Me.Discharged = true then Me.ProposedDischargedDate.visible = false
But this doesn't seem to work. What am I doing wrong? Thanks for any help.
I am working on a school project and am stuck on the last part. I need to produce a query for use as a report. The fields in the query are:
Building Teacher 1 Teacher 2 Teacher 3 Teacher 4
Archive 1 Archive 2 Archive 3 Archive 4
"building" is the search parameter for the query
I need to produce a list of names of teachers, if there is nothing entered into the corresponding archive number e.g. no data in archive 1 and 3 , so show teacher 1 and 3.
I have a form with field [Status]. When [Status] is set to "Will Attend" I can input the country of birth in field [Country_Birth]. My question is there something where if [Status] field is set back to null field [Country_Birth] will automatically clear?
I would like a MsgBox to pop up for the user if the Priority field is blank and the box count is not. I tried doing this on the report but the report will just not run. So I considered adding a module to the query.
However, I get a Data type mismatch. Below is the module and query. Can someone help me with this?
Function ErrorPriorityReport(ByVal BackPriority As Integer, ByVal BackBoxes As Integer, ByVal Priority As Integer, ByVal SumOfBoxes As Integer) As String If BackPriority Is Null And BackBoxes <> Null Then MsgBox "Report will be inaccurate! There are blank priorities. Please run report on Customer Menu!", vbOKOnly, "Missing Priority" End If
If Priority Is Null And SumOfBoxes <> Null Then MsgBox "Report will be inaccurate! There are blank priorities. Please run report on Customer Menu!", vbOKOnly, "Missing Priority" End If End Function
Here is the field in the query calling the module: ErrorCheck: ErrorPriorityReport([BackPriority],[BackBoxes],[Priority],[SumofBoxes])
good morning all, I am using the afterupdate event on this forms field. If this date field is NOT EMPTY, then i want the next field to be visible. Here is the code i have
If Me.quotedate = ????????? Then Me.quotehow.Visible = True Else Me.quotehow.Visible = False End If
I have done this with text fields where i have a defined data to be = to, but for a date field what do i put in ?????????? to show is not null?
I've got 2 different lines on a form linked to a table. One of the lines is claim status with either open or closed as the options. The 2nd field is "Date claim closed". I want to require the date to be filled in if "closed" in the status field. Can this be done? If so, what do I need to do?
i am trying to make a query which will give my the result as if Sum of filed is not null.. Criteria 1) (Field Name)in Category "Asset ID Diff" And "Cusip Change" should select first then
2) Fund and Amt
if Fund Amt is not zero than data will comes in query
CategoryFundAmt Asset ID DiffADF01000 Asset ID DiffADF0-1000 Cusip ChangeADGH2000 Cusip ChangeADGH-2000 Asset ID DiffADF05000
in above data you can see ADF0 funds total is 5000 which is not null or 0.so query will give me the result as.Asset ID DiffADF05000
I have a form to enter attendance in that pulls students from a StudentEnrollmentTable based on FacultyName which is selected from a combobox that runs the following code:
Private Sub cboInstructorName_Click() Me.Requery End Sub
A query (StudentAttendanceBYFaculty) is run each time a faculty name is selected. On the form their is a field name TempClassesAttended which is bound to a field of the same name in the StudentEnrollmentTable. Teachers will enter attendance data and run an append query to append the current form records to the StudentAttendanceTable. Each time the form is repopulated the most recent TempClassesAttended values are pulled into the form. This is what is expected.
Now I want to load Null values into the TempAttendance field on the form each time the Faculty selects their name and runs the event. I looked at code online and it seems easy enough, but I don't know enough to make it work. This is the code suggested:
UPDATE TableName SET FieldName = Null OR UPDATE MyTable SET MyField = Null
how to include this into the current event so that the event will return the faculty records with Null values in the TempAttendence field.
I need to add a Yes/No field to a table. I know I can do this in design view. Next I want to loop through the table and set it to Yes if certain fields are null. What I need help with is the VBA looping part and setting the boolean field to Yes if the fields being checked are null.
SysID PersonData " " " " AddressData " " " " " " " " " TelephoneData " " " " Year (this is 1 or 2)
I'm taking each component Person, Address, Telephone info and making a compacted table of each (i.e. lots of Jane Does etc, appear in both years) Each table with have a URN
I then need to update the big table with the URN so move onto another part of getting this data into a usable format.
Basically what follows is my question on how to get the URN's back into the table:
Is there a quicker way (or a way I'm not seeing) of doing the following.
I have a set of data 'tblDatabaseTable, say:
ID Field1 Field2 Field3 Field4
In the database.
I have an imported file which will be tblImportTable, same deal without the ID:
Field1 Field2 Field3 Field4
I need to compare the data on each table:
SELECT * FROM tblDatabaseTable INNER JOIN tblDatabaseTable ON tblDatabaseTable.Field1 = tblImportTable.Field1 AND tblDatabaseTable.Field2 = tblImportTable.Field2 AND tblDatabaseTable.Field3 = tblImportTable.Field3;
Works great as long as all the fields have values in.
Is there a way to compare the tables and get results as:
Database Ian MacConnell null
Import Ian Macconnell null
Match!
without doing something like:
SELECT IIf(IsNull([Field1]),' ',[Field1]), IIf(IsNull([Field2]),' ',[Field2]), IIf(IsNull([Field3]),' ',[Field3]) FROM DatabaseTable
SELECT IIf(IsNull([Field1]),' ',[Field1]), IIf(IsNull([Field2]),' ',[Field2]), IIf(IsNull([Field3]),' ',[Field3]) FROM ImportTable
before comparing them???
Phew, hope that all makes sense, very long day and I'm........
I have the query below and it returns the number of cases for eache of the case status (open, closed or private) Some of the cases have no status, the field is empty. Is there a way to count the number of cases which have no status in the status field?
Can someone tell me how to do this?
SELECT Count([Report table part one].CaseStatus) AS CASES, [Report table part one].CaseStatus AS STATUS
First off, I want to see if this is possible. It seeems like it should be but sometimes I dream bigger then things allow.
OK... I have a form made in Access. There is a text field that I would like to be able to do the following:
If the field is null, a number will be randomly generated betwen teh values of 1 and 1500. If the field is not null (we entered a number between 1 and 1500), then that field would be set to that number. The field CANNOT have the same number given (we will only have 1500 records and want them randomly generated a number unless otherwise specified).
Can this be done? I made my attempt but it keeps failing no matter how I try and fix and such. I guess this is what happend when you haven't used Visual Basic in about 4 years.
So I went searching through the forums and found a thread that provided coding for getting the # of years and months from two dates:
Function fAgeYM(StartDate As Date, EndDate As Date) As String 'Purpose: Returns the difference between StartDate and Date in full years and months 'To call: ' ? fAgeYM(#1/21/04#, #1/19/06#) 'Returns: ' 1 years 11 months
fAgeYM = Int(intHold / 12) & " years " & intHold Mod 12 & " months "
End Function ------------------------------------------
That's perfect for what I'm wanting but I'm doing it with Hire/Term dates. So I want to be able to see how long past and present employees have worked in the company. Obviously that makes the Term field Null at times. With the coding above it requires a date to be in the EndDate field. How can I change it to allow for a null field in which the field would essentially be the current day's date? [Now()]
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.
