UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Reformat Date Changes Data Type, Access 2010    
 
   
scewing
post Nov 2 2017, 11:58 AM
Post#1



Posts: 34
Joined: 2-June 17



I'm using a due date in a report with a query as a source. My goal is to use a due date (which exists in the query's table as a date data type) in a form with it's format as "Jan 2018."
Also, I want it to be labeled as "Overdue" if the due date is past the current date.

I made another calculated field in the query like so:
DueDate: IIf([dteNextDueDate]<Date(),"Overdue",Format([dteNextDueDate],"mmm yyyy"))

This gives me exactly what I want. HOWEVER...when I sort the data in the report, it sorts it alphabetically because it sees that date as text.
So I get an order like: Apr 2017, Apr 2018, Apr, 2019, Jan 2017, Jan 2018, Jun 2017....etc. instead of Jan 2017, Feb 2017, etc...

If I just used the original date in the report (as a data type of date), I don't get the option of "mmm, yyyy" as a format property for that field.

Any ideas how I can pull this off, or am I just stuck?
Go to the top of the page
 
doctor9
post Nov 2 2017, 12:19 PM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


scewing,

Remember, the query for a report is NOT going to dictate the sort order of the data. Instead, you should use the Grouping and Sorting dialog box. Just don't sort on DueDate. Sort on dteNextDueDate instead.

Hope this helps,

Dennis
Go to the top of the page
 
scewing
post Nov 2 2017, 12:44 PM
Post#3



Posts: 34
Joined: 2-June 17



doctor9

Worked perfectly! Thanks very much.....again!
Go to the top of the page
 
GroverParkGeorge
post Nov 2 2017, 03:08 PM
Post#4


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


Just a bit of background.
"...alphabetically because it sees that date as text."

Actually what happened here is that you converted it to text using the Format() expression. That's one of the tricky things about Format().
Go to the top of the page
 
doctor9
post Nov 3 2017, 08:58 AM
Post#5


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


scewing,

George makes a good point. I try to avoid using Format() inside a query, and prefer to use the format property of the textbox displaying the date instead.

Hope this helps,

Dennis
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 01:28 AM