Field Calculation in query error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Short overview of the set up:
qry1 retrives data--> acct, sale1, date1(mmyy);
qry2_crosstab acct as rowsourse, date as column source, sale1 as sum value
Now I am trying to create next qry that will have month1, month2, month3 as
the colums, but depending on the past month value> Here is what I am using
PM: IIf((Month(Now())-1)=8,[0805],IIf((Month(Now())-1)=9,[0905],"")) but
since october is not here, if I put a future field the jet doesn't recognize
it. How can I go around it, so I don't have to add new field every month?

Thank you

Thanks
 
Rather than column headings like 0905 and 1005, use relative dates. Your
column heading expression would be something like:
ColHead:"Month" & DateDiff("m", [Sale1],Date())
This will create a column Month0 for the current month and Month1 for last
month.
 
thank you I will try that.
--
If at first you don''''t succeed, destroy all evidence that you tried.


Duane Hookom said:
Rather than column headings like 0905 and 1005, use relative dates. Your
column heading expression would be something like:
ColHead:"Month" & DateDiff("m", [Sale1],Date())
This will create a column Month0 for the current month and Month1 for last
month.

--
Duane Hookom
MS Access MVP


kontra said:
Hi,

Short overview of the set up:
qry1 retrives data--> acct, sale1, date1(mmyy);
qry2_crosstab acct as rowsourse, date as column source, sale1 as sum value
Now I am trying to create next qry that will have month1, month2, month3
as
the colums, but depending on the past month value> Here is what I am using
PM: IIf((Month(Now())-1)=8,[0805],IIf((Month(Now())-1)=9,[0905],"")) but
since october is not here, if I put a future field the jet doesn't
recognize
it. How can I go around it, so I don't have to add new field every month?

Thank you

Thanks
Click to expand...
Click to expand...
 
It is not working, since the date1 is a text field not a date field. In my
qry1 i create date1 field by mid function which turns it in to a text field,
if I don't , then it will come in as a decimal number field.

Thank you.
--
If at first you don''''t succeed, destroy all evidence that you tried.


Duane Hookom said:
Rather than column headings like 0905 and 1005, use relative dates. Your
column heading expression would be something like:
ColHead:"Month" & DateDiff("m", [Sale1],Date())
This will create a column Month0 for the current month and Month1 for last
month.

--
Duane Hookom
MS Access MVP


kontra said:
Hi,

Short overview of the set up:
qry1 retrives data--> acct, sale1, date1(mmyy);
qry2_crosstab acct as rowsourse, date as column source, sale1 as sum value
Now I am trying to create next qry that will have month1, month2, month3
as
the colums, but depending on the past month value> Here is what I am using
PM: IIf((Month(Now())-1)=8,[0805],IIf((Month(Now())-1)=9,[0905],"")) but
since october is not here, if I put a future field the jet doesn't
recognize
it. How can I go around it, so I don't have to add new field every month?

Thank you

Thanks
Click to expand...
Click to expand...
 
Thank you sooo much, I converted the data that I originally retrieved and
then used your suggestion and it worked perfectly. Greatly Appreciated.
 
Back
Top