Format date from SQL Linked Table to Access 2007 1

[MICKI0220]

I have moved a table from an Access program to an SQL 2008 database because of field restrictions. I then linked the table to my ACCESS database and when I run the reports the date format has changed to a YYYY-mm-dd format. The users of the report want it to look like it did with the mm/dd/yyyy format. When I try to change the format in the report properties the drop down is blank and it doesn't utilize anything I type in it such as mm/dd/yyyy. Can someone lead me in the right direction?

 

[genomon]

Set the format in the text box property sheet.

Ni neart go cur le cheile.

 

[MICKI0220]

That's where I put the mm/dd/yyyy. It usually gives me a choice like general or standard etc, but it comes up blank and then I type in the mm/dd/yyyy and nothing changes. It stays in the box, but the fields still print out in yyyy-mm-dd format.

 

[SkipVought]

I would question that that is a Date Value. Behaves like it is only a string value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[genomon]

How about setting the recordsource to =Format([yourfield],"mm/dd/yyyy") ?

Ni neart go cur le cheile.

 

[MICKI0220]

In the SQL 2008 administration, the data type is "date

 

[MICKI0220]

I tried formating the control source and it gives me an "error" in the field of the report now.

 

[genomon]

I created an unbound text box and populated it with this code in the Detail_Print() event and it works:

txtTestDate = Format([test_date], "m/d/yyyy")

Had to have a bound textbox with the field test_date as control source and visible set to false to be able to use the field name in VBA.
Don't know if this approach is practical for you or not, though.

Ni neart go cur le cheile.

 

[dhookom]

When you view the Record Source in datasheet view, is the un-named date value left or right aligned?

When you check the design view of the linked table in Access, does it show Date?

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

It's actually centered

In Access the data type is coming over as a text field

 

[dhookom]

Centered? Apparently their is some formatting that is being applied :-(

If it shows as text in the design view, then I would confirm the data type on the SQL Server and then relink. If it still comes up as Text, you will need to convert it to a date in the report's record source using:

NewUnNamedDate:CDate([un-named date field])

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I got it working, what I did was this. I went into SQL and changed the data type from date to datetime and now it is coming over to Access in a date data type. Everything is working again. Thanks for the insight.