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?
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()]
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"
I'm really lost on this one... How do I find the # of days it takes to complete a task between two date fields. For example: [DateRec] field is the field the app was first input to the database. [DateNumb] field is the date the first task was completed. So I understand, =DateDiff("dd", [DateRec], [DateNumb])) But I'm getting an #Error because often these two dates are the same date/equal, Or/ the DateNumb field may pre-date the DateRec date. Or/ one or both fields might be Null (blank). How do I set the formula on this?
Bottomline,I need to find the average # of days it takes to complete a task between two date fields for all tracking records. How do I do That???? HELP!
I am new to Access. I am after a vba script to run an update query.
I have a table named MYOB_Invent. I have a Query Named MYOB_Update.
The query is showing records where my field named Test1 is null.
I want to update records with null in the field to today's date (the date of the day that it is run) formatted as 18/08/2014 and the field next to it is named Updated (which is yes/no) from un-ticked to ticked for the updated values.
I would like a vba script to automate the process as I want to automate it via a commandbutton for users.
I'm new to programming with Access but am attempting to create a new field in a table with an new date based on existing fields in the table.
The current fields are [Frequency], integer, [Risk], text, [Last Audit Date], date/time, and the new field is [Next Audit Date]. [Frequency] is a calculated field based only on [Risk] and is equal to "5" if [Risk] is "Low" and is "3" is [Risk] is "Medium" or "High", and [Frequency] is blank if [Risk] is (thus far it has never been empty).
What I need the new calculated field to do is return "N/A" (or blank, or anything easily separated really) if [Frequency] is blank, or if [Risk] is "Low" or "Medium". If [Risk] is "High", [Next Audit Date] should be equal to [Last Audit Date] plus 3 years. When I try to save the code, I get this message: "The expression could not be saved because its result type, such as binary or NULL, is not supported by the server."
This is my code now: IIf(IsNull([Frequency]),"",IIf([Risk]="Low","N/A",IIf([Risk]="Medium","N/A",IIf([Last Audit Date]="N/A","N/A",[Last Audit Date]+Year(3)))))
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.
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
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 form with two controls: DateFrom & DateToI want to filter based on those criteria but when the criteria is ommitted (NULL) I want to display all.In case of other controls I have an SQL as follows:... WHERE (tblClassesOffered.Course = Forms!frmClassReport.class Or Forms!frmClassReport.class Is Null)But with the dates controls the best I could come up with is:...Between IIf(IsNull([Forms]![frmClassReport].[DateFrom]),#1/1/1900#,[Forms]![frmClassReport].[DateFrom]) And IIf(IsNull([Forms]![frmClassReport].[DateTo]),#1/1/3000#,[Forms]![frmClassReport].[DateTo]) Or Is NullHowever that solution is kind of ugly becasue instead of wild cards (I couldn't make them work) I'm using those made up dates and I also wish not to use the IIF if possible (like the first statement).
I have a query that returns hours of vacation time taken for each specfic day of the year. But if no time is taken on for example Jan. 19th, that date do not show up in the query. I am wondering is it possible to return all 365 days and display a zero,if nothing is booked for that day. Attached is the db.
A form with a text box expecting a date has the validation property as, >#1/1/2010# And Is Not Null. If a date is entered older that this the error is caught however if the field is left null it is not caught as an error. I have tried several modifications of this statement with the same results.
I have form that user can filter the records and generate a report but I have difficult trying filter null date.
If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...
I'm trying to search through a table with serial card IDs, Order Numbers, and Ship Dates. I'm search through the Serial Card ID column and making my code work so that if the serial card ID exists and its ship date is between 9/30/2001 and 10/1/2011, then retrieve the associated order number and put in array. All of this works perfectly except for one tiny part : it's not returning a null for either unfound serial Card ID or serial card Ids that exist but their ship dates do not qualify. I would like to return a null value for these fields into the array as well as keep the found values in the array. The whole point of all of this is so I could count the number of rows for all the found order numbers and return a total value with that number.
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 need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.
When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?
Could there be some sort of join preventing all records from being returned?