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
> Separate Date From Time In Query, Access 2016    
 
   
startingover0001
post Sep 18 2019, 02:55 PM
Post#1



Posts: 233
Joined: 13-November 10
From: 37745


Can't seem to find what I'm looking for in other posts.

I have a table with a time/date field called "EditDate". I use 2 combo boxes for date parameters (start and end date). I'm querying the dates in the table to eliminate the issue of "guessing" on potential dates. Naturally, there will be many many records with the same date but different times, even if only by a second or two, so of course each would be considered a "unique" value. My combo box is formatted the way I want but I don't need times...only dates.

StartDate: Format([EditDate],"Medium Date")

Setting unique values to yes will still show many records for the same date. How do I format out the time portion so that only the date itself is all that gets read so that finally the unique value will show only ONE of that date WITHOUT utilizing a sub-query? (Not even sure a combo box can utilize a sub-query).
Go to the top of the page
 
theDBguy
post Sep 18 2019, 03:26 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,379
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you're talking about for the Row Source of your Combobox, then you could try something like:
CODE
SELECT DISTINCT DateValue([DateFieldName]) FROM TableName

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
startingover0001
post Sep 18 2019, 04:01 PM
Post#3



Posts: 233
Joined: 13-November 10
From: 37745


This is the way the query currently is:

SELECT DISTINCT USysAudit.EditDate, Format([EditDate],"Medium Date") AS StartDate
FROM USysAudit;

Your suggestion does pretty much the same thing.

To attempt a clarification... a list of dates as saved in the table

9/14/2019 6:32:50 PM
9/14/2019 8:40:11 PM
9/15/2019 7:30:41 PM

the combo box is already formatted to display the way I want. The box should only show

Sept 14 2019
Sept 15 2019
Go to the top of the page
 
theDBguy
post Sep 18 2019, 04:46 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,379
Joined: 19-June 07
From: SunnySandyEggo


Hi. So, if it's already doing what you want, could you please clarify what part you needed help with? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
startingover0001
post Sep 18 2019, 04:51 PM
Post#5



Posts: 233
Joined: 13-November 10
From: 37745


The formatting was not the problem...notice there are 3 dates in the table...2 dated 9/14 and 1 dated 9/15. The combo box should only show 1x 9/14 and 1x 9/15. As it is, it shows all the dates, not distinctly selecting. I'm thinking it may be best to just change the way the data is saved in the table to begin with. The time is not particularly important.
This post has been edited by startingover0001: Sep 18 2019, 04:52 PM
Go to the top of the page
 
theDBguy
post Sep 18 2019, 05:02 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,379
Joined: 19-June 07
From: SunnySandyEggo


Hi. If I'm understanding your situation, removing the time component from the table will not help. Do you have a primary key assigned in your table? If so, try adding it to the dropdown as a bound column.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post Sep 18 2019, 05:19 PM
Post#7


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


What is your SQL for the combo box? You might be able to have a query that has something like
SELECT DISTINCT USysAudit.EditDate, Format([EditDate],"Medium Date") AS StartDate
FROM USysAudit

Save this query, and then in the combo box, use a distinct using the saved query as the data source, if that makes sense.

I wouldn't change the table structure, as you might need the time part at some stage, and easier to exclude data than extrapolate data back in.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
startingover0001
post Sep 18 2019, 05:47 PM
Post#8



Posts: 233
Joined: 13-November 10
From: 37745


dbguy - The primary key will kill the idea. I'm not trying to go to a particular record...I'm trying to requery a list box showing all listings for that date.

Wildbird - Your suggestion is exactly what I had in the combo box to begin with. I tried it as a separate query and then queried the combo box off it using unique value but it did no good. Still showed all dates.

When you consider there could literally be thousands of records added/edited in a single day, you don't want to look at all 1000 Sept 14 2019...you only want to see it once, query the date and let the listbox show them all. Once I get this part right, I'll even be querying by field name, but that's another chapter.
Go to the top of the page
 
