Dec 10 2010, 12:25 PM
I am working on query and need to convert a field from text to either date or numeric.
The text field is called processingmonth and contains month and year i.e. November 2010. This is a Make Table Query.
Thanks in advance for any assistance on this,
Dec 10 2010, 12:42 PM
Try the following expression:
Expr1: Format(CDate([YourFieldName]),"mmmm yyyy")
Dec 10 2010, 12:59 PM
I tried your suggestion and it is creating a duplicate field in the table. Not sure if I am doing something wrong. I have limited Access knowledge.
Dec 10 2010, 01:05 PM
QUOTE (lundy @ Dec 10 2010, 12:59 PM)
duplicate field in the table.
What do you mean a duplicate Field? What type of Query are you creating? Instead of having the Field in the Query and the expression I mentioned before, just have the expression, to convert the Text Field to Date Format.
Dec 10 2010, 01:26 PM
I appreciate the help.
I am running a make table query pulling data from another DB. The original field "processingmonth" is text (NOVEMBER 2010). I removed processingmonth from my query and replaced it with the expresssion that you sent me. The Property sheet format defaults to General Date. When I run the query, I get a field called Expr1 with the same text data as the original. So what am I doing wrong?
You will have to forgive me, but I am pretty new at all of this.
Dec 10 2010, 01:36 PM
You didn't say that you were creating a make Table Query. In the Make Table Query, do not include the original Field that the expression I mentioned before is replacing, otherwise like you said you will the see the same data twice. Also, change the Expr1 part of the expression to what you want to name the Field. Understand?
Dec 10 2010, 01:43 PM
Got it. I did as instructed. I have attached 2 screenprints that show my query setup and then results. I did run this again changing Expr1 to ProcessingDate with the same results.
Dec 10 2010, 01:53 PM
If you can make a backup copy of your database and with the backup copy, delete all private data, I will take a look and make the changes for you. Just let me know which Field you are trying to convert from Text to Date.
Dec 10 2010, 01:56 PM
I will package it up and email it to you.
Dec 10 2010, 01:58 PM
Do not email it to me. WinZip the file and upload the WinZip File.
Dec 10 2010, 02:31 PM
So, here it is.
Thanks for the help.
Dec 10 2010, 02:45 PM
What is the name of the Query that you want me to look at?
Dec 10 2010, 02:47 PM
Dec 10 2010, 03:05 PM
When I changed the MACOMMkTblTotalCombined to a Select Query without making changes to the date Field it returns no records. Does that make sence? Therefore, if I change it back to a Make Table, it will delete everything in the table MACOMMkTblTotalCombined and return no records. What do you want me to do?
Dec 10 2010, 03:26 PM
To Convert a string like "November 2009" to a date use CDate([DateString]). That will return the date value for 1 November 2009.
To avoid errors I would use an expression like the following in a query.
If your date string has two year dates (November 09) then you need to concatenate a 1 plus a space to the beginning.
IIF(IsDate("1 " & [DateString]),CDate("1 " & [DateString]),null)
If you fail to add the "1 " to the beginning the conversion will be to the month and day of the current year.
Dec 10 2010, 03:51 PM
it contains no real data so have at it.
Dec 10 2010, 03:55 PM
That worked like a charm.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here