UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount Returning False Value, Access 2007    
 
   
mmadani
post Dec 30 2017, 11:04 AM
Post#1



Posts: 302
Joined: 27-June 08



Happy new year Friends;

I am using a Dcount to fill in values in a matrix. Each value is working fine except for one .

in an almost identical query the results come out okay : =DCount("[ID]","qry_open_SPRs_16_Locations","[sub_system] Like '*TCFU*'")

Now I copied this to another variable to capture count in an added criteria and I get zero results. No errors at all. the attached Matrix is an image that I overlay values on . I put that in the summary of the report . I can also draw it manually and insert the values in each matrix cell to be calculated. Notice the TCFU value calculates fine until I use the following :

=DCount("[ID]","qry_SPRs_Line_EW","[sub_system] IN ('*FIMS*','*SFU*','*TCFU*','*WIFI*','*TCSS*')"), then no value is calculated for the count !!!!


qry_SPRs_Line_EW and qry_open_SPRs_16_Locations are almost identical.

qry_SPRs_Line_EW:

SELECT DISTINCT Format([record_date],"mm-dd-yy") AS Shortdate, SPR_Log.id, SPR_Log.name, SPR_Log.State, SPR_Log.substate, SPR_Log.CR_reference, SPR_Log.sub_system, SPR_Log.StationID, SPR_Log.CR_category, SPR_Log.CR_type, SPR_Log.headline, SPR_Log.severity, SPR_Log.submitter, SPR_Log.description, SPR_Log.ID1, SPR_Log.implementer, SPR_Log.implementation, SPR_Log.validation_description, SPR_Log.impacted_items, SPR_Log.proposed_change, SPR_Log.product, SPR_Log.submitter_date, SPR_Log.Closure_date, SPR_Log.record_date, IIf([record_date]="",Null,DateDiff("d",CDate(Left([record_date],InStr([record_date],"GMT")-1)),Date())) AS Age, IIf([Age] Between 0 And 29," Less than 30 days",IIf([Age] Between 30 And 60,"30 - 60 days",IIf([Age] Between 60 And 90,"60-90 days",IIf(IsNull([Age]),"No Age",IIf([Age]>90,"Over 90 days"))))) AS bucket, SPR_Log.Primary_Block, SPR_Log.Secondary_Block, SPR_Log.submitter_CR_reference, SPR_Log.Comments
FROM SPR_Log
WHERE (((SPR_Log.StationID) In ('E250','EEI','E240','EEK','EE2','EEY','E237','E235','EEX','E230','EEA','EXE','EXW','E220','EED','E210','EEE','EZE','E147','EEZ','E140','EEC','E130','EEM','E120','EEG','E110','EEU','EFX','WFF','E100','W110','WWT','W120','WWV','W130','WWA','WPX','P210','WPB','WW2','WXM','W210','WWW','W220','WWH','E*')))
ORDER BY SPR_Log.id;



The second query looks like this : qry_open_SPRs_16_Locations

SELECT SPR_Log.id, SPR_Log.name, SPR_Log.State, SPR_Log.substate, SPR_Log.CR_reference, SPR_Log.sub_system, SPR_Log.StationID, SPR_Log.CR_category, SPR_Log.CR_type, SPR_Log.headline, SPR_Log.severity, SPR_Log.submitter, SPR_Log.description, SPR_Log.ID1, SPR_Log.implementer, SPR_Log.implementation, SPR_Log.validation_description, SPR_Log.impacted_items, SPR_Log.proposed_change, SPR_Log.product, SPR_Log.submitter_date, SPR_Log.Closure_date, SPR_Log.record_date, IIf([record_date]="",Null,DateDiff("d",CDate(Left([record_date],InStr([record_date],"GMT")-1)),Date())) AS Age, IIf([Age] Between 0 And 29," Less than 30 days",IIf([Age] Between 30 And 60,"30 - 60 days",IIf([Age] Between 60 And 90,"60-90 days",IIf(IsNull([Age]),"No Age",IIf([Age]>90,"Over 90 days"))))) AS bucket, SPR_Log.Primary_Block, SPR_Log.Secondary_Block, SPR_Log.submitter_CR_reference, SPR_Log.Comments
FROM SPR_Log
WHERE (((SPR_Log.State) Like "Closed") AND ((SPR_Log.StationID) In ('E250','EEI','E240','EEK','EE2','EEY','E237','E235','EEX','E230','EEA','EXE','EXW','E220','EED','E210','EEE','EZE','E147','EEZ','E140','EEC','E130','EEM','E120','EEG','E110','EEU','EFX','WFF','E100','W110','WWT','W120','WWV','W130','WWA','WPX','P210','WPB','WW2','WXM','W210','WWW','W220','WWH','E*')))
ORDER BY SPR_Log.id;


