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: 27
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


UtterAccess Editor
Posts: 17,921
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

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
scewing
post Nov 2 2017, 12:44 PM
Post#3



Posts: 27
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: 31,197
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


UtterAccess Editor
Posts: 17,921
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

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 02:19 AM