Working With Text And Dates
Mar 19, 2008
I have a situation where I need to extract the date from a text field to use in a select query. The date in the text field is listed at the beginning of the text field like this.
S12345678 3/21/2008 adittional text beyond here blah, blah...
the first series of numbers can be either 9 or 10 digits long, and then the date is always in this format, the length of the characters change accordingly with the date. So trying to use the Mid function only wont work.
I supposed I need to find a way to get the position of the space character in front of and after the date to use the Mid function for each record.
I need to be able to extract this date to do a DateDiff against another date.
I have been trying to do something like this.
Narr_Date: Format(Mid([si_narr_t],InStr(1,[si_narr_t],Chr(32)),InStr(9,[si_narr_t],Chr(32))),"Short Date")
But I know I am off the mark here. Can someone tell me what I am missing?
View Replies
ADVERTISEMENT
Mar 7, 2008
Hi there,
I have long file (1 mil records) downloaded from AS400 as .csv text file.
there 2 feilds - shipping date and receiving date.
I need to calculate the lead time in days as RecievingDate - (minus) ShippingDate.
It would be easy in Excel, but i have too many lines.
So there are 2 problems.
How to make text data from .CSV the date in Access?
How to calculate using date data in Access?
Thanks to everybody interested to help.
View 3 Replies
View Related
Dec 11, 2006
Hello,
I am trying to work out how to find the difference between two dates, in years and days. I have a [date_of_birth] field and another field [date] and wish to work out the difference in years and days as opposed to decimal years. Is it possible to do it?
Thanks.
View 1 Replies
View Related
Sep 15, 2005
I have set up an Attendance database at work, where a record is created for each member of staff when they are absent. The tables are set up as follows:
[tblStaff]: StaffID, FirstName, Surname, Team, JobTitle
[tblAbsence]: StaffID, StartDate, EndDate, Issue
What I want to be able to do is set up a query to search for a reason for absence on a certain date (btw my date format is set at dd/mm/yyyy). The problem I have is if the user needs to check a date in between the start and end date i.e. Start Date is 11/11/2005 end date is 11/12/2005 and the user checks on 01/12/2005 then this record will be found.
I know there is a simple way to do this but I can't seem to figure it out. Any help would be greatly appreciated!
View 2 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data tyle mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 1 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data type mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 3 Replies
View Related
Apr 1, 2007
I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.
View 14 Replies
View Related
Nov 5, 2007
I've had it before, but can't find it.
basically something like this --
DateDiff("w", StartingDate, EndingDate)
that also makes sure date is not in tblHolidays.
anyone knows how to acomplish this ?
View 1 Replies
View Related
Feb 1, 2006
I would like to count the number of working days between two dates, NOT the calendar days, if the workers only work Monday thru Thursday. I know we could use the DateDiff function, but HOW do I count excluding Fridays, Saturdays, Sundays?
Thank you for your help.
View 1 Replies
View Related
Aug 30, 2014
Can't figure out why dlookup will not work for me in a query for certain dates?
Simple example to highlight the problem:
Table1 -
Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40
Query based on this table with the following calculated field:
Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")
Query output -
Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40
Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)
If you try it with many dates its skips several and I cannot see a pattern.
View 7 Replies
View Related
Jan 26, 2015
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.
Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
[code]...
View 8 Replies
View Related
Oct 6, 2013
The below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.
It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.
I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.
Code:
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NetWorkMinutes
' Author : Rod
' Date : 13/12/2012
' Purpose : Returns the number of work minutes between two date-time arguments.
[code]...
View 14 Replies
View Related
Oct 26, 2006
I have searched the forum for this answer but no luck. :(
I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.
I need this time to be calculated in hours.
In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.
At least if i could get the working days i could then convert it into hours.
Please help!
View 7 Replies
View Related
Mar 16, 2008
Any ideas on calculating future dates based on # of working hours. For example... If a task is suppossed to be completed in 32 business hours, when would that be? Considerations include standard working hours M-F, no working hours on weekend. I have to believe that someone has written this before and I don't need to reinvent the wheel. ANy help is much appreciated!
View 5 Replies
View Related
Sep 9, 2013
I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.
Public Sub SetDate1()
'Apply date filter and rebuild query in real time
On Error GoTo Err_SetDate1
If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then
MsgBox ("Please Enter Date First"), vbExclamation
GoTo Exit_SetDate1
[code]....
View 3 Replies
View Related
Aug 7, 2006
I have a 'yes' check box and a 'no' check box. When the 'yes' check box is ticked, it enables a text box so I can enter a date and disables the 'no' box. Great, how it should work.
However, for some reason, whenever a date is entered into the text box when the 'yes' box is checked, every 'no' box afterwards is greyed/disabled out.
The thing is, it doesn't happen when no data is entered into the text box, when the 'yes' box is checked. So I can have the yes box checked, the no box will grey out as it should, and the text box will be enabled, as it should. But as soon as I enter a date, the above problem happens.
If that's not too confusion, can anyone help?
I've tried attached the database, but apparently it's too big, so i've posted the code underneath.
Cheers
Dan
EDIT: - i've just figured out that this problem is solved when I close the form and re-open it. But obviously I don't want to keep doing this every time I have to enter a date in a record.
Code:Private Sub chkSuccessful_AfterUpdate()If chkSuccessful = True ThentxtDateAppointed.Enabled = TrueEnd IfIf chkSuccessful = True ThenchkSuccessfulNo.Enabled = FalseEnd IfIf chkSuccessful = False ThenchkSuccessfulNo.Enabled = TruetxtDateAppointed = FalseEnd IfEnd SubPrivate Sub chkSuccessful_Enter()If chkSuccessful = True ThentxtDateAppointed.Enabled = TrueEnd IfIf chkSuccessful = True ThenchkSuccessfulNo.Enabled = FalseEnd IfIf chkSuccessful = False ThenchkSuccessfulNo.Enabled = TruetxtDateAppointed = FalseEnd IfEnd SubPrivate Sub chkSuccessfulNo_AfterUpdate()If chkSuccessfulNo = True ThentxtDateAppointed.Enabled = FalseEnd IfIf chkSuccessfulNo = True ThenchkSuccessful.Enabled = FalseEnd IfIf chkSuccessfulNo = False ThenchkSuccessful.Enabled = TrueEnd IfEnd SubPrivate Sub chkSuccessfulNo_Enter()If chkSuccessfulNo = True ThentxtDateAppointed.Enabled = FalseEnd IfIf chkSuccessfulNo = True ThenchkSuccessful.Enabled = FalseEnd IfIf chkSuccessfulNo = False ThenchkSuccessful.Enabled = TrueEnd IfEnd Sub
View 1 Replies
View Related
Jan 7, 2014
I have a textbox that has 3 columns in it (Rank, First Name, Last Name)...when the user selects the appropriate choice, it just shows the Rank.
Ex: Lt. John Smith --> just shows Lt.
To get around this I made an unbound text box and have it set to show all three choices when the selection is made in the drop down box. Unfortunately, it isn't working right. This is the code I am using for the text box:
=[OPRID].[column](1) & [OPRID].[column](2) & [OPRID].[column](3)
Is there anything that looks wrong with this code?
What it ends up showing in the text box is: JohnSmith (no rank, no space between the names).
View 3 Replies
View Related
Sep 17, 2014
I am trying to get a text string from a table to convert to the proper date format in access 2003.
The text string is formatted as 140930 for Sep 30th, 2014 for example.
I have tried using the formula following formula: Format([PCCSDTA_DCSDIU]![DTEAVL],"mm/dd/yyyy") but this format returns the date 11/06/2285 instead of the desired 09/30/2014.
I am still relatively new to Access.
View 9 Replies
View Related
Apr 23, 2014
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
View 10 Replies
View Related
Oct 2, 2006
I'm trying to make changes to records that are 3 years old. The date format is text and it displays as Ex. "20020831" or yyyymmdd.
i try <"20020101" but I get no results and I know there should be.
I'm a rookie and only know how to query using short date format.
ideas anyone?
thx
View 1 Replies
View Related
Dec 17, 2004
Dear All:
I have creadted a form with a combo box that has 3 dates in it: February 1, 2005, September 1, 2004 and June 4, 2004.
Is there a way to display in another field on the form(A text field) when one of these dates is selected from the combo box, a text field returns "DATED ON THIS FIRST DAY OF FEBRUARY TWO THOUSAND FIVE"?
I reckon this is a vb issue and any help is appreciated.
Thanking in advance
Dion
View 2 Replies
View Related
Nov 14, 2013
I have a Table Named "Combined List 05/06/03" Within this table there is a Field named "Scheduled Date" which has dates but the field was created as a Text Field. How do I go about changing this field to a date field. I have approx 95,000 records in this table. Is their a way to change this field to a date field or can I create another date field and move that info over. Im using MS Access 2010.
View 9 Replies
View Related
Mar 16, 2005
hi,
i want to dispaly the weekday or the day of the month
i am using the formula =format([date],"d") for day or "ddd" for weekday in the text box
but it is not returning the value that i want. returns #name//
whats wrong with the formula
many thanks
View 8 Replies
View Related
Jan 8, 2014
I'm bringing an archived db into Access, with entry forms to continue on into the future for maintenance. In the past, some dates were recorded only to year (yyyy). Obviously, I won't continue that, but I need to integrate the old and new in a fashion that will sort. For instance, one form has a subform section based on a report to show the old entries, while below are unbounded text fields for making new entries (with a Save command button).
How to integrate partial old text dates with newly recorded dates.
View 3 Replies
View Related
Feb 20, 2015
On a form I'm asking a user to enter dates in 2 different textboxes, say, [text1] and [text2], both formatted to short date. In a third textbox [text3] I need to see the date of the greater of the two, and in a fourth [text4] I need to see the date of [text3] if it falls on a Monday through Friday, or the date of the previous Friday if [text3] falls on Saturday, and the date of the following Monday if [text3] falls on a Sunday.
...a couple quick examples;
If text1 = 2/20/2015 and text2 = 2/27/2015, text3 should evaluate to 2/27/2015, and text4 should evaluate to 2/27/2015
or
If text1 = 2/20/2015 and text2 = 2/28/2015, text3 should evaluate to 2/28/2015, and text4 should evaluate to 2/27/2015
My attempts to compare text1 and text2 revolve around an IIF but result in an error...
Entering this into [text3]... IIF([text1]>[text2], [text1], [text2])
results in "#Name?" error, which I interpret as meaning the date from the source cannot be pulled into the formula.In attempt to check the day of week, I used =Weekday([text3],1) which results in a number. I haven't been able to do an IIF on it as it errors with #Name? again.
View 3 Replies
View Related
Mar 18, 2013
I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))
DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
View 7 Replies
View Related
