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
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."
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 RelatedI am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following
Code:
strleftB = Chr(34) & " & me."
strRightB = " & " & Chr(34)
strTemplate = Replace(strTemplate, "[", strleftB)
I then get a string but in need to convert part of the string into variables, before I capture the correct output for my email
Code:
"<div>RFC Submission: <strong>" & me.ChangeID & ", </strong> " & me.Details & "</div>"
My question is: what is the best way to split the string into strings and variables
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
I have a string that includes mm/dd/yyyy + person's name. All of this is concatenated and is recognized as a text value. Then I want to convert all of these characters to a number. Since each character has an ASCII code, I would like to find ASCII equivalent.
In Excel, this would involve using something like the CODE ( ) function, but that only gives the numerical equivalent for the first character in the string, but I want all characters at once.
When I try the VAL ( ) function, it returns the first character which is the number of the month.
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.
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"
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).
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 RelatedMS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
How can I convert a Date/Time field into a Date field?
View 4 Replies View RelatedI am trying to do date calculations, but when I generated my query to run the report off of I lost my formating and all my cells turned to text. I have tried to search this problem down and it seems that I could use the CDate function to correct my cells, but I am unsure how to use it.
My query (Report General Query) is based on two separate queries (General Protocol Query Pull Dates 1-9 and 10-60). It appears that the formatting is correct in the two separate queries, but turns to text when combined into the final query.
How do I correct this and where do I need to do this? In the Report General Query?
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.
I am using CDate to convert a date text string into a date value, which is then listed in ascending order in the result.
If I leave the default format as 'dd/mm/yy' the data is in the correct date ascending order when the query runs but if I use -
Format(CDate(etc, etc),"dd mmm yy")
The query is in alpha/numeric order as if the value is a text string and not a date value.
Not entirely sure why it is doing this when all that I have changed/added is the 'Format' function.
This function gets the last working day (Mon-Fri Are Deamed Working Days For Invoicing purposes) of a month adds it to a table then gets the next until it has done the Number indecated In "txtMonths", It works with a UK Date Format but not with a USA Date Format.
Is there A Better way of doing this or does anybody have an idear as to how I could correct it for UK and US Date Formats
Y = Year(Me![txtStartDate]) 'Get Added Year
M = Month(Me![txtStartDate]) 'Get Added month To Keep Track Of year Change
D = Weekday(Me![txtStartDate], vbMonday) 'Get Day of week and set to monday
On Error Resume Next
For C = 1 To Me![txtMonths]
DDt = CDate(1 & "/" & M & "/" & Y) 'Set to first of the month
Ld = LastOfMonth(DDt)
Nd = CDate(Ld & "/" & M & "/" & Y)
'StrSch = Ld & "/" & M & "/" & Y
CWd = Weekday(CDate(Ld & "/" & M & "/" & Y), vbMonday)
'Now If the date falls on a sat or sunday move the day back to friday
If CWd > 5 Then
If CWd = 6 Then D = 1
If CWd = 7 Then D = 2
Nd = CDate(Ld & "/" & M & "/" & Y) - D
End If
AddSchedule Nd
'Debug.Print C & " | " & StrSch & " | " & CWd & " | " & Nd & " | " & D
M = M + 1
If M > 12 Then
M = 1 'Only 12 Months in a year
Y = Y + 1 'Gotta be a new year Happy New Year
End If
Next C
Edit: Function used to get the last day of a given month
Function LastOfMonth(InputDate As Date) As Integer
' Return a date that is the last day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer
If IsNull(InputDate) Then
LastOfMonth = Null
Else
D = Day(InputDate)
M = Month(InputDate)
Y = Year(InputDate)
'find the first day of next month, then back up one day
LastOfMonth = Day(DateAdd("m", 1, DateSerial(Y, M, 1)) - 1)
End If
End Function
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.
Thanks in advance!
Hi there,
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!
Many thanks in advance!!
Sasha.
Hi,
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.
Grateful for advice!
I have a query to create in access based off a query already created in SQL
The SQL query converts a 4 digit number into a date using this code:
CAST(CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN RIGHT(OPDT, 2) ELSE LEFT(OPDT, 2) END + '/' + '01' + '/' + CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN LEFT(OPDT, 2) ELSE RIGHT(OPDT, 2) END AS SMALLDATETIME)
OPDT is a digit number in text format. The function converts 9606 into 1996-06-01
What is the access method in order to this?
I have this linked table query from a OBDC and I need to be able to filter out specific dates in that query. The dates in the table were in text format and I converted the dates using the CDate function. I wanted to filter the query to a single date and always I get the Datatype mismatch in criteria expression error.
However, filtering dates does work only when there are other specifications in the criteria fields (e.g. if I specify a date and and name). My SQL code in error looks like this:
SELECT
purch_hist.PUITM AS ITEM,
purch_hist.PUPO AS PO,
purch_hist.PUQTY AS QTY_RECEIVED,
CDate([purch_hist.PURDT]) AS RECEIPT_DATE,
itmcnt.ITBYR AS BUYER,
purch_hist.PUCST AS UNIT_COST,
vendor.NVNO AS VENDOR NO,
[code]...
This query works fine with a non converted date field, however the dates I need are in text format and need to be converted since I do not have permissions to edit the tables.
I am importing an excel text field into a table. I am then appending the data to another table. I am having a hard time with the Cdate function
Excel Date Field:
YYYY.MM.DD HH:MM:SS
Example Value:
2014.04.21 18:24:30
My Query Code:
NewDate: CDate(Left([DateQ],4)+"/"+Mid([DateQ],6,2)+"/"+Right([DateQ],2))
My Query Result:
4/30/2014
What it should be:
04/21/2014
I am building a database using data imported from Excel workbooks. The dates in the workbooks are formatted as text in the YYYYMMDD format. Is there a way to convert this into date format during the import or after? I am pulling in a lot of different workbooks and trying to avoid having to reformat each individual workbook prior to importing them.
View 3 Replies View RelatedI 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?
View 1 Replies View RelatedI 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?
View 4 Replies View RelatedI have the following SQL Statement which returns the desired result to me
Code:
SELECT tbl_p_stats.i_closing
FROM tbl_p_stats
WHERE (((tbl_p_stats.i_weekending)=[forms]![frm_a]![i_olddate]) AND ((tbl_p_stats.i_sacat)=[forms]![frm_a]![wfid1]) AND ((tbl_p_stats.i_complexity)=[forms]![frm_a]![wfc1]));
And am now trying to convert it to a Dlookup to provide the default value to a control on form Loading. The statement i came up with is
Code:
= DLookup("[i_closing]", "tbl_p_stats", "[i_weekending] = " & i_olddate & " AND [i_sacat] = " & wfid1 & " AND [i_complexity] = " & wfc1)
However, it is showing the control (wf1oi) as a blank control now. When I run the dlookup through the immediate window, the returned result is Null.
A little more detail
- All the mentioned controls live on the same form (frm_a)
- The control that this Dlookup is going into is called wf1oi