Grouping by calculated Field in a Query

[tacoprincess]

Hello, apologies if this has been addressed before (I'm sure it has, but I can't seem to find a thread on it).


I work with images from wildlife cameras, and our goal is to figure out which species visited which cameras per day.

The metadata from my camera labels each ImageDate as a date and time (1/1/16 12:01 AM), but I only care about days, so I am using the expression Left([ImageDate],InStr(1,[ImageDate]," ")-1) which can create a new column with only the Date.

My problem is, I want to write a query that gives me unique species per site per day, but I can't get the query to run. I'm wondering if it is because I can't used a calculated field in a query? I have tried to create a calculated field named "Date" in the table and then query from that and it won't work. I then tried to create a new field ("PhotoDate7" in the picture) and Group By that, but it also won't work.

Any help would be greatly appreciated! I am a novice at Access and this has been a bit difficult.

[ranman256]

in the field
format([imageDate],"mm/dd/yyyy")

or
right click [imagedate] query field, set the format there.

[CJ_London]

dates are numbers, not what you see as a formatted view

if you want to group by the date (i.e. excluding the time element) use the datevalue function

PhotDate7:datevalue(imagedate)


note that date is a reserved word (it returns todays date)

see this link about different date functions

http://www.classanytime.com/mis333k/sjdatetime.html

[tacoprincess]

That worked! Thank you very much for your help!