I've been trying to convert a date format like dd/mm/yy to a SAP used format like dd.mm.yyyy .
a simple string conversion like
Code:
pp = day(datefield) & "." & month(datefield) & "." & format(year(datefield),"YYYY")
is not working, the year is converted wrong.
thus 17-07-62 should be converted to 17.7.1962 ( European date format )
I have a date value in text format that is 5 character and want to convert it to a proper date format. Here is a sample of the data:
07301 actually represents 7/30/2011. How to actually convert that value to the date format of mm/dd/yyyy?
Hi,
and again I have a problem :)
so,
In Form I have two calendar controls, and two text boxses. Based on selected date, text boxes are showing day of the year (format(Date(),"y")).
Query is based on table with records of days in year (numbers),
so, when Im trying to sort Between two numbers from Form (in this case nubers of days in year) query is reciving (imo) full date, not the number and im not getting any results.
Any non VBA ideas?
tnx
I'm tring to build a queary based on a field called "PLAN_CODE" and a policy issue date field. There are 3 plan codes ...select1, select 2 and select 3.
I want to pull information as follows
Select1 has an issue date 11 months ago (day the queary is ran)
select2 has an issue date 23 months ago
select3 has an issue date 35 months ago
I built a field to calculate the duration of the policy based on the issue date
Duration: (Round((POLICY_ISSUE_DATE]-Date())/365,1)). I think the format is wrong? need help with that!
I built a field to create a nested "IF" statement but the first "IF" is not pulling correctly, so I cant built the remaining "IF" statements.
Expr2: IIf([PLAN_CODE]="SELECT01N" [Duration]=-0.9,0).
Anyone have a better approach? Any input how to handle this? I'm not real good with VB code but have a general understanding of it. It's easier for me to build in the QBE.
Hi Folks,
I have a table of dates and a "frm Dates" based upon it. I have copied a very useful calendar control which I found on a sample datebase elsewhere on this excellent site. I want to have an [OK] button which jumps to the correct date in my frm Dates when I choose that date on my calendar, but it's getting the months and days the wrong way round. Here in the UK our favoured approach is dd/mm/yy, but to try and figure this thing out I've formatted everything as Long Date.
Further details are on the form of the attached database, I hope you can help. Non-technical answers most appreciated as I'm pretty much a greenhorn with this stuff.
Thanks all!
I have a query based on two tables that calculates days elapsed between two dates. Problem is one date field is date/time and the other is short date, so the result is always infinite decimal places. I don't want to change the date format for the date/time on the one table. I tried the format function in the query, but still ended up with decimal places.I just want to make that one long date to act like a short date for calculating "days", not hours, etc.
View 1 Replies View RelatedI have a date entry textbox.
The user is copying a date from an old form.
Sometimes the form will have the date entered as 00/00/97
How can I change the month and day to 01/01 and of course leave the year as it was.
I've tried using split with no success.
I have a numeric month, as in 1 for January. I want to convert 1 to January. Any advice on the syntax?
View 1 Replies View RelatedI'm imported data from a csv file. The dates stored in there are in a dd-mm-yyyy time format. How can I work with this since Access's date format is just mm/dd/yyyy? I imagine I need to do some sort of conversion? Does Access provide anything for me to do this?
View 6 Replies View RelatedI have a numeric month, as in 1 for January. I want to convert 1 to January. Any advice on the syntax?
View 2 Replies View RelatedI have a time stamp field from an Oracle database that I want to convert to a regular date field in my Access query so that I can pull data from the table base on start and end date.
The time stamp field is formatted as: 09/19/2006 03:16:00 PM
In my query I have tried the following formatting:
1. DateRcvd: Format((Left([time_stamp],10)),"mm/dd/yy")
or
2. DateRcvd: Format([time_stamp],"Short Date")
Without criteria I get all the records in the following format:
1. 09/19/06
2. 9/19/06
Dates in the table are from 06/01/06 to current date 07.
Using the following criteria - I get varing results but never what I want. For example, using format #1, if I enter 09/16/06 and 01/10/07 I get everything for 07 and nothing for 06
If I use format #2, I get everything for 9/16/06 (no other records) and everything for 07
Criteria:
Between [Forms]![frmDateRange]![StartDate] And [Forms]![frmDateRange]![EndDate]
Any suggestions?:confused:
My challenge is to fix a broken report that results from a query. The query is suppose to bring up all records within a date range. The problem is that the table was designed with the date field being text. Dates such as 06/12/2006 are entered without the beginning 0, so simple queries do not work, and the data is simply 6122006. I think the data needs converted to date first, possibly by extracting and converting. I do not know how to do this because of the missing digit inconsistency. The table cannot be changed directly to a date field without data loss.
Thanks for any suggestions.
What steps do I need to take in order to convert a 2007 database into a 2010 database
View 4 Replies View RelatedI have a table with a text field to receive dates or "na" or "n/a". From this, I am trying to run a query that selects all dates that are not "na" and not "n/a" and not null. This works fine with a criteria of
"Is Not Null And Not In ("NA","N/A")"
The problem is, I also must select a date range. In the query, I have created a calculated field equal to various date conversion functions and they all seem to work at converting the text to date. However, I get a Type Mismatch error any time I try to do anything to the date field such as select a date range or to sort on it.
Any suggestions?
I have an Excel file that I want to import into an Access db table. In that Excel file is a date field formatted mm/yy. When I import that file, Access converts the date to m/d/yyyy. Is there anyway to re-format that data after it's imported into Access (like a global change?)? I even tried setting up an input mask for that date field, but that only seems to work when you are actually typing data into the field.
Thanks for your help!
rbgolfn
Hi all.
I can complete this in excel no problem with monday through sunday being 1 -7, but is the same possible in access.
i.e. 12/03/08 = Wednesday.
Many Thanks Dean:confused:
Update:
DoW: Weekday([Calldate],0)
I am the junior of the ms access.
I try to convert the below text to date
(03/08/2015 19:42)
(31/07/2015 12:20)
CDate([XXXX])
return 3/8/2015 7:42:00 PM (should be 03Aug2015)
return 7/31/2015 12:20:00 PM (should be 31Jul2015)
I have received a Access97 database which has a date field filled with numbers.
The date of birth field is in the format : 19970131
And the date of birth field is a text field.
The software requires the date to be in dd/mm/yyyy order and also to be a date/time field.
When I try to change the text to date/time, Access deletes all dates of birth.
I am not sure how to solve this as I am very new to databases.
Can someone please help me soon?
I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.
The query is supposed to pull all data where the "Date Overdue" field is less than today.
"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.
It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.
"Date Overdue" is set to this value:
Code:
DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
"Date Overdue" has the criteria "<DateValue(CDate(Now()))"
I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..
The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.
I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:
"Syntax error (missing operator) in query expression 'DATE OVERDUE' "
NOTE: I'd like to add that this is just a regular Select query.
Code:
SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))
AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8
AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS],
[Code] .....
I have two table
1. dbo.period (OpeningDate, ClosingDate)
2. dbo.data (blah blah, doc_date)
I want to create a view as follows
Select doc_date from dbo.data
where doc_date> 'select OpeningDate from dbo.period'
both doc_date and opening date have the same format
but the error will still appear as follows:
"Conversion failed when converting date and / or time from character string."
Formatting issue regarding elapsed time calculated using DateDiff().
I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.
[PunchIn] = 11/23/2013 8:11:28 AM
[PunchOut] = 11/23/2013 5:43:30 PM
[ShiftLength] =DateDiff("n", [PunchIn],[PunchOut]) = 572 minutes.
Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:
=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct
=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example
=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?
Hi,
Is it possible to change the resulting format of a new field (expression) within a query?
I.e. Instead of the resulting when running query being displayed results in decimal places but instead being displayed in percentages
Regards
I can't seem to set the format and decimal places to do what I want in my table. I've tried several different combinations of Field Size being either Integer or Long Integer, Format being either General Number or Fixed, and Decimal Places being either Auto or 1.
What I want is to display the number I've input as I've typed it. For example, I type 10.6 and thats what I want.
But instead I'm getting either 11 or 11.0 or 11.00 for all the formatting options I've tried.
Can someone please tell me why its doing this rounding to my number, and what do I need to change?
Thanks.
I am having trouble applying conditional formats to my access db they work in excel but cannot seem to put them in to access I have one table that my forms and queries look at
These are the conditions i had used in excel
formula is =AND($H18>=TODAY(),$H18<TODAY()+14)
this turns red if within two weeks of today
formula is =AND($H18>TODAY()+15,$H18<TODAY()+43)
this turns amber if between two and six weeks of today
less than =<TODAY()
text is struckthrough and turned red
Any help with implementing these in access either similar or new method
I have built several reports and I have set the layout to Landscape. When I go into the reports after a few days, it puts the format back to portrait. How can I stop this from returning to portrait?
View 2 Replies View RelatedHi
I have been battling to get a mail merge to work correctly with a access database, the data isn't coming across correctly.
I have the date set up in access as a long date format, when it merges into word it changes to 2005-10-05 00:00:00, can anyone tell me what I need to do to get the date to appear correct.
thanks in advance
Di
ps I have read and re read the help pages on this but still can't get it to work