GroverParkGeorge
post Sep 18 2019, 06:00 PM
Post#9


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


You're not going to get this to work as you want, I'm afraid.

SQL
SELECT DISTINCT USysAudit.EditDate, Format([EditDate],"Medium Date") AS StartDate
FROM USysAudit;


Will return ONE record for each value in USysAudit.EditDate, regardless of what you do otherwise.

You CAN get the result you want, though, by limiting the returned fields to those that can be grouped:

SQL
SELECT DISTINCT Format([EditDate],"Medium Date") AS StartDate
FROM USysAudit;


Any additional field you add to this SQL will change the number of rows returned depending on how many unique values it contains.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WildBird
post Sep 18 2019, 06:01 PM
Post#10


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


So you have a query that only shows dates?

Try querying that query with a Grouped query and see if that gets just the dates.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Sep 18 2019, 06:25 PM
Post#11


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Yeah, what George said - dont have any additional fields in there. Try his SQL.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
startingover0001
post Sep 18 2019, 06:32 PM
Post#12



Posts: 233
Joined: 13-November 10
From: 37745


@Wild - if by that you mean use a subquery, I've already tried that and it didn't work either.

@George - It did the same thing...showed all dates. I think you're right...I'm not going to get it to work the way I want using typical techniques. I've always said that microsoft was dumb with it's dates...they should separate dates and times. Can't anybody convince me they couldn't do that.

I think what I'm going to have to do is save the julian date and year as well and query it that way...if I can figure out how to get the julian date.

I appreciate everybody's help.
Go to the top of the page
 
WildBird
post Sep 18 2019, 07:11 PM
Post#13


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Can you post the SQL you are using and what you are is giving the errors (showing multiple dates)


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
GroverParkGeorge
post Sep 18 2019, 07:56 PM
Post#14


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


Okay, I can see you're frustrated, but, frankly, this is not a problem with the way Access has stored dates since version 1.0 back in November of 1992.

I have a very hard time seeing how this SQL can return ALL of the records, including duplicates of days.

SQL
SELECT DISTINCT Format([EditDate],"Medium Date") AS StartDate
FROM USysAudit;


However, I would also like you to try this version, if you will please.

SQL
SELECT DateValue([EditDate]) AS StartDate
FROM USysAudit GROUP BY DateValue([EditDate]);


Same results?


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
startingover0001
post Sep 18 2019, 07:57 PM
Post#15



Posts: 233
Joined: 13-November 10
From: 37745


I've already changed what I had. Now I'm using ordinal (not julian) date and while it's presenting my dates right in the dropdown boxes now, I'm going to have to figure out how to work the SQL formula now. I'll try to post a copy of the table and form tomorrow. I've been working on this today for the last 12 hours. I have to go to work still. Thx again though.
Go to the top of the page
 
theDBguy
post Sep 18 2019, 09:12 PM
Post#16


Access Wiki and Forums Moderator
Posts: 76,379
Joined: 19-June 07
From: SunnySandyEggo


Hi. Good luck with your project. Let us know how it goes...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Sep 19 2019, 07:12 AM
Post#17


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


I would simply ask that you TRY the suggested GROUP BY query with one and only one date field in it. In order to remove the time portion from that field, I would also urge you to use DateValue(), not Format() to do that. Format CONVERTS dates to strings. That means the result you get from using Format() may not be entirely appropriate. For example, it may cause the records to sort differently from your expectations, i.e. sorting on text isn't the same thing as sorting on dates.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WildBird
post Sep 19 2019, 03:06 PM
Post#18


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Agree with George, on both trying to sort and group by. When it comes to sorting date you could use Format() as long as you use yyyymmdd it should work. Using "Medium Date" or anything with text will give issues (unless you convert them back to a date with something like CDate()). I usually name files with datestamp at the start of the name so they can be sorted correctly.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 09:50 AM