Count records and show total on a form

[fenhow]

Hi,

I am trying to develop a form that will show the total number of records based on a criteria defined in a query.

On my form I have a unbound label called "Open Orders" beside that I want to show the total number of "Open Orders"

Each record in the database has a option group, it is 1=Open, 2=Closed and so on.

I created a totals query and isolated the option group "Status" to only show items in status "1" it then pulls all the records with a status "1". In the totals row I placed "Count"

On my form I added a txtbox with the control source CountOfStatus from the query however it is only showing a "1" not the total count?

Can anyone help?

Thanks!

Fen How

 

[fenhow]

Ok,

I found this and it seems to work;
=Count([Status])

However how can I differentiate this by staus versus isloating the status in the query, becuase I want to show All Open Orders, All Closed Orders etc...

Thanks.

Fen

 

[statsman]

=Count([Status]="Open")
=Count([Status]="Closed")

 

[fenhow]

Thanks,

This seems to work

=DCount("[Status]","Order Details","[Status] = '1'")

How can I add one that takes the date of the order [AssnDate] and adds 30 days?

So I can show all orders in status 1 that are 30 days and past?

Thanks a ton!

Fen

 

[statsman]

Do you wish to show the orders that are more than 30 days old. You would use:
DateDiff()

If you wish to show a date 30 days in advance of AssnDate you would use:
DateAdd()

 

[fenhow]

Thanks,

This is what I have so far

=DCount("[Status]","Order Details","[Status] = '1'" DateDiff("d",[AssnDate],>30()))

It is giving me an invalid syntax error? any suggestions?

Thanks.

Fen

 

[boblarson]

=DCount("[Status]","Order Details","[Status] = '1'" AND DateDiff("d",[AssnDate],[NEED DATE HERE])>30))

 

[fenhow]

Thanks Bob,

On the NEED DATE HERE what date would that be?

Fen

 

[boblarson]

Thanks Bob,

On the NEED DATE HERE what date would that be?

Fen

Which ever date you have that you are needing to calculate the date diff from [AssnDate] to be >30 days. If you are using the current date then you would just change it to:

=DCount("[Status]","Order Details","[Status] = '1'" AND DateDiff("d",[AssnDate],Date())>30))

 

[fenhow]

Great this works however;

=DCount("[Status]","Order Details","[Status] = '1'" And DateDiff("d",[AssnDate],Date())>30)

Counts all records not just the Status 1? Am I missing something?

Fen

 

[boblarson]

Is your status a text field or numeric? If numeric you would get rid of the single quotes.

 

[fenhow]

Thanks,

Its text and a result from a option group. On this code it works perfectly

=DCount("[Status]","Order Details","[Status] = '1'")

This pulls 137 records

=DCount("[Status]","Order Details","[Status] =1" And DateDiff("d",[AssnDate],Date())>30)

This pulls 704 records

 

[boblarson]

I guess I'm down to asking for the db to be posted so I can take a look.

 

[fenhow]

Thanks,

It is a split database and it is very large. Looking at the code it is logical that it should work...

Let me see if I can extract the piece for you to look at.

Thanks

Fen

 

[boblarson]

If you can create a copy and then import the table (at least a portion of it) so that it is there then we can see what happens. I'm just more visual and if I can see a situation and play around with it, a lot of the time I can get it better than trying to mentally visualize it.

 

[fenhow]

Test Zip

Bob,

This should encompass the problem. I appreciate you looking at it.

Fen

 

[boblarson]

Okay, we got it -

instead of DCount use this:

=Sum(IIf([Status]="1" And DateDiff("d",[AssnDate],Date())>30,1,0))

 

[fenhow]

Bob,
That is awesome! Thanks so much... since we are in this together you will note on the example I sent you a few other items I want to count...

Should I use the Sum feature for the rest of them?

Take the next one for an example;

I want to show all Reposessed Orders Status Option 4 that have not been Invoiced [Invoiced] value 0?

Would it be like this?

=Sum(IIf([Status]="4" And [Invoiced]="0"))

Fen

 

[boblarson]

Should I use the Sum feature for the rest of them?

Yeah, I think you should do that.

Would it be like this?

=Sum(IIf([Status]="4" And [Invoiced]="0"))

No, you have to give it a 1 or 0 to sum so it would be:
=Sum(IIf([Status]="4" And [Invoiced]="0"),1,0)

 

[fenhow]

Ok, I am getting a Wrong Number Of Arguments error??

Fen