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 RepliesI 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?
I have a form with a subform. On the subform I have a field that has customer number in it, on the main form there is a field that will need to say "none" if the field with the customer number is empty, and empty if the customer number field is filled in. the field will not print out in the letter if there is no data on it.
the customer number comes from the table "CustomerNumbersData"
the the field that needs to be empty on the main folder comes from table "CustomerData"
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 Relatedhave this code:
Private Sub
Job_Number_BeforeUpdate(Cancel As Integer)
If
IsNull(DLookup("[Job Number]", "Job", "[Job Number]= " & Me.[Job Number]
& " ")) Then
MsgBox "Job Number doesn't exist Enter a job
number that already exist."
Me.[Job Number].Undo
Cancel = True
End If
End Sub
It is not clearing the Job Number Field, and also it is not letting me to close the form without entering the Job number that already exist. If I try to close the form without entering the job number it gives me run time error " syntax error (missing operator) query expression '[Job Number] = '."
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 RelatedI 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 RelatedLock 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.
I have got a form with a couple of dlookups on it, when they return an error it pops the debug message up and if there is a value in the field already it just leaves that value there.
How can I suppress the message and clear the field and move on to the next line of code?
How can I loop loop through a tabular form to clear each value in a field of all rows ? I tried the below code, but it did not work.
Loopcnt = DCount("*", "Budget")
For Loopcnt = 1 To Loopcnt
[Forms]![Budget Form].[Newbudgt] = Null
DoCmd.GoToRecord , , acNext
Next Loopcnt
The code pasted below creates a union query for a set of tables (J000171, J000174, J000178 etc) and stores the results of the query in a table called temp.
The first piece of code queries the ‘status’ field of a table rjobs for those records with a ‘status’ field of “Live”. Another field within this rjobs table, ‘JobID’, happens to be the name of a table where additional information relating to that job record is held eg. J000178 All of the tables selected in the query on rjobs are then included in the union query.
The second piece of code stores this information in a table called temp
I would like to be able to do 2 things with this;
1.add an additional field to the union query which holds the JobID field value from rjobs (or alternatively the table name from which the data originates eg J000178 etc as that is the same as the JobID file din rjobs)
2.create an option to clear the info in the temp table. Currently additional info is appended, so whenever the query is refreshed new data is simply added to old data. I would like to be able to clear that data where possible.
The union query is run from the on click of a command button on a simple form. Perhaps an additional button could be used to clear the records from the table temp.
Any ideas greatly appreciated.
Here is the existing code …
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim db As Database
Dim rsRjobs As Recordset
Dim rsRapps As Recordset
Dim LengthofUnionSQL As Long
Dim sql As String
Dim UnionSQL As String
Set db = CurrentDb
Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot)
Do While Not rsRjobs.EOF
UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, from " & rsRjobs!jobID & " Union "
rsRjobs.MoveNext
Loop
'following two lines are to remove the trailing word Union from the string unionsql
LengthofUnionSQL = Len(UnionSQL)
UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7)
' Now variable Unionsql will hold the value something like
' Select ObjectID, SearchNo, DateSearched, Consultant from J000145
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147
MsgBox UnionSQL
Set db = CurrentDb
Dim rsUnionquery As Recordset
Dim rstemp As Recordset
Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges)
Set rsUnionquery = db.OpenRecordset(UnionSQL)
Do While Not rsUnionquery.EOF
rstemp.AddNew
rstemp!ObjectID = rsUnionquery!ObjectID
rstemp!SearchNo = rsUnionquery!SearchNo
rstemp!DateSearched = rsUnionquery!DateSearched
rstemp!Consultant = rsUnionquery!Consultant
rstemp!jobID = rsUnionquery!jobID
rstemp.Update
rsUnionquery.MoveNext
Loop
End Sub
I am working on form where the user selects either "IN" or "OUT" from a dropdown of field name "CheckOut" in Frm1.
If they select "OUT" they will in turn need to fill in 2 additional fields. When they change the value back from "OUT" to "IN",
I want those other fields to be cleared of data for just this record so next time they change back to "OUT" from "IN" those 2 additional fields are already blank.
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 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])
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
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
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 Relatedi 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.
View 3 Replies View RelatedAll,
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,
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;
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
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)
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()]