Query Results Driving Me 2 Drink!

[rjf3123]

Have a display form that presents global, YTD & MTD stats.

One of the fields is TotalRefund. For the global total this isn't an issue because there is data (numbers to add up). For MTD, when there is no values (no refunds Month-to-date), I get a #error in the field. When I change the calendar on my PC to a month where there is data, the screen works fine so I know my code is alright.

I created a select query (using the query wizard) to add the TotalRefund by Month, which works ok. I created a subform with one field called [Msum Of Refund]. As I said, when I change the calendar on my PC to a month where there is data, everything appears fine.

I have tried using the Nz function everywhere I can possibly think of from the query to the subform to the main display form with no luck.

Any ideas?

TIA R. Fisher

 

[namliam]

Sounds like there is something going on with Null values somewhere thus the Nz will problably be your saviour....

Cant really say how or why.... Maybe if you post a small sample...

Regards

 

[rjf3123]

Not sure how to post an example but let me give this a shot & see if it works, working from the display form backwards, here is what I have:

Form: SummaryStatistics (the screen where I am displaying the info).

TextBox Name: MTDRecovered (the field on the screen)

ControlSource: =Forms!SummaryStatistics![MTD Stats subform].Form![MSum Of EstRefund] (The subform on the SummaryStatistics screen, using subforms because I am getting data from a variety of places throughout the app.

SubForm Name: MTD Stats Subform
SubForm TextBox Name: MSum Of Est Refund

Form Record Source: SELECT [MTD Recovery].[MSum Of EstRefund] FROM [MTD Recovery]; (the FROM [MTD Recovery] is a query].

Query Design:
Field: MSum Of Est Refund: EstRefund
Table: CheckDetails
Total: Sum
Show=Yes

Field: Format$(ClientTracking.InvoiceDate,'m')
Total: Group By

Field: Format$([ClientTracking].[InvoiceDate],'m')
Total: Expression
Criteria: DatePart("m",Date())

When I run this query, nothing is displayed under MSum Of Est Refund UNLESS there is data in the tables, then everything works just fine.

Does this help?

R. Fisher

 

[namliam]

I was more so looking for a table example or maybe an exerpt from your db which you could post....

For 1 thing: DONT use spaces in table/query/field, whatever names..... *sigh* -- i sometimes feel like getting a tape recorder -- bad practice and will get you into trouble sooner rather than later

Then Format$ will not allow you to handle Nulls Format$ returns a string value whereas Format returns a variant. Variants can contain nulls where strings may not.

Then again. Why use both Format$ and Datepart for the same purpose? Some would argu that one is better than the other, but all will tell you using both is NOT a good idea. Stick to 1, my latest "teachings" have tought me to use DatePart....

If that dont help, make a copy of the DB empty the table and put is some dummy data. Delete all but the table/query and form involved, compact, zip and post on the forum....

I am sure it can be done....

Regards

 

[rjf3123]

Namliam -
Apologize for the "non-reply" to this post, don't want to come across as ungrateful for the assist, the email notification for this went into my "junk mail" folder for some reason.

One thing I am learning through this project is not to rely so heavily on the Access wizards. They are the ones responsible for the spaces and $format statements in the query.

Going through another part of this project, I got a little more fluent in using DCount and DSum, which now I am starting to rethink using those functions to accomplish what I am trying to do here.

Perhaps you can steer me in the right direction ...

If I am using DCount to count all invoices between 1-1-2003 and 1-31-2003, my function looks like [InvoiceDate]>=#1-1-2003# AND [InvoiceDate]<=#12-31-2003#

Can I use DatePart somehow here to specify the current month or current year?

Thanks again for the reply to this - appreciate it!

R. Fisher

 

[namliam]

Yes you can or use functions like
Year
Month
Dateserial


Regards

 

[rjf3123]

I'll give it a shot -

Thanks!
R. Fisher