I’m stuck Need help

[alfred67]

Hello

I’m really stuck here. I'm fairly new at access

In my table “items” are paid everyday. At the end of every month I have to produce a report showing which “items” were paid for that month (example February).

My problem is I still have to show the “Items” that where paid in other months (Jan,March) as Zero or not paid this month in the report

How do I do this in a query/report?

Alfred67

 

[namliam]

You will need a "payed date" then the query is easy, anything that is payed last month or not payed yet... ( Null )

 

[alfred67]

Hello

I do have a "Paid date" but I'm not sure on how to write my Criteria in the Query. I have to show this months pay items and previous paid item as zero or not paid this month.

Alfred67

 

[Brianwarnock]

As you want to show ALL items it is not the criteria that you need to address.

Lets assume that your fields are FAmount and FDate

AmountpaidThisMonth: IIf(Month(Fdate)=Month(Date()),Famount,0)


Month(Date()) might be something else Say = [entermonth] where the month requiied is entered as a number.

Brian

 

[alfred67]

Thank you very much worked like a charm

Alfred67

 

[alfred67]

I forgot to ask Is there away to get all Amounts paid on previous months

AmountpaidPreviousMonth: IIf(???????)


Thanks

Alfred67

 

[alfred67]

Never mind figuired it out

 

[namliam]

Lets assume that your fields are FAmount and FDate

AmountpaidThisMonth: IIf(Month(Fdate)=Month(Date()),Famount,0)

This has a big draw back, as you will also select Month 2 from any year (last year(s)) ...

Also it will simply show all records instead of only those you need.

Continuing the assumption of FDate... you want something like:
Where ( Fdate >= Date() - Day(date()) +1
and Fdate < Dateadd("M",1, Date() - Day(date()) +1) )
OR Fdate is null

 

[Brianwarnock]

This has a big draw back, as you will also select Month 2 from any year (last year(s)) ...

Also it will simply show all records instead of only those you need.

You're possibly correct about the year, depending on his data, however he does want all of the records and his last post changed his requirements on what he wanted to do with them. Infact I'm a bit confused about his requirements.

Brian

 

[alfred67]

Hello

Thanks for the help Guys This is how I got it to work

Quantity This Period: IIf([date]>=Forms![start date]!cbotype And [date]<=Forms![end date]!cbotype,[Payment Quantity],0)

Thanks
Alfred67

 

[Brianwarnock]

Well I'm glad its working but that does not appear to be the question we were asked.

Brian

 

[Rich]

What is [date] in the query? It looks like a field

 

[alfred67]

Yes, Date is a field. I was worried about the year too. So I have a pop up start date and pop up end date. Which gives me day,month and year.

Seems to work good

Alfred67

 

[alfred67]

Well I'm glad its working but that does not appear to be the question we were asked.

Brian

Yes it was ?? I used your IIf statement, Date was just mod it to include day,month, year

Alfred67

 

[rainman89]

FYI Date is a reserved word in access.. I would change your fieldname to somethign else..

 

[alfred67]

FYI Date is a reserved word in access.. I would change your fieldname to somethign else..

Sorry I didn't realize that. Thanks for the heads Up

Alfred67