DCount within VBScript in Access

[Wildster]

Hi,

I'm having a little trouble with some VB code within Access.

I have the following code which assigns a DCount to an integer: -

intStore = DCount("[Training_ID]", "[tblTrainingGiven]", "[Training_Renewal_Date] <=Now() AND [Training_Renewed?] =0")

It basically counts the number of records which have a Training_Renewa l_Date which is previous to todays date. This works fine, although I am trying to take it further by getting it to only bring back people who are Still_With_Comp any which is a tick box, I'm trying to say only return the count for the code above if the records counted have a tick on the field Still_With_Comp any.

The Still_With_Comp any filed is stored in the table "tblPeople" which is linked to the table "tblTrainingGiv en" via the People_ID field.

Any help would be much appreciated.

Thanks

[servantofone]

The Still_With_Comp any filed is stored in the table "tblPeople" which is linked to the table "tblTrainingGiv en" via the People_ID field.

Well you seem to have control over the DCOUNT function in VBA. Have you created a query that merges the information in the linked tables? Base your DCOUNT function on a query and then add the criteria for the checkbox.

-Kyle

[Wildster]

Hi, how would I add the criteria for the checkbox, would it be as simple as using another AND statement at the end? i.e.

intStore = DCount("[Training_ID]", "[qryTrainingGiven]", "[Training_Renewal_Date] <=Now() AND [Training_Renewed?] =0 AND checkbox = true")

Thanks,

Well you seem to have control over the DCOUNT function in VBA. Have you created a query that merges the information in the linked tables? Base your DCOUNT function on a query and then add the criteria for the checkbox.

-Kyle

[servantofone]

Hi, how would I add the criteria for the checkbox, would it be as simple as using another AND statement at the end? i.e.

intStore = DCount("[Training_ID]", "[qryTrainingGiven]", "[Training_Renewal_Date] <=Now() AND [Training_Renewed?] =0 AND checkbox = true")

Thanks,

That's right. Don't forget the brackets around the name of the checkbox.

DCount("[Training_ID]", "[qryTrainingGiven]", "[Training_Renewal_Date]<=Now() AND [Training_Renewed?]=0 AND [theCheckBox]=TRUE")

-Kyle

[Wildster]

Sorted it, just added the AND statement at the end of the script I previously had, and also used a query to refer to the Still_With_Comp any checkbox from a linked table as you advised.

Many thanks for the point in the right direction.

That's right. Don't forget the brackets around the name of the checkbox.

DCount("[Training_ID]", "[qryTrainingGiven]", "[Training_Renewal_Date]<=Now() AND [Training_Renewed?]=0 AND [theCheckBox]=TRUE")

-Kyle