Hi All,

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,

Lundy

Try the following expression:

Expr1: Format(CDate([YourFieldName]),"mmmm yyyy")

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.

Thanks,


Lundy

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.

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.

Lundy

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?

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.

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.

I will package it up and email it to you.

Thanks

Do not email it to me. WinZip the file and upload the WinZip File.

So, here it is.

Thanks for the help.

Lundy

What is the name of the Query that you want me to look at?

MACOMMkTblTotalCombined

Lundy,

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?

RAZMaddaz

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.
IIF(IsDate([DateString]),CDate([DateString],Null)

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.

it contains no real data so have at it.

John,

That worked like a charm.

Thanks,

Lundy