SQL output to textbox

[Damob9K]

Hello,

I have searched through this and other forums for an answer to my puzzle, but so far everything I have found is overly complicated compared to what I want to do !!

Basically what I want is to be able to display a total count of a particular product type into a single text box, so that I can have on the form :
You have [number] of product type in stock.
My SQL code works correctly but when I add it to the control source of the unbound text box I get "#Name?" instead.

The SQL statement is:
SELECT Peripherals.PerType, Sum(Peripherals.Quantity) AS SumOfQuantity
FROM Peripherals
GROUP BY Peripherals.PerType
HAVING (((Peripherals.PerType)="Mouse"))
ORDER BY Peripherals.PerType;

Is this the correct way to be putting a SQL statement in the control source ?

Thanks

Damob

 

[Galaxiom]

No. SQL can't be the Control Source for a text box.

Use a DCount.

 

[Damob9K]

Hi Galaxiom,

I looked into DCount but from what I understand is that it counts records only ?

So if I have for example:

Optical mouse - microsoft - 25
Optical mouse - Lenovo - 10

DCount will only return a value of 2 if I use :

DCount("Peripherals.PerType", "Peripherals", "Peripherals.PerType = Mouse")

Whereas the SQL statement will return 35

Or have I got the usage of DCount all wrong ??

Cheers

D.

 

[Galaxiom]

Try something like this:
DSum("quantityfield", "Peripherals", "PerType = 'Mouse'")

 

[Damob9K]

Thanks Galaxiom, that sounds like the right command to use ..

I have tried putting that in as the control source and get the same error.
I have also tried doing it through the expression builder, and specifying the table the record derives from but I can't seem to get the right syntax.

I think I am nearly there,but to be honest with you, I can barely keep my eyes open right now, so will get some sleep and try this again tomorrow evening after work.

Thanks for pointing me in the right direction, hopefully tomorrow I can report back that it is all working

Cheers for now.

D.

 

[Galaxiom]

Did you include an Equal sign before it?

ControlSources that are formulas need that or Access looks for a field with the name and will give the error you mentioned

 

[Damob9K]

thanks Galaxiom that was it.

Proof that doing things when tired is not a good plan !!

Cheers my friend.

Damian