Query Datetime field and Grouping by Date only

[nserrano]

I'm losing my mind on this one. Here is my situation... I receive a daily excel sheet with these fields. I would like to import this workfile into access and would like to manipulate it anyway I want. The problem I'm coming into is that I can't collapse/group the datetime field into just the date when I run a query. I would like to be able to run a query on any date range the client registered, a query on any and all the unique dates the client purchased something, all the unique clients, etc. Here is a sample

Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/4/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/5/2007 18:20
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 10:21
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 01:22 - 8/9/2007 05:46

I would like it to show...

Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/5/2007
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/6/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007

and a different query like ( the purchase date doesn't matter here)...

Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt
Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007
Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007

I read in other places to change the format, input mask, convert, char, etc. but nothing. Please tell me exactly what to do. I don't care if the time changes to midnight or 0:00:00. I will owe you my first born child.

 

[Bilbo_Baggins_Esq]

SELECT Table1.[Sales Name], Table1.[Sales #], Table1.[Client Name], Table1.[Client #], Month([Table1]![Registered Dt]) & "/" & Day([Table1]![Registered Dt]) & "/" & Year([Table1]![Registered Dt]) AS Registered_Date, Table1.[Purchased Dt]
FROM Table1
GROUP BY Table1.[Sales Name], Table1.[Sales #], Table1.[Client Name], Table1.[Client #], Month([Table1]![Registered Dt]) & "/" & Day([Table1]![Registered Dt]) & "/" & Year([Table1]![Registered Dt]), Table1.[Purchased Dt];

This is for the first example you supplied.
Table1 assumed to be replaced the the actual name of your table.
Note: I don't know if you've supplied only the captions of your column names or if these are the actual names, but if they are the actual names, you can get into problems later if you leave empty spaces in the actual names.
If the supplied info from your example is only the captions, then you'll need to replace what I have with the actual column names.

 

[Bilbo_Baggins_Esq]

An extra note.
I did not apply the same method of building the date to the purchased dt column, but the same principal applied to the Registered dt can apply to the Purchased dt.

Here is for the second example:

SELECT Table1.[Sales Name], Table1.[Sales #], Table1.[Client Name], Table1.[Client #], Month([Table1]![Registered Dt]) & "/" & Day([Table1]![Registered Dt]) & "/" & Year([Table1]![Registered Dt]) AS Registered_Date, Min(Table1.[Purchased Dt]) AS [MinOfPurchased Dt]
FROM Table1
GROUP BY Table1.[Sales Name], Table1.[Sales #], Table1.[Client Name], Table1.[Client #], Month([Table1]![Registered Dt]) & "/" & Day([Table1]![Registered Dt]) & "/" & Year([Table1]![Registered Dt]);

 

[Bilbo_Baggins_Esq]

Now, what is the expected delivery time for your first born?

 

[neileg]

Easier way:

A date/time field is stored as a decimal number with the whole number being the date and the fraction being the time. So if you use Int() this gives you just the date. Use CDate() to turn this into a date.

JustTheDate: CDate(Int([Registered Dt]))

 

[nserrano]

IT WORKS!!!! Both of them worked, I decided to opt for the shorten version. Know to make the baby. THANK YOU BOTH!!!!

 

[jasn_78]

hey guys sorry to reopen an old post but i tried this and everything works fine except when i use the option by bilbo which is what i need as i only want to ditch the seconds from a group by command when i do minutes if the minute is under 10 it is only showing 1 digit e.g 9 instead of 09 in the minutes