converting numeric date to text date

[Guest]

Hi I'm not sure if this is possible,
but I have list of receipts and receipt date.
for example:
Receipt # Receipt date
123 01/25/2005
324 12/1/2004

is there anyway in sql or query to take receipt date and conver it to say
January 2005? I know you can change the format to to have it MMM YYYY,
however, it is still recognizing as "1/25/2005". I have another table which
receipt date is format as text " January 2005" . I would like to create a
match query with these two, however since one is a text and another is a date
format, the query is coming up as Data Mistype.

Thank you!

 

[Rick B]

Sure. Look at the FORMAT function.


Rick B

 

[John Spencer (MVP)]

It can be done but not using the design view. You need to use the SQL view to
link the tables.

SELECT A.[Receipt#], A.[ReceiptDate], B.[Receipt#], B.[ReceiptDate]
FROM [YourTable] as A INNER JOIN [YourOtherTable] as B
ON A.[ReceiptDate] = Format(B.[ReceiptDate],"mmmm yyyy")

You can create the query in the design view and try linking the two fields and
then switch to the SQL view. Then you can modify the Linking (ON) clause.