Dcount Multiple Criteria

[gray]

Hi All

Windows XPPro
Access 2002/2007

Would someone help me with the syntax of a Dcount please? The following example data in my Artists_To_Tracks table....

ID = autonumber
Artists_Unique_No = long
Tracks_Unique_No = long
Record_Status = text

ID Artists_Unique_No Tracks_Unique_No
335 6 23
380 8 23

The Record_Status columns are all blank

The following Dcount does not return 2 when Me!unique_no = 23? (me!unique_no is from the Form's recordset)

Artists_Count = Nz(DCount("Artists_Unique_No", "Artists_To_Tracks", "[Tracks_Unique_No] = " & Me!Unique_No & " AND [Record_Status] <> 'Remove'"))

Tried all sorts... argggh!

thanks

 

[Galaxiom]

[Record_Status] <> 'Remove' only tests for values that are not null.
You need to extend the test to include Null values.

You should also consider changing the Status values to integers and using a lookup when you want to display the text. Numbers are far quicker for Access to process.

 

[gray]

Hey thanks OZ!

Must say the NULL thing with strings drives me to utter distraction... !

And thanks for the tip on using numbers for Record Status... In this particular project I display the status to the user so retro-fitting it would be a nightmere. IIF( record_status = 1,"Remove") etc statements all over the shop but i'll certainly use it in future projects.

Incidentally, this is how I coded it for anyone else out there...

Artists_Count = Nz(DCount("Artists_Unique_No", "Artists_To_Tracks", "([Record_Status] IS NULL AND [Tracks_Unique_No] = " & Me!Unique_No & ") OR ([Record_Status] <> 'Remove' AND [Tracks_Unique_No] = " & Me!Unique_No & ")"))

I did it this way because Record Status can have many other values which I do want to include in the count.

Seems to return the correct results.....

Thanks

 

[Galaxiom]

And thanks for the tip on using numbers for Record Status... In this particular project I display the status to the user so retro-fitting it would be a nightmere. IIF( record_status = 1,"Remove") etc statements all over the shop but i'll certainly use it in future projects.

Normally this isn't done with an iff but a combobox. The numeric field is used as the bound column while the text field is displayed. When used in a report the drop down arrow is not displayed.

The easiest way to set up the database for this is to set the a Lookup on the field in the table. Adding that field to a form will then automatically set up the combo as a bound control in this style.

However, do remove the lookup from the table after you have made your forms as they are not a good thing to have in a production database.

 

[gray]

Yes, I like the combobox idea...far more elegant than my IIF statements... thanks for a great tip....

cheers