Date Format

B

Blackops

Registered User.
Local time
Today, 11:16
Joined
Mar 23, 2011
Messages
14
Hello,

I'm hoping there is a way to do what I'm hoping can be done.

I have import'ed several records from another sever which have defaulted the date records to the following format YYYYMMDD (text format). My question is, can I change that to show as a date. I would like for it to only show month and year.

Currently showing: 20110315

Want to show something like: March 2011 or 03 2011

Thank you
 
Is there no way you can format it as a date in the format yyyy/mm/dd

It will be much easier to get the March 2011 format.

If that's not possible, here's the answer to your question:
Code: 
format(dateserial(left([COLOR=Red]"20110315"[/COLOR], 4), mid([COLOR=Red]"20110315"[/COLOR], 5, 2), right([COLOR=Red]"20110315"[/COLOR], 2)), "mmmm yyyy")
 
presume that you will replace "20110315" by [yourfieldname]

Brian
 
When I try to enter the format, I'm getting the following error: "Data type mismatch in criteria expression".

Is this because some of the records within the field have nothing in them or have a 0 and nothing else?
 
Am I correct in assuming that this format can be entered within the query to display as stated?

Which of the following formats should I be using. I have tried them all and still get errors:


1.) Format(DateSerial(Left([LRDTE],4),Mid([LRDTE],5,2),Right([LRDTE],2)),"mmmm yyyy")

2.) Format(DateSerial(Left("LRDTE",4),Mid("LRDTE",5,2),Right("LRDTE",2)),"mmmm yyyy")

3.) Format(DateSerial(Left("LRDTE",4),Mid("LRDTE",5,2),Right("LRDTE",2)),"mm yyyy")
 
Blackops said:
When I try to enter the format, I'm getting the following error: "Data type mismatch in criteria expression".

Is this because some of the records within the field have nothing in them or have a 0 and nothing else?
Click to expand...

Yes, that will certainly cause problems, have you attempted to handle this?

Brian
 
Ok, I understand the format provided and see how it return the month and year of March 2011, however, is there a way to have it return the date and month of what ever is within the field. Not all fields are March 2011.
 
Brianwarnock said:
Yes, that will certainly cause problems, have you attempted to handle this?

Brian
Click to expand...


No because the table I"m pulling from is linked from another server and will not allow changes within the table itself.
 
Sorry to keep posting over myself I found the issue. I was entering the information incorrectly. The format worked. Thank you very much for your help.
 
So a lot depends on what you want to do where there is no valid date.
A first instance might be

IIf((LRDTE Is Null OR LRDTE = 0),Null,Format(DateSerial(Left([LRDTE],4),Mid([LRDTE],5,2),Right([LRDTE],2)),"mmmm yyyy"))
hope my syntax is ok!

This will give NULLs or correct dates, you can then use criteria to not select the Nulls if required.

Brian
 
You must log in or register to reply here.

Similar threads

G
Filtering on Date fields problem
Replies
8
Views
752
azhar2006
A
A
FILTER PRINT REPORT
Replies
2
Views
278
arnelgp
arnelgp
G
Again, query where dealing with empty fields
Replies
13
Views
831
theDBguy
theDBguy
L
Solved Transform Columns in multiple rows
2
Replies
20
Views
856
June7
June7
D
How to select most current date by month, day and year
Replies
15
Views
1,198
Design by Sue
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom