Create a field in a select query based upon created fields in the same query

[Brians440]

All,



I'm very new to advanced Access queries and I'm trying to re-create the output from an Excel spreadsheet for a group. I've created two fields and would like to use those two field names to create a third in the same query. Is that possible? Here are the two fields I created.

Fall: IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #8/15/2014# And #12/1/2014#,[HOURS],0)
and
Spring: IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #1/10/2015# And #5/5/2015#,[HOURS],0)

Here's what I came up with in the interim:
Total Hours: IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #8/15/2014# And #12/1/2014# Or DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #1/10/2015# And #5/5/2015#,[HOURS],0)

I would rather have something like "Tot_hours: [Fall] + [Spring]". When I try this I get prompted for the value of "Fall".

Thanks in advance for any help.

Brian

[June7]

Why are you using DateSerial? Is WeekStartDate not a date/time field? How is date value structured?

The references to Fall and Spring should work.

Post the full SQL statement for analysis.

[Brians440]

June7,

Hey. Here is the format the report (.CSV) it comes out of a canned SSRS report, I'm just trying to make the process automated. (They're currently keying this info into Excel.)

EMPLID,EmployeeName,WeekStartDate,Textbox17,HOURS, Textbox3,Textbox8,Textbox21,Textbox30,Textbox27,Te xtbox31,Textbox20
1234567,"Smith, John",07/06/14 Summer,1,9.1,0,0,0,37.5,"2,532.9","103,401.6","2,5 32.9"
1234567,"Smith, John",07/13/14 Summer,2,8.6,0,0,0,37.5,"2,485.9","103,401.6","5,0 18.8"

Here is the query I'm trying to work with/create.

SELECT tblimport_new.EMPLID, tblimport_new.EmployeeName, Sum(tblimport_new.HOURS) AS [Total Year], IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #8/15/2014# And #12/1/2014#,[HOURS],0) AS Fall, IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #1/10/2015# And #5/5/2015#,[HOURS],0) AS Spring, Sum(IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #8/15/2014# And #12/1/2014# Or DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #1/10/2015# And #5/5/2015#,[HOURS],0)) AS [Total Hours], [fall]-[spring] AS [Hours left to work]
FROM tblimport_new
GROUP BY tblimport_new.EMPLID, tblimport_new.EmployeeName, IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #8/15/2014# And #12/1/2014#,[HOURS],0), IIf(DateSerial(Mid([WeekStartDate],7,2),Left([WeekStartDate],2),Mid([WeekStartDate],4,2)) Between #1/10/2015# And #5/5/2015#,[HOURS],0), [fall]-[spring];

Thanks ever so much for you help!

Brian

[Brians440]

June7,

Hey. It appears I had an ID10T error when I was writing the query. I didn't look at all the "HOURS" values. There's blank cells in there. So, thanks for your help in making me dig into the data and question my formulas. I'm glad it was my screw up....

Brian