In any retrospect, the count of the TCFU or any sub_system should produce something greater than Zero.

Why are there not any values returned for that particular count ? I am stumped pullhair.gif pullhair.gif

Thanks

Mike

Attached File(s)
Attached File  Matrix.JPG ( 53.94K )Number of downloads: 3
Attached File  subsystem.JPG ( 54.87K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Dec 30 2017, 11:36 AM
Post#2


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


QUOTE
=DCount("[ID]","qry_SPRs_Line_EW","[sub_system] IN ('*FIMS*','*SFU*','*TCFU*','*WIFI*','*TCSS*')")

Why are there not any values returned for that particular count ?

Hi Mike: You are not getting values because you cannot use the * approach in the IN operator values. Access is looking literally for the * in the field, not treating it as "Anything". The * approach is valid in Like, but not IN.

So you will have to address these limits another way....

One way is ...

"[sub_system] Like '*FIMS*' OR [sub_system] Like '*SFU*' OR ... etc ...

... or possibly some other way.

Test the Like OR approach and see if it gives you what you want. Then you can look for another approach if that is appropriate.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mmadani
post Dec 30 2017, 11:44 AM
Post#3



Posts: 302
Joined: 27-June 08



Thanks Joe.

I think you got it. I am testing now..

Mike

This post has been edited by mmadani: Dec 30 2017, 11:56 AM
Go to the top of the page
 
RJD
post Dec 30 2017, 11:47 AM
Post#4


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


You are welcome, Mike. Let us know how your test turns out - and if you find another alternative approach.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mmadani
post Dec 30 2017, 11:57 AM
Post#5



Posts: 302
Joined: 27-June 08



Hello Joe

=DCount("[ID]","qry_SPRs_Line_EW","[sub_system] IN ('FIMS','SFU','TCFU','WIFI','TCSS')") ****************This one did not work

But

=DCount("[ID]","qry_SPRs_Line_EW","[sub_system] Like '*FIMS*' OR [sub_system] Like '*SFU*' OR [sub_system] Like '*TCFU*' OR [sub_system] Like '*TCSS*' ") ..WORKS YAY


Many thanks Joe. Resolved pompom.gif

Mike
Go to the top of the page
 
RJD
post Dec 30 2017, 01:19 PM
Post#6


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


You are welcome, Mike. Glad you got that working.

The IN approach you posted just above will not, of course, work since you need to look inside the field for a character set that is embedded. IN compares the value you indicate with the entire field.

This once again points out the problem with how your data are arranged, with so many items embedded in longer strings rather than in their separate fields, as good db design would suggest. But I understand you may not have control over what you receive to process. This arrangement suggests you might see if the data supplier can change the layout you receive, or if you can write a routine to better format the data before you begin your processing. Certainly when I see you still using IN operators in your WHERE statements that have so any elements, and frequently digging out embedded codes, it causes me concern about your design.

Good luck with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mmadani
post Dec 30 2017, 02:47 PM
Post#7



Posts: 302
Joined: 27-June 08



Thanks for your advise and input Joe.
I am currently trying to reach an agreement with the Data originator to clean the database so that I can struggle less with it.

and I am removing as much as I can the use of the IN and NOT IN methods

Happy New Year to you and the UA.

Cheers

Mike fundrink.gif

Go to the top of the page
 
RJD
post Dec 30 2017, 03:10 PM
Post#8


UtterAccess VIP
Posts: 7,977
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

And a Happy New Year to you too, Mike!

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th January 2018 - 02:43 AM