Show Field If Another Field Is Null

Mar 5, 2012

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.

View Replies


ADVERTISEMENT

Only Show Ones Have Have Null Value On A Field

Aug 27, 2006

hello,
i have a querie, haves jobs information in it and one field called "Date Finished Fixing"

i want the querie to only show the ones with out dates put in so if they =nothing i don't no how to do it can someone help

View 3 Replies View Related

Reports Show No Field Data If Value Is Null

Oct 6, 2006

I have a question about reports in Access. Is there a way for report fields to be omitted from the report if the database value is null?

For example:

Name: Joe
Last Name: Smith
Company: (Is Null)
website: something.com

would become.....

Name: Joe
Last Name: Smith
Website: something.com


thanks!

View 9 Replies View Related

Field Criteria: Is Null; There Are Null Values In That Field; No Records Are Returned

Nov 16, 2007

I 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?

View 10 Replies View Related

Queries :: Form Field Return A Null Then Look At Field In Table

Jun 10, 2013

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 Related

Make Field True If Other Field Is Null

Jul 10, 2005

Is there an expression in a query, that if want to say, if one field is not null make another field say true?

View 2 Replies View Related

Leaving Field Null Based On Another Field

Oct 15, 2005

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.

View 6 Replies View Related

Lock Field For A Record If Another Field Is Null

Nov 24, 2005

Lock field for a record if another field is null:

I would like to stop users from entering a date in "Ctrl Closed" unless they have populated "Ctrl Reason" for any given record.

Not sure how to do this.

Any ideas would be greatly appreciated.

View 6 Replies View Related

Modules & VBA :: Filter Subform Data - Only Show Records Where Field A Is Higher Value Than Field B

Oct 24, 2013

I want to filter my subform data, to only show records where field A is a higher value than field B.

Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True

This way it doesn't find field B.

Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True

This way it seems to filter all record to the field B value of the first record.

View 5 Replies View Related

Reports :: Show A Field Based On Result Of Another Field

Dec 4, 2014

I have a list of all previous employers for a job applicant. I can enter up to 10 but I only want the boxes to show if there is a previous position for that box. So, if there are 3 previous positions, I want to see 3 lines. If there are 10, I want to see 10. I have fields [Company_1]. [Company_2], etc. I only want company 2 to show if the person has 2 previous positions entered into the form.

View 1 Replies View Related

Show Query Field In Form Field

Aug 17, 2006

I have sequence field in query and want it show in form.
Please help me. Thanks

View 1 Replies View Related

Hide Field If Another Field Is Null

Aug 9, 2006

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.

View 4 Replies View Related

How To Clear A Field When Another Field Is Null

Oct 7, 2011

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?

View 2 Replies View Related

Error If Null Field

May 9, 2006

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])

View 4 Replies View Related

If Date Field Is Not Null

Mar 9, 2005

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?

Thanks
Kevin

View 6 Replies View Related

Requiring A Field To Be Not Null

May 30, 2005

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?

View 9 Replies View Related

Queries :: Sum Of Field Is Not Null

Aug 30, 2013

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

View 4 Replies View Related

Setting Field To Null

Jan 28, 2013

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.

View 14 Replies View Related

Add Field To Table Then Check For Null

Feb 10, 2008

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.

View 3 Replies View Related

Linked Field And Null Values

Feb 1, 2006

All,

Introduction (I case I'm doing this all wrong)

I have 1 huge table of data:

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........

Cheers,

View 4 Replies View Related

Null/empty Field Count

Nov 30, 2007

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

FROM [Report table part one]

GROUP BY [Report table part one].CaseStatus;

View 2 Replies View Related

Query Citeria For A Null Field

Mar 1, 2008

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?

Thanks for any help.
Shap

View 2 Replies View Related

Random Generate Into A Null Field...

Apr 19, 2005

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.

PLEASE HELP! Thanks In Advace,
Tara (aka Bay)

View 14 Replies View Related

Filling A Null Date Field

Mar 27, 2006

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

Dim intHold As Integer


intHold = Int(DateDiff("m", StartDate, EndDate)) + _
(EndDate < DateSerial(Year(EndDate), Month(EndDate), Day(StartDate)))

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()]

View 5 Replies View Related

Forms :: Populating A Null Field

Jun 26, 2014

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.

View 4 Replies View Related

Forms :: Not Able To Set Date Field To Null Value

Sep 4, 2013

I have a form that runs a search query.

I have an error trap set on a date field.

If (DataErr = 2113) Then
Select Case Screen.ActiveControl.Name
Case "DA_Start"
MsgBox "invalid date", vbExclamation
Me.DA_Start. = Null
End Select
Response = acDataErrContinue
End If
End Sub

This will pop up the error message, but will trigger the debuger when gettin to "Me.DA_Start = Null"

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved