Null Value messing me about

[liftshaft]

I have a number of queries which when referenced through my report and conditions match work fine.

e.g.

SELECT Count([Patient Stats].[Anaesthesia Lip]) AS [CountOfAnaesthesia Lip]
FROM [Patient Stats]
WHERE ((([Patient Stats].Date) Between [Start Date] And [End Date]))
GROUP BY [Patient Stats].[Anaesthesia Lip]
HAVING ((([Patient Stats].[Anaesthesia Lip])="Permanent"));


But if there are no records between a specific date ranging matching the value of "CountofAnaesthesia Lip" returned, I am presuming, is Null.

This means that all queries show up as blank on my form, unless
all match criteria between a specific date range.

Please, does anyone feel like enlightening me?


Thanks in Advance

Chris Dalton

 

[liftshaft]

Sorry, I should also say that I am looking to return zero (0) if criteria doesn't match.

 

[directormac]

Search Access help on the Nz() function. Also, you may find this article helpful.


--Blank Mac

 

[liftshaft]

Please ignore my dumbness

The only thing with this is where would I use the NZ function, in my query or on the report...


From what I have read about NZ it is used exclusively with variables.

Will I pass the information generated by the query to a variable and do it this way or will I reference [CountofAnaesthesia Lip] directly?

I think I may have done it the wrong way around in the first place, as I have text boxes with data control sources referencing the queries directly....

Any chance of an example in relation to my query up above?

Thanks

Chris Dalton

 

[directormac]

Sorry, Chris, just went back and re-read your post and realized you're looking for something a bit more than just "how do I handle a null value". Zip up a copy of your db that has any sensitive data replaced by dummy data and post it here.

--Too-Hasty Mac

 

[liftshaft]

Please see attached cut down version....

contents

Dummy data table (Patient Stats)
2 Reports (Report1 and complications)
A lot of queries....

In this instance, the value of Cons query causes problem, can be demonstrated by changing 1 Cons value (table) to yes.....


Hope this makes sense....

Thanks

Chris

 

[directormac]

... thing's don't seem normal ...

I just d/l'd your sample and will look at it in more detail as soon as time permits. However, you should really consider going back a couple steps and normalizing your tables. You've got info in this one table that ought to be in at least three, maybe more.

Doing so will make your app more flexible, not to mention cut down much of this query forest...

--Abnormal Mac

 

[liftshaft]

Yeah... started off as a small project and grew....

Client is always wrong type of thing.....

Query forest.... thats right...

My first larg(ish) database, so imagine my horror when I tried
to use more than a certain number of queries on on report....

I will get around to tidying it up a bit, just as soon as I can get zero values to be shown as such...

Thanks again....

(Just looked at your profile, this must be fate... we share the same birthday.... Even spookier if it was 1977....)

 

[directormac]

Yeah... started off as a small project and grew....

All the more reason to practice normalized design from the start of every project

Seriously, while I'm sure Pat H. or Jack or Rich or SOMEBODY with more experience than I could untangle the underlying problem in a snap, I'm stuck... just sitting in a corner twiddling my fingers, which only produce that "wipfuh" sound...

Forget Nz(), I couldn't get an unbound control with a datasource of ([queryfield] + 1) to work in some cases!

I will get around to tidying it up a bit, just as soon as I can get zero values to be shown as such...

Hmm... so you're going to invest a heap of time to come up with a hack that works for a structure you know you'll never use again, and THEN go back and fix the design? After which you'll have to solve the problem again anyway, since the hack won't work for correctly designed tables? Not sure I see how that's a time saver...

we share the same birthday.... Even spookier if it was 1977....)

Mine is, sadly, considerably earlier.

Maybe it's senility creeping in, but I don't see an "easy" fix for this. If you want to go back and fix your tables, I'm happy to help, but after about the 4th level of nested query, you lost me on this one. I seriously wish you the best and hope one of the real geniuses around here has the answer. But I don't.

--Failure Mac

 

[directormac]

Here's a quick example of an alternate design... you would have to adapt, then create & run some queries to xfer your data from the old structure, but take a look at the vastly simplified queries here...

--Exemplar Mac

 

[liftshaft]

Thanks Mac....

I like the design, has given me some things to think about,
makes me wonder what I am giong to do to turn around my stabbings in the dark.

My only problem here is that the record is generated based on patients attendance, at which point any of the procedures and or complications can happen.

Also the database is client designed, which was reflected in the price....



Cheers

Chris

 

[directormac]

My only problem here is that the record is generated based on patients attendance, at which point any of the procedures and or complications can happen.

So the "basic" record then becomes an entry in tblVisits, which has a complex PK of fkPatientID and dtmVisitDate. Links to tblProcedures and tblComplications as they arise.

Also the database is client designed, which was reflected in the price....

Of course I don't know all the details, but at this point I'd probably try to renegotiate. Imagine if you were in the moving business and the client said they would provide their own truck. You show up to move them and the truck turns out to be too small (or, more appropriate to this case, it turns out to be too small, underpowered, and having an engine built around a wood-burning stove...). At this point you have to say "umm... sure, I CAN move you in this, but..."

I'd explain to the client that the initial agreement was based on the understanding that he would deliver a WORKABLE design for you to start from. Although you want to honor the agreement, his failure to do so puts you in an awkward position. He has the choice of keeping his design with the understanding that it creates limitations (i.e., no zeros on the reports), or renegotiating the job to include re-designing the structure and importing existing data. Explain that keeping this design will make every future change longer, more difficult, and more expensive--After a certain point, it's cheaper to get a new tire than to keep patching flats.

Just my $.02...

Best of luck--shout out if you need help with a revised design.

--Offering Mac

 

[liftshaft]

Cheers Mac...

I think I will plod on and try to come up with a hack, thanks for the offer but to tell you the truth I am fed up to the back teeth with it (pun intended)....

Thanks for your help

Chris

---Mac a shining beacon in a dark, scary world....All hail Mac

 

[directormac]

Your choice, of course. One final bit of advice--when you finish the scope of the bid/project and a bit of time passes and this person comes back wanting another change...

stick it to 'em!

--Wrathful Mac