Dcount

[malform]

My main table is called NEWcompiled, I have fields named "faction", "SPeffect", and "Launcher_ID". I am trying to use Dcount in a query to count up how many entries have a value in "faction" and "SPeffect" that are equal, and a value of "yes" in "Launcher_ID".

Currently my code looks like this:
DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "Launcher_ID = yes")

This indeed counts how many entries have equal values for "faction" and "SPeffect", but then it seems to add that to sum of all the entries that have a yes for "Launcher_id".

Any help would be great, thank you for your time.

 

[DJkarl]

My main table is called NEWcompiled, I have fields named "faction", "SPeffect", and "Launcher_ID". I am trying to use Dcount in a query to count up how many entries have a value in "faction" and "SPeffect" that are equal, and a value of "yes" in "Launcher_ID".

Currently my code looks like this:
DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "Launcher_ID = yes")

This indeed counts how many entries have equal values for "faction" and "SPeffect", but then it seems to add that to sum of all the entries that have a yes for "Launcher_id".

Any help would be great, thank you for your time.

Is the Launcher_ID field text? If so try putting the word yes in quotes i.e.

DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "Launcher_ID = 'yes'")

 

[malform]

Thank you so very much for your response. Unfortunately that doesnt seem to fix my problem.

The "Launcher_ID" is actually a field in the main "NEWcompiled" table itself. Its data type is set to yes/no.

 

[DJkarl]

Thank you so very much for your response. Unfortunately that doesnt seem to fix my problem.

The "Launcher_ID" is actually a field in the main "NEWcompiled" table itself. Its data type is set to yes/no.

hmmm, maybe could you use an SQL COUNT statement?

"SELECT Count(*) AS CountOfFaction
FROM NEWCompiled
WHERE (((NEWCompiled.Faction)=[NEWCompiled]![SPeffect]) AND ((NEWCompiled.Launcher_ID)=True));"

 

[malform]

hmmm, maybe could you use an SQL COUNT statement?

"SELECT Count(*) AS CountOfFaction
FROM NEWCompiled
WHERE (((NEWCompiled.Faction)=[NEWCompiled]![SPeffect]) AND ((NEWCompiled.Launcher_ID)=True));"

At the risk of sounding dumb, where would I put that code? I put it in a field in my query, and it doesnt do anything.

I am very inexperienced in access, but I have come a long way in the past year I have been tinkering with this project. If it would make it easier to understand my problem you may download my database here (it is slightly too large to attach):

http://www.box.net/shared/2s49r2d3za

The NEWcompiled query is where I am trying to make this happen. Please see the ShootbackTEST fields at the beginning of the query.

 

[boblarson]

My main table is called NEWcompiled, I have fields named "faction", "SPeffect", and "Launcher_ID". I am trying to use Dcount in a query to count up how many entries have a value in "faction" and "SPeffect" that are equal, and a value of "yes" in "Launcher_ID".

Currently my code looks like this:
DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "Launcher_ID = yes")

This indeed counts how many entries have equal values for "faction" and "SPeffect", but then it seems to add that to sum of all the entries that have a yes for "Launcher_id".

Any help would be great, thank you for your time.

I noticed you had brackets missing on Launcher_ID:
DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "[Launcher_ID] = yes")

Also, if Launcher_ID is a YES/NO field then:

DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "[Launcher_ID] = True"

 

[malform]

I noticed you had brackets missing on Launcher_ID:
DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "[Launcher_ID] = yes")

Also, if Launcher_ID is a YES/NO field then:

DCount("Faction","NEWcompiled","Faction = '" & [SPEffect] & "'" And "[Launcher_ID] = True"

Thank you for your time. But alas, the code seems to perform exactly the same with or without the brackets. Setting it to "true" or "yes" seems to give the same results either way as well.

 

[boblarson]

Then post your db because a test worked for me in my test db. I want to see exactly what is going on. Apparently we're not asking the right questions to get at the heart of the true cause.

 

[malform]

Then post your db because a test worked for me in my test db. I want to see exactly what is going on. Apparently we're not asking the right questions to get at the heart of the true cause.

Certainly, you can download it here:
http://www.box.net/shared/2s49r2d3za

It seems to be too large to be attached via the forum attachment tool. Its 2.3 meg.

 

[boblarson]

Unfortunately that site is blocked by our web at work. Can you compact (Tools > Database Tools > Compact and Repair) and then use WinZip or WindowsXP, or something, to zip it up and then email it to my home email address? I'll PM you the email address.

 

[malform]

Unfortunately that site is blocked by our web at work. Can you compact (Tools > Database Tools > Compact and Repair) and then use WinZip or WindowsXP, or something, to zip it up and then email it to my home email address? I'll PM you the email address.

Sure thing, Im packing it up as much as I can right now.

 

[boblarson]

Where again exactly was the code that was causing the problem?

 

[malform]

Where again exactly was the code that was causing the problem?

The shootbacktest fields in the newcompiled query is where I was trying to make the magic happen.

 

[boblarson]

Well for one, you can't have a dlookup in a query trying to look up a value from itself. You need to lookup data in a DIFFERENT query or a table. As the code stands right now, your dlookup is trying to find the information within itself and that won't work. That would explain alot.

 

[malform]

Thank you so very much for your help... But I finally got the stupid thing working, like this:

DCount("*","NEWCompiled","(Faction = '" & [SPEffect] & "') and Launcher_ID")