pulling queries off of same queries

[kplatt]

Hello, I was wondering if it is possible to pull a query from an expression already in the same query. (or even in a different query) I dont know the formating to do this.

Thanks

 

[pbaldy]

I'm not clear on what you're trying to achieve. You can refer to a calculated field by its alias within the same query:

MadeUpField: Price * Quantity
AnotherOne: MadeUpField * Whatever

 

[kplatt]

Thanks for getting back to me. I tried what you suggested and cant seem to get it to work. Here is my scenario

In one query i have below in one collumn
Quarter: DatePart("q",[SDate])

Then I try to use this in another collumn
IIf([Quarter]=1,"01 01",[Quarter])

So basically what i want is, if Quarter comes back as 1, I want to show 01 01 in the ran query.

Thanks

 

[pbaldy]

I just tested that and it worked fine. What error are you getting? One caveat to that method is that you can't use an alias name in a criteria.

 

[kplatt]

can you screen shot what you did so i can copy it? kevin.platt@ngc.com. Thanks

 

[pbaldy]

This is it in SQL view:

SELECT dbo_tblReservations.ReqDateTime, DatePart("q",[ReqDateTime]) AS Quarter, IIf([Quarter]=1,"01 01",[Quarter]) AS Whatever
FROM dbo_tblReservations;

 

[namliam]

This is it in SQL view:

SELECT dbo_tblReservations.ReqDateTime, DatePart("q",[ReqDateTime]) AS Quarter, IIf([Quarter]=1,"01 01",[Quarter]) AS Whatever
FROM dbo_tblReservations;

Not all versions of access handle this properly, using Aliased columns again in the same query... generaly is not to be adviced, as it can get rather messy rather quick...

SELECT dbo_tblReservations.ReqDateTime, DatePart("q",[ReqDateTime]) AS Quarter, IIf(DatePart("q",[ReqDateTime])=1,"01 01",[Quarter]) AS Whatever
FROM dbo_tblReservations;

or

SELECT ReqDateTime, Quarter, IIf(Quarter=1,"01 01",[Quarter]) AS Whatever
FROM ( SELECT dbo_tblReservations.ReqDateTime, DatePart("q",[ReqDateTime]) AS Quarter
       FROM dbo_tblReservations) as Xyz;

Either should work, but why make q1 to be 01 01?? Will you then not have a problem with Q2? What is going on in a bit more detail?
Perhaps format or some other function could help you?

 

[kplatt]

My goal is to have a report show the beginning and end date of each quarter that a specified sampling dates lands in. For example I do water sampling monthly and my query takes the avg and max of some characteristics of those samples. I need the report to show the begginning and end of each quarter for all dates that land in that quarter. For example, if i did sampling once in jan, once in feb, and once in march. My query and report shows the aggregate avg, max, and min of all three months. But i can not get the date to show start of the quarter and end of the quarter. I would like it to show on the report as

01 01 09 03 31 09

Hopefully that helps clarify what i need. I have not been able to figure it out so the origninal question that I sent you was my back door attempt to accomplish the same thing,.

Thanks

 

[namliam]

This:
dateadd("M",-1 * (month(adate)-1) mod 3 , aDate - day(aDate)+ 1 )

Will make from any date the first of any quarter, use it in any query or function...
Background:
Function dateadd adds or substracts months to a certain date
aDate - day(aDate)+ 1, will make any date into the first of the same month
-1 * (month(adate)-1) mod 3, will calculate what month of the quarter we are in and substract the needed months.


This
dateadd("M",3 - (month(adate)-1) mod 3 , aDate - day(aDate)+ 1 )-1

Will find any last date of any quarter, use it in any query or function...
Added background:
-1, this will without the -1 calculate the first of the next quarter, doing -1 will make it the last of this quarter.

Hope this is more or less clear?? You can then format this date which ever way you see fit....

 

[kplatt]

Yo man you are awsome. Thanks for the help I really appreciated it. I was stuck for a while. Thank you very much it worked. Any ideas of what i could purchase to get better at the expressions/sytax/code.

KEvin

 

[namliam]

No purchase needed, a creative brain, will to learn, google, a nice forum (AWF) and persistance is all that is needed IMHO. Dont give up so easily, which IMHO is what you did (dont be offended).
You have a problem, work towards the solution in small steps, like I did.
1 - From any date go to the first of the month
2 - now find a way to go back to the first of a Q.
3 - Now reverse it and find the last

Your problem though may be in dates themselves, before you start 'working' with more complex dates like this, try and understand what is a date, what is an hour, minute, month, second etc... Once you have a better feel for that, you will be (much) better equiped to tackle any problem related to calculating any date.

Ex: Try and search how to calculate someone's age, the code is on here someplace and see if you can understand it in detail.

FYI: I am 100% full self tought.