Converting Text Dates To Integer With Nulls Present?
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.
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.
Is there an easy way to convert an integer into time? What I want is for the person to enter in 0820 or even 820a and then convert it to 8:20 am. This is mainly to save keystrokes.
I'm combining two date/time fields in a query to an integer. The first field has the date, the second the time. I'd like the resulting integer to be without the opening 0. How can I do that?
That is, these are the two fields: 2006-09-14 (date/time) 15:00:20 (date/time)
And I'd like those two combined to be 60914150020 (integer) in the third field in the query.
Basically, I am trying to calculate a integer number difference from two dates (TAT = Due-Date - Result_Date). The number is calculated and excludes weekends and ideally holidays (for that I have a tblHoliday but not sure how to use it). The function below seems to calculate a number but doesn't exclude weekends.
For example, if Due_Date is 9/26/2014 and Result_Date is 9/30/2014, then TAT is calculated to be 5 (should be 2).Since 9/26/2014 is a Friday only Friday and Monday are used in the calculation.
Code:
Option Compare Database Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long Dim intCount As Long intCount = 0
Ok, let's say I have 3 tables with the following data
T1 T2 T3 1 , 1 , 1 2 , 2 , 3 3 , 4 , 4 , , 5
I'd like to be able to create a query that has the only record as its result as 5 as it is the only data item the does not exist in Tables 1 and 2. I don't just want to search for 5, I can do that :) I would like to be able to write a query which finds all the records that are present in one table, but are not present in x number (in this case 2) of other tables.
I don't seem to be able to find any simple way of doin this and there doesn't seem to be a definitive answer on any forums. Anyone got any ideas?? :)
I created a form for a database I am building. While in Form View some of the fileds have data in them and some do not (by design). How do I automatically grey out the areas that have no information in them? I know there is a way to do this but I don't know how. Thanks.
First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.
Problem:
Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)
I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.
Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)
or
Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).
Any advise would be much appreciated.
:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.
I am having a query which having a category field like Electrical, Sports, House hold etc.
What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...
Option Compare Database
Public Function fncGrade(intNum%) As String Select Case intNum Case 0 To 1: fncGrade = "Same as Previous" Case 2 To 32: fncGrade = "C-3" Case 33 To 40: fncGrade = "C-2" Case 41 To 50: fncGrade = "C-1" Case 51 To 60: fncGrade = "B-3" Case 61 To 70: fncGrade = "B-2" Case 71 To 80: fncGrade = "B-1" Case 81 To 90: fncGrade = "A-2" Case 91 To 100: fncGrade = "A-1" Case Else:: fncGrade = "X-X" End Select End Function
I have a database that was built 5 years ago that has an auto field with an integer. There are relationships attached to this. I an rewriting it to simplify the database and I need to keep the relationships somehow. I want to make the auto field a text fields. How to work this out...
i'm importaing a table through a mysqlserver, it contains a field (call_time) and its a long integer, i suppose it's an unix date format that can be converted
into DD:MM:YYYY HH:MM format, only problem is i cant find a way to do it on access i've tried creating a query with all the fields and adding my one fields with commands such as valdate([call_time]) but it dosen't seem to work.
I am trying to write some VBA to convert a date into a week number.
My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:
I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.
Start day for the week is Sunday. End day is the Saturday.
Some examples to check against:
22nd December 2013 = Week 52 2013
29th December 2013 = Week 1 for 2014 ----
21st December 2014 = Week 52 for 2014
28th December 2014 = Week 1 for 2015 ----
20th December 2015 = Week 52 for 2015
27th December 2015 = Week 1 for 2016 ----
25th December 2016 = Week 53 for 2016
1st Jan 2017 = Week 1 for 2017 -----
Tried various methods already
iso 8601, wrong week start day
intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.
- Assets - Shifts - Machine Offline Date - Machine Offline Time - Machine Online Date - Machine Online Time
Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.What I have already done is allocate shifts per asset e.g. when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.
If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM
Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs
Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.
I would like to make this an automatic calculation, Is this possible?
I have two tables that I want to append to each other.But in one of them the date column has the yyyymmdd format and in the other it is a serial date number.How can I make them consistent by either converting yyyymmdd to date number or by converting the date number to yyyymmdd?
My table has many records for each month. I am creating a report that will display only the records in one month of a year. I have been able to create a form that gives the user the choice of the Month and Year for which to create the report. The code I am using to create the combo box is:
Code: SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName FROM MainTBL ORDER BY Month([QTDate]);
What I need to do now is create the query for the report that displays all records for the chosen month and year. If I simply reference the combo box, all it shows is mmmm yyyy and the days are missing so the query doesn't work. What do I need to do to create the query so it displays all days within the month and date selected?
Hi, I have the following problem which I hope someone can help me with:
I am importing a CSV file, one of the field in which contains times in the following format: 1200 (ie this is 12:00, th text file does not contain the : separator)
I want these to be recognised as time values by access, which at the moment only treats them as text. Is there any way i can run a query on this table to convert the numbers into times? I can do it in excel using concatenate to add in the :, but access does not have this function.
I have a table with a field called 'dereg_date' which is formatted as text, an example is as follows:
2006-07-07 00:00:00.000
I want to be able to convert this field to a date. I have tried using a query with Cdate([dereg_date]), however it doesn't convert it and just returns #Error for all the records.
Has anyone got any ideas on this? I need this fixed fairly urgently as my superiors are getting quite impatient!
Is it possible to convert the text field that comes in this form:
:80421
to the following datetime format: MM/DD/YY 12:00:00AM
Please note that the text field contains a colon at the beginning. The number 8 represents a year (2008), the 04 represents a month (April) and the last two characters are days in the month (21). The converted field should look like:
I have a table with person's name, clubname, cluburl. In a report I can show the clubname,but when I export to HTML, can this clubname become a hyperlink to the clubURL ? (While showing the clubname..) Anybody ideas ?
My Db had an email_address field which was a hyperlink. For various reasons I had to change it to a text field. However, now every email shows as: some_email@domain.com#http://some_email@domain.com# Is there a simple way of cleaning this up so I am only left with the email address? In other words is it possible to create an update query to remove everything between # signs (including the # signs)?
---- I found some vba code on the forum which extracts strings but was wondering if there is an easier way just using an update query since I'm still not too efficient with vba
I have a date field in a form which is not compulsory.
I then pull this date field into a query to create a report.
If the date is not entered then is it possible for "Date not entered" to be written into the report automatically. I have tried playing around with nulls but have had no luck.
I have a file called CAPTURE.BUF. I am trying to convert it to text. Here is what I ahve done, but I get the message box ever time.
Public Function copyFile() Dim SourceFile As String Dim DestinationFile As String SourceFile = "C:PCPICSWCAPTURE.BUF" DestinationFile = "C:INVESTMENT_REPORTSCAPTURE.TXT" On Error Resume Next FileCopy SourceFile, DestinationFile If Error > 0 Then MsgBox "Could not copy mls file." End If On Error GoTo 0
I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?
