• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

MS Access 2010 - Setting the date the way I want.

Hi Experts,

I have this date on the SQL Server:
2008-11-11 00:00:00.000

On the Report of MS Access 2010 I get this:

mm/dd/yyyy

I want it to be:
yyyymmdd

Please help and thanks
0
Amour22015
Asked:
Amour22015
  • 5
  • 4
1 Solution
 
Saurabh Singh TeotiaCommented:
You can use this...

select convert(varchar(12),@field,112)

Open in new window


replace your field with the actual field name..

Saurabh....
0
 
Amour22015Author Commented:
I am new to all this.

I get this value from a Stored Procedure.
So you mean to change it on the SQL Server "SP".

Or do that in MS Access like:
=convert(varchar(12), Name,112

Thanks
0
 
Nick67Commented:
Two choices.
In the query that powers the report, you can format the field fed to the control.
Let's say the field is presently MyDate
Change the field to
MyFormattedDate:Format([MyDate],"yyyymmdd") and bind the control to the new field name MyFormattedDate

or

In the properties of the control, change the Format property.
Try giving it
"yyyymmdd" and see if it will play nice
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nick67Commented:
I get this value from a Stored Procedure.
 So you mean to change it on the SQL Server "SP".


That is a third option if a sproc is in play.
You can change the sproc to return a format value.

BUT

What Saurabh has suggested will change your DateTime data to string data.
Access will no longer treat it as a date for sorting or calculating purposes.
0
 
Amour22015Author Commented:
Ok,

First let me say this is a "Text Box"

It is mentioned:
MyFormattedDate:Format([MyDate],"yyyymmdd") and bind the control to the new field name MyFormattedDate

can you explain in more detail? is MyFormatDate  in the Property Sheet of Access?

I tried:
In the properties of the control, change the Format property.
 Try giving it
 "yyyymmdd" and see if it will play nice
So I placed
"yyyymmdd" in the format section of the Property Sheet.

and all it did was give me:
yyyymmdd
in the field NOT the actual date

Please help and thanks
0
 
Nick67Commented:
Try without the quotes.
Format properties are finicky, which is why I gave them as a second choice.
Look below, though.
format textbox with date format
0
 
Amour22015Author Commented:
That worked thanks for your help
0
 
Nick67Commented:
MyFormattedDate:Format([MyDate],"yyyymmdd") and bind the control to the new field name MyFormattedDate

 can you explain in more detail? is MyFormatDate  in the Property Sheet of Access?


Queries are fun things, in and of themselves.
They take data from tables and other queries and pass it on to other objects.
You do NOT have to pass out the data strictly as the table gives it.
You can transform it in the query.

Let's say you have Table1
Table1 has fields A and B
In a query, you can transform the data to be passed out.
You could create
MyConcat:A&B
and it would pass out A and B concatenated together as a field named MyConcat.

Similarly with you Date field (whatever it is named -- I used MyDate as an example)
Format() is a built-in Access function.
Look it up in the help
Here, we will use it to transform your field (if it was named myDate) to the output you'd desire
Format(MyDate,"yyyymmddd")
Now, we are doing this in a query.
Every field that does not come directly from a table or other query requires a name (alias)
Or Access will assign it an ugly one like Expr01
So we'll give it one, MyFormattedDate
The name ( more properly the alias) is the part before the colon in
MyFormattedDate:Format(MyDate,"yyyymmddd")
Run the query and you will see a column called MyFormattedDate that has your MyDate data in the format you want to see.

Clear enough?
0
 
Amour22015Author Commented:
Ok, so you are mentioning using/creating another query to get the date from my "SP" and format that date using the new query.  That is clear, Thanks
0
 
Nick67Commented:
In the beginning you did not mention that the data was coming from a sproc.
I have tables linked to SQL Server.
For those, you add tables to a query and can manipulate the data before the query outputs it.
The underlying SQL is stuff like

"Select ThisColumn, ThatColumn, Format([MyDate],"yyyymmdd") as MyFormattedDate from SomeTable"
This is quite amenable to manipulation.

A pass-through to sproc is a different monster altogether.
All it has for SQL is

Exec uspWhateverSprocIsOfInterest
And this is not amenable to manipulation in the same way at all!

But if that pass-through query was named, say,  qryMySproc, you could create another query, add qryMySproc as a source table and then manipulate it in the same fashion as a linked table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now