Modules & VBA :: Date And Time Functions Not Using Current Info
Jun 30, 2014
Encountered a situation where the Date and Time functions in VBA are not using current data? I have VBA code that uses Time to determine certain actions. A new associate took a copy of that code and started tinkering with it to complete a project I assigned. Now, his copy of the code returns old data for the Date and Time functions. It returns 5/27/2014 for Date and 7:15:42 AM for Time. The Now function works properly.
Additionally, running the Date and Time functions in queries works fine. I've compacted and repaired the Access database, I've checked the references, and I've checked to see if he assigned values to variables named Date or Time, but I don't see anything wrong. What am I missing?
I am working on a report in Access2000 which is getting information from several tables via a number of queries. I am using nested IIf() functions to combine text formatted data in a calculated query field before using it in a report.
One such IIf() function is
Desc_col: IIf([HOVR - UDP].[SERVICE] Is Not Null,[DA] & " " & [SEQ] & " " & [TYPE] & [MO] & " " & [SUB] & " - " & [HOVR - UDP].[SERVICE], IIf([MinOfCCT] Is Not Null,"I/O DIST CAB " & [UDP - JC CIRCUITS-05-F].[PWR] & " - CCTS " & [MinOfCCT] & " thru " & [MaxOfCCT],IIf([DESCRIPTIO] Is Not Null,[DESCRIPTIO],"Spare")))
When I have tried to do the same thing with date fields it does not work. The function is fine as long as it is not nested.
That is
Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],"")
and
Rev_col: IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],"")
both work.
But when I combine them into a nested function
Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],""))
I get an error message "! Data type mismatch in criterial expression"
There is no data mismatch in the original tables from which the information was extracted. The dates are all in DATE/TIME and set to "General"format in the tables.
I am wondering if I will have to use Visual Basic function to combine these fields. However that poses a problem. When I've looked at this I find that runtime VB is no longer available in Access and I do not want to go out and buy it on the offchance that it will work.
Can anyone tell me if there is another solution to my problem?
I have a form with date/time field. I have the default value at =Now(). My problem is I would like for this particular field to stay with the current date/time all the time. I mean if I created a form last week with the date/time of 3/14/2005 12:54:34 PM and go back to the same form to edit something this week I would like for that particular date/time to be the current date/time and not the date/time from the week and time the original form was created.
I have a table called Neutron2015. I want the current date and time to complete when entries are made in two other fields. So I am looking for this date and time to populate in EXLStart when an entry is made in Processed By, and also the current date and time to appear in EXLEnd when an entry is made in Completed By with the bold word being the field names.
All, using access 2010. How do I display the default value of a date/time field to just the current year instead of using =date() to get the full date.
Looking to create two command buttons or two keystrokes sets in an Access 2007 form that will allow me to place a current date and time in any allowable field where the Cursor is presently placed. Similar to what was in Access 2000, ctl: (for the current date) and ctl shift : (for the current time).
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
When you update inspection requested the inspection due is automatically updated to 30 work days from that day.
Now what I'm trying to do is get the overdue field (it's a yes/no field) to automatically check itself when the current date is passed the inspection due and the inspection done field is blank.
Here's what I have that isn't working:
Code: Private Sub Form_Current() If DateAddW([REQUEST], 30) < Date And [INSPECT DONE] = 0 Then [OVERDUE] = True End If End Sub
DateAddW is a UDF that works just fine. I've tried replacing 0 with Null and neither works.
I am using access 2010. I need to rename a table when I import the a new table. But I can't get the docmd rename code to work. I also want to append the current date on the end i.e.
I am trying to query my records by the current records selected date then send the results in the body of an email on click. I believe I am close but I think there is a problem with the date format because I am getting 3421 Data type conversion error. Here is what I have:
Code:
Private Sub eMail_Click() On Error GoTo EH Dim dbExceptions As Database Dim rstExceptions As Recordset Dim dbDate As Database Dim rstDate As Recordset
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only. ([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null) How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I need a Select Query to display data on a form. When an order is appended to my table the field named Printed is updated with the time it was appended. When my form opens it needs to display the field Printed and a field I call MinutesFromPrint. This field needs to display the total minutes elapsed from the time in the Printed field to the current time. My expression is not working.
MinutesFromPrint: DateDiff("n",[Printed],Now())
Here are a couple of samples of my results when I run the query at 7:49 A.M.:
Hope all is well! I need to create a ad-hoc report for the rental items table . For example the table has an Expired Date. What kind of Date function I can use, so everyday, I can query out and show report for those rental that have 1 week prior to expiring date? Please help. Thanks so much!
For example : The Rental table has 3 records: Item 1, Expired Date on 12/13/07 Item 2, Expired Date on 12/22/07 Item 3, Expired Date on 12/29/07
If I run the report on 12/06/07, then the report should show Item 1 since it is expired in 1 week.
1/ First of all, how do I view in the Forums all of my own threads that I created? (silly question! )
2/ In MS Access 2000, what Date function/calculation expression used to count number of days between 2 date EXCLUDING weekends or even EXCLUDING Friday, Saturday, and Sunday? (since the workers work from Mon-Thu weekly). For example:
Date PO enter field: 02/01/08 Date PO sent out: 02/07/08
I would like the Number of Days will return 3 instead of 6 (since we don't count Feb 2, Feb 3, and Feb 4 since they are Friday, Saturday and Sunday)
3/ If I have a Date field called "Rental Expired Date", what is the Date function that I can use to give me the report of any given day of all the items that will be expired 2 weeks from the day I run the report? Better yet, our office use Novel Group Wise email application, is there a way to send this report directly to a particular person Inbox everyday?
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
I am writing a process for a hospital application in the UK
We have a concept of "Takes" which are periods to which patients coming into hospital are assigned to and thus the consultant (specialist) who looks after them.
They are fixed times between 0800-2000 (Day take) and 2000-0800 (+1) Night take.
I have written the following function to try and determine which take a patient should be assigned to based on the time now to power various queries to enable the end user to quickly see "today's day take" and "yesterday's take"
Testing it I seem to not be able to hit the right "Take times" in my output. I suspect it's a problem with the way I'm initiating my variables as Today 0800 and Today 2000
Code: Function GetTakeStarttime() Dim StartTime As Long Dim EndTime As Long Dim CurrentTime As Long Dim YestStartTime As Long
I want to save NOW() (i.e. Date and time) into an event log file. But I just cannot work out the syntax. My insert statement works fine without the date field in, but fails on a syntax error (3134) when I include it.
I'd like to prevent for duplicate time/date for same GuideCode. How can i prevent for those during the entry time or save time ? Is it possible ?
Capture1.
Capture2.
1. AUNKZ(GuideCode) is already assigned in other file number(12345) for 03-Aug-14 (as 02-Aug-14 to 04-Aug-14).
2. TOD(GuideCode) is already assigned in same file number(23456) for 10-Aug-14, 12:00 PM (as 10-Aug-14 to 10-Aug-14, 8:00AM to 1:00PM). I'd like to prevent if the file number are same or not same.
My goal is if the current date = 1st - 4th of the month to return the failure_date if it is >= 1st of the prior month and <= 4th of the current month (ie, 01/01/14 - 02/04/14)
this works: >=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4)
But if the current date > 5th of the month to return the failure_date if it is >= 1st of the current month and <= 4th of the next month.(ie 02/01/14 - 03/04/14)
this works >=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4)
But when I put it in the iif statement if will not work:
IIf(Date()>DateSerial(Year(Date()),Month(Date()),4 ),>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4),>=Dat eSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4))
SQL = SELECT table_testing_dates.Failure_Date, table_testing_dates.Failure_Date, table_testing_dates.FailureGrouping FROM table_testing_dates WHERE (((table_testing_dates.Failure_Date)=IIf(Date()>Da teSerial(Year(Date()),Month(Date()),4),(table_test ing_dates.Failure_Date)>=DateSerial(Year(Date()),M onth(Date()),1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date())+1,4),(table_testing_dates. Failure_Date)>=DateSerial(Year(Date()),Month(Date( ))-1,1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date()),4))));
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName FROM Staff INNER JOIN myTable ON Staff.ID = myTable.StaffID WHERE myTable.myDate >= #3/2/2014# AND myTable.myDate <= #3/3/2014# AND myTable.myTime >= #8:00PM# AND myTable.myTime <= #11:00PM# ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
I have a variable (dtDueDate as Date) showing as 6/28/2013. I want to append a time value to it (dtMaxTime as Date) which is 5:00 PM so dtDueDate reads 6/28/2013 05:00:00 PM. I have been going in circles trying to figure this out. My goal is to append this date to a table field which has a datetime (General Date).
