String To Date
Jan 16, 2007
I have just inherited a database that has dates stored as strings ex.: 01/01/01. I really do not have time to add a new field and convert the string date to date format (this would cause a lot of rewrite to the application that is using this table). I am trying to use the 'between' to display records that are between specific dates. Also, I am using Cold Fusion as the front end, if that makes any difference. Below is an example I thought might work but did not:
<cfquery name="acty_due_month" datasource="customer">
SELECT acty_end_dt
FROM activity
Where DateValue(Format([acty_end_dt], "MM/DD/YY"))=10/30/05
</cfquery>
Any assistance would be greatly appreciated.
View Replies
ADVERTISEMENT
Jan 7, 2005
Hi there. I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format. Sorry if this is an easy question. - AJS
View 4 Replies
View Related
Mar 16, 2006
The dates in my table are strings that appear as 12305 (1/23/05) and so forth. So some are 5 and some are 6 digits depending on the 1 or 2 digit month. I want to convert them in my query to a date field. I think I have to use the DateSerial and extract the parts of the date, but that leading 0 that is not there is throwing me off. Any easy solutions?
View 2 Replies
View Related
Feb 27, 2014
I want to add a string as year to a date.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.
Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
View 3 Replies
View Related
Sep 30, 2011
I am doing a project which currently requires me to input a new file we just got. However, the dates on all of them, as in the field containing the dates, displays something like 20030608 instead of June 8, 2003. I was thinking I could just run the file through a looped vb array to go through the information, reformat it, and then dump it into a new column.
View 2 Replies
View Related
Aug 1, 2012
I am trying to create an order ID field based on the 1st 3 letters of the name of the buyer and the date of purchase. For example, if the buyer is James, I want the order ID to become JAM010112 (If the date is 01/01/12). How do I go about doing this?
View 1 Replies
View Related
Oct 6, 2006
I 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?
View 4 Replies
View Related
Nov 8, 2007
Hi all,
I currently have two strings that look like this 070707 and 020709. What i need to do is compare these two dates and see which one is greater.
Is there any easy way to do this since the year is just yy instead of yyyy?
Thanks in advance
View 2 Replies
View Related
Nov 17, 2004
Hihi
I have a field containing data in this format
01Apr04:12:34:56
for example
It is not recognized as a date by Access, so it is stored as text
I need to put it in any general date/time format, like
dd/mm/yy hh:nn:ss
I also need that Access actually reads it as a date/time (not a string) to be able to merge with other data, then sort chronogically.
How can I do that (please no code, only query if possible, i'm really a newbie...)
thx!
caroline
View 1 Replies
View Related
Oct 15, 2013
In my query I want to extract the last 10 characters of a string in a column which represent a date in the format DD.MM.YY and then convert these to a real date format to be available for further processing.
My query looks like this:
SELECT Angebotskopf.[Laufende Nummer], Angebotskopf.Angebotsnummer, Angebotskopf.Angebotsdatum, Angebotskopf.Anfragedatum, Angebotskopf.Kunde, Angebotskopf.Ansprechpartner, Angebotskopf.Telefonnummer, Angebotskopf.Faxnummer, Angebotskopf.Projekt, Angebotskopf.Preis, CONVERT(varchar(10), RIGHT(Angebotskopf.Projekt, 8),104) AS TestAngebot
FROM Angebotskopf;
But Access gives an error message "unknown function 'CONVERT'"
The "RIGHT" functions works but the resulting column is not being recognized as a date, it is a only a string and therefore useless for processing of any date related calculation.
View 3 Replies
View Related
Jun 21, 2015
I have a string value that comes out of a query in the form of
01012015
02012015
12012014
etc...
where the first 2 digits are the month, the next 2 are the day, and the next 4 are the year.How do I convert these values to a valid date that can be queried on?
View 14 Replies
View Related
Feb 2, 2006
Hi there,
I have to format a date following dd-mmm-yy in an expression also containing text.
Expr1: "Text" & " " Date()
Although separately formatting the date is easily done. I need to keep both int he same expression.
View 3 Replies
View Related
Apr 23, 2007
There is a end_date column record with date/time datatype, how could i query datat that showing the particular is empty.
I try end_date = NULL, no record is showing and end_date = ' ' , datatype mismatch... so which one should i write?
View 3 Replies
View Related
Apr 27, 2007
In the following statement i tried to send BeginningDate(textbox value) and
EndingDate(txtbox value) to the SQL stmnt but it gave an error saying "Type
Mismatch" even though i used CDate method. answer for this problem is greatly
appreciated......
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND
Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" +
cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND
Region.nRegionId=IndicatorData.nRegionId AND
Institute.nInstituteId=IndicatorData.nInstituteId AND" + _
"IndicatorData.indicDate Between '" + CDate(BeginningDate) +
"' AND '" + CDate(EndingDate) + "' " + _"GROUP BY
IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " "
View 2 Replies
View Related
Nov 12, 2013
I would like to convert 2013.11.13 which is a string into a date. This is what i have so far but it's not working.
2013 year
11 month
13 day of week
Dim strDate As String, dteConvertedDate As Date
strDate = 2013.11.13
dteConvertedDate = CDate(Mid$(strDate, 6, 2) & "/" & Left$(strDate, 4) & "/" & Right$(strDate, 8))
MsgBox (dteConvertedDate)
End Sub
View 7 Replies
View Related
May 22, 2015
I am trying to add 30 or 45 days to a certain date and insert that date in a table. The field is declared as datetime on the SQLServer and has been tried with all date formats, and none, on the access side.The problem I am encountering is that the date is being inserted as 12:00:00 in the table, no date is being stored.When I print out the SQL statement to the immediate window, the date in question shows the correct date.
Code:
Dim a As Date
Dim b As Date
a = DateAdd("d", 45, Me.DateOfExit)
b = DateAdd("d", 30, Me.DateOfExit)
strSQL = "INSERT INTO MyTable ( No, Name,DueDate, OriginatedBy, ID, Status) " & vbCrLf & _
"VALUES (" & Me.No & ",'" & Me.Name & "', " & b & ", '" & Me.Facilitator & "', " & Me.ID & ", '" & "Required" & "');"
I have tried adding the date add code to the SQL string, but that produced the same result.
View 4 Replies
View Related
Apr 17, 2014
I'm working with a table of financial transactions. I need to know the date a record relating to cash received actually arrived in our bank, but the software doesn't allow the user to enter this, so I've asked them to enter it into a text reference field, e.g. 'Other Text XX 16/04/14', and then I've got a formula - CDate(Right(Trim([RefField]),8)) - which takes the date from this reference.
This works fine, but it's really important I don't miss any of these records due to the date not being entered, so what I'm trying to make is a formula which will give me the date from the ref field as in the formula above if there is one, but if that formula gives an error (so the date has been missed out or incorrectly entered) then I want to take the date from another field, called [DateAdded] as a failsafe.
I can't work out a formula that won't give me an error, i've tried loads. First I tried :
iif(iserror(CDate(Right(Trim([RefField]),8))),[DateAdded],CDate(Right(Trim([RefField]),8)))
But that gives an error, and I think from looking at forums that Access evaluates both parts of an Iif function so it'll do that.
It seems like other people are saying that you need to use Nz, IsDate or IsNumeric or something along those lines instead of IsError to evaluate the field, but I can't get that to work as it's not a field, it's a portion of a text field.
This is my latest attempt:
Join Date: IIf(IsDate(Right(Trim([RefField]),8)),[DateAdded],[Calc Date]),
where [Calc Date] is an expression field with CDate(Right(Trim([RefField]),8)) in it, but there's the #Error again.
How can I use iif to pick either a date from a text string, or where that results in an error then another date field?
View 9 Replies
View Related
Apr 7, 2015
I need to use VBA to get a string which is essentially just a month and year (so date, not including day)
Would Prefer it to be 04/15 as opposed to April-15, as the number is easier to sort than the word (how to sort month names in numerical order as opposed to alphabetical id).
So that I assume i could do by =format(now(), mm/yy) (though havent actually tested it)
However to add to the complexity id also need to add a month to it, so for instance todays date is 08/04/15, id need the string to read "05/15"
or if the date was 10/12/15 id need the string to read "01/16"
View 3 Replies
View Related
Dec 23, 2014
I wrote a VBA code to get the field value of a csv file, then use it to update the database table. I paste some of my code below
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objUpdate As ADODB.Connection
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolderName & ";Extended Properties='text;HDR=Yes;FMT=Delimited'"
[Code] ....
1. The target of this code is to set the [Section] value in the table
2. The [Section] value is at the 3rd column inside the csv file
3. By using the UPDATE query, it map the [Section] value to the corresponding [Program] and [Course] in the table
This code did the job. But the problem is the string of the [Section] value is like "2-22-01". But after update to the table, the value become "2/22/2001".
The value is a string text, not date. How can I prevent this happen ?
I just attached 2 files, 1 mdb and 1 txt (change the extension to csv before test).
View 3 Replies
View Related
Apr 10, 2013
I am connecting acces to oracle servers via ODBC .
In the table there is a date time stamp - format data type text:
20130225060621
I would like to convert so that it is recognised as a date so I can calculate against e.g. Now() or another standard date format.
this is what I am currently using (which is OK) BUT I need to calculate against the time also.
Current Check Point Date: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))
I spoke to an IT guy who works in TOAD program and he gave me this SQL:
select to_date(dz_zpkt_ak,'DD/MM/YYYY HH24:MI:SS')from orders where ordnr='4411310';
this is the format I would like but cant make this work.
View 12 Replies
View Related
Jan 10, 2015
I am trying to filter a recordset with a variable q. The field in the table associated with the record set is of data type Date/time. I assigned data types String and Date to q but both cases generates the error message " Data type mismatched in expression".
View 10 Replies
View Related
Feb 13, 2014
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."
View 3 Replies
View Related
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 Replies
View Related
Mar 12, 2007
Hi all,
I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?
Any suggestions would be gratefully recieved.
Andy.
View 3 Replies
View Related
Feb 10, 2014
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
View 2 Replies
View Related
Oct 27, 2006
I have a column called CPU_S within a table called workstation that contains sample text like P111 933
I want to use the update command to search the CPU_S column for entries that contain this in there string then add P3 to a column called CPU_N
So far I have the code below but I don't know how to search a column entry for a specific string within a string. Can this be done and how?
UPDATE workstations SET CPU_N = "P4"
WHERE CPU_S = ;
View 3 Replies
View Related
