text to short date but comes out backwards

[mkelly]

I have a text firld that I am converting to a date with this code in a query.


Hired: Year([POSITIONSTARTDATE]) & "/" & Left([POSITIONSTARTDATE],5)

However my date comes out backwards
2004/01/01

Can I format this correctly?

01/01/2004

Also it will not let me search by a date range.

any ideas???

 

[ghudson]

Hired: Format(Year([POSITIONSTARTDATE]) & "/" & Left([POSITIONSTARTDATE],5),"mm/dd/yyyy")

 

[mkelly]

Thanks that did it one more question if you don't mind.
I am trying to search between dates. In the criteria field I am using the code.

Between [forms]![Report Date Range]![BeginDate] And [forms]![Report Date Range]![EndDate]

however, it only returns the dates that equal the date and month of the first date enty?

for example if i enter
01/01/2000 and 01/01/2005 it will only return people hired on 01/01/of any year

 

[The_Doc_Man]

01/01/2000 and 01/01/2005 it will only return people hired on 01/01/of any year

This would occur if the dates were stored as strings, because in that case, the only things "between" 01/01/2000 and 01/01/2005 would be 01/01/2001, 01/01/2002, 01/01/2003, and 01/01/2004 - AS STRINGS.

Now, if the dates are in date fields, I don't know why that would happen.

You can still recover this but the syntax is just a bit trickier.

Between [forms]![Report Date Range]![BeginDate] And [forms]![Report Date Range]![EndDate]

Change this using CDate function (it's in the Help files) to

Between CDate([forms]![Report Date Range]![BeginDate]) And CDate([forms]![Report Date Range]![EndDate])

And, of course, the search fields would ALSO have to be in or converted to dates as well.

 

[mkelly]

Thank You!

It has been a great help.