<div>
Try this:<br />
<br />
CInt(Left([Brth_DT], 2)), CInt(Mid([Brth_DT], 3, 2))), DateSerial(CInt(Right([Brt<wbr />h_DT], 4))<br />
<br />
Greg<br />
<br />
<br />

</div>


<div>
I tried that it says invalid syntax or quotes. &nbsp;Any Ideas?
</div>


<div>
If u want it displayed as mm/dd/yyyy you could try changing the format property on the table design. <br />
subsequently doing format(&quot;mydatefield&quot;,&quot;Shor<wbr />t Date&quot;) will take the format that is defined in your control panel settings<br />
but you can override by doing format(&quot;mydatefield&quot;,&quot;MM/D<wbr />D/YYYY&quot;)<br />
note, only to be done on a date field, not your yyyymmdd field
</div>


<div>
Thanks Rockiroads... it worked perfeclty
</div>


<div>
No probs. Glad to have helped, again!!! :)
</div>


<div>
<div class="content wysiwyg-content">
Microsoft Access 2003<br />
&nbsp;I have a table with a number field [Brth_DT] with dates setup as yyyymmdd. &nbsp;This field is setup with the data type Number.<br />
<br />
I am making a make- table&nbsp;Query to convert this number field to a date field in the new table. &nbsp;I need the new field to be a date field with mm/dd/yyyy.<br />
<br />
What expression do I need to use to get this to work in the Query Design View?<br />
<br />

</div>
</div>


Microsoft Access 2003
 I have a table with a number field [Brth_DT] with dates setup as yyyymmdd.  This field is setup with the data type Number.

I am making a make- table Query to convert this number field to a date field in the new table.  I need the new field to be a date field with mm/dd/yyyy.

What expression do I need to use to get this to work in the Query Design View?



Try this:

CInt(Left([Brth_DT], 2)), CInt(Mid([Brth_DT], 3, 2))), DateSerial(CInt(Right([Brth_DT], 4))

Greg




I tried that it says invalid syntax or quotes.  Any Ideas?

If u want it displayed as mm/dd/yyyy you could try changing the format property on the table design. 
subsequently doing format(&quot;mydatefield&quot;,&quot;Short Date&quot;) will take the format that is defined in your control panel settings
but you can override by doing format(&quot;mydatefield&quot;,&quot;MM/DD/YYYY&quot;)
note, only to be done on a date field, not your yyyymmdd field

No probs. Glad to have helped, again!!! :)

Convert number yyyymmdd to Date/time mm/dd/yyyy  MS Access 2003

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.