I have a bit of an odd request. I won't be using normal dates and times, it's a military way for date and time, called a Date Time Group (DTG). It looks like this: 052337Z APR 06
The first two characters (05) represent the day of the month. The next four characters (2337) represent the time. The sixth character (Z) stands for Zulu, which is what we call the format of the time. The next three characters (APR) is the month abbreviation and finally (06) is the current year. Zulu time is obviously a 24 hour clock and it is always GMT...no daylight savings, etc...
I need some ideas on how I can store this in the database and search it as if it were an actual date, so I could get all records between two certain dates, for example. I'm not quite sure on how I would go about doing this.
A couple things I thought of maybe doing is when the user enters the criteria, converting the DTG they enter into local time and have the values in the database stored as local time. Not sure how to do that. Second I thought maybe storing each part of the DTG individually and then searching that way. Any help appreciated.
I have a query that contains a complete_date including time (this is automatically datestamped when the user presses a button)At the end of the day, I want to show how many records where processed that day but cannot figure out how to display records between 06:00am and 08:00pm on todays date.
Also, the same applies to selecting a range of dates (eg. the full week), How do I display the records that fall between Monday - Friday including the time.
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 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 table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
so i created a system to have events booked, and i am trying to check time availability of the event room available, but i dont know what wrong. it either my query or vba code. i have attached the attachment,
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
I have a column/field named [DateTaken] which contains test dates and times in the same cell. I am needing to find those with a test time less than 2:30 pm or <14:30pm.
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 would like to make an Access database for my collection of scientific papers. From that database I should be able to find articles using descriptive keywords. The problem is I haven’t figured out how I could search for the articles needed using multiple keywords at the same time (eg. papers that fill the criteria: <keyword a> AND <keyword b> AND <keyword c>).
What kind of tables and queries I have to create to do that?
I've created a search on a text and date field which has been working fine for few days now. However when I've started to search on the early days in February (i.e. 01/02/2005) it is not finding the records (but they do exist). When I create a record with 13/02/2005 or higher however it does find the record. I desperately need a solution as the DB will fall down without it. The code I'm using is:
If Not IsNothing(Me.TxtEmpNameSearch) Then VarWhere = (VarWhere + " AND ") & "[EmpName] ='" & Me.TxtEmpNameSearch & "'" End If
If Not IsNothing(Me.TxtDateSearch) Then VarWhere = (VarWhere + " AND ") & "[Dates] =#" & Me.TxtDateSearch & "#" End If
If IsNothing(DLookup("txtempno", "tblwork", VarWhere)) Then MsgBox Mes5, OKIn, Ti End Exit Sub End If
In criteria of a Query I have this SQL: Like [Forms]![FSearch].[qb1] & "*"
This SQL is to write in a textbox one parameter at a time to search in a table.I need to search for more than one parameter at a time and I have been trying to add to the SQL the operator OR or AND but does not work.
Like [Forms]![FSearch].[qb1] & "*" OR Like [Forms]![FSearch].[qb1] & "*" Like [Forms]![FSearch].[qb1] & "*" AND Like [Forms]![FSearch].[qb1] & "*"
How can I search for more than parameters at a time?
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
I am trying to compare a value in my databse produced by the date() function, short date format, to one that is exactly 24 hours after the value recorded by the date() function. If the value in the database is 24 hours prior to the current date(), I need to flag a text box a diffrent color to alert the user. I am unsure on the If statement that I will need to produce this result.
Any help would be greatly appreciated!!!! Thanks Mikeco555
I'm getting a Run-time error 2295: Unknown Message Recepients.
I've got a DB of about 2000 clients. In testing I did a test DB with 50 random clients. Using any search criteria, it would grab those clients and open a new email with their email addresses no problem. However when I do this same thing with my complete DB of 2000 clients, I get this run time error. I'm pretty confused here, and anxious. It looks like the program is working, I just need it to run with all my clients. This is the last thing I must conquer to be finished with this project.
I am attempting to create a search form where a user can search by either employee name or company name. I have 5 tables to use. Is there a simple way of creating a search method for this? I would like to be able to have the user type in a name and click a button that says search with any records matching the search come up. However, I could definitely use a method where they type it in a box and it finds it as the person types.
How to search from all five tables at the same time.
I have a report that have a datebox(Short date) and a timebox(Short time) and also a box that contains numbers like 36:59 and so on. That number is hours and minutes, I would like to take the date, time and add my 36:59 and get a new date from that and put that in another box. The date and time is used as planned date and planned time for some work to happen. 36:59+planned time+planned date=should be planned finishing date and time. Does anyone know what I have to do, to make this happen ???
How can I create a field in a query, extracting the date portion of a date/time field? The format is mm/dd/yyyy hh:mm:ss. I need just the mm/dd/yyyy so I can prompt the user for a date range, without them having to key in the date mm/dd/yyyy 00:00:00 to mm/dd/yyyy 23:59:59. Please Help
I have a "Search" form that I am using to search through a query in a list box of all of my Customers. Once the record that I am wanting to view comes up I highlight it and click a "View" button to bring up another form with the customers full record. When this form is open I am able to update the customer information and add User's to it if necessary.
My problem comes after I click on my save and close button that takes me back to me search form. I want to close the search form and the error I get is "The record cannot be deleted or changed because table "tblUserProfile" includes related records." I click OK and get a SECOND error stating "You can't save this record at this time. MS Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?"
I have already made my changes and saved them in the form with the customer record so am not sure why I am getting an error when trying to close the Search form.
Help! Right now in my database the dates are displayed as yyyymmdd, and i'd like to be able to pull all records in a month (to send birthday cards). How would I do this? Thanks.
In my database I has a Month([Date]) field which returns records from the criteria of month "1", "2" through to "12". However I want it to return them for those months only of this year.
Is there a date function where I can have something like Month([Date]), Year ([=Now])
