very very urgent !!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to craet a new text box in my form to disply the value of
"count" of the products sold by "John" for example in a date that's equal to
the date's form,putting in consideration that the query from which i want to
display the value looks like the below one, I tried to do it many times but i
failed ,please help me it's very urgent.
One more thing,i don't know how can i get the answer so if
possible,could you send it to me at (e-mail address removed)

Best Regards
Date product type responsible
26/08/2006 Mother board Asus John
26/08/2006 Mother board Super Tom
27/08/2006 monitor Asus John
28/08/2006 hard desk Super John
28/08/2006 hard desk Asus Tom
29/08/2006 mouse Super John
30/08/2006 key board Asus John
 
controlSource -->
=dcount(
"*",
"Table or Query name",
"Date_fieldname =#" & nz(me.date_controlname) & "# AND responsible ='" &
nz(me.responsible_controlname) & "'"
)

you should not use DATE for a name -- it is a reserved word

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Kindly find below the exact query from which I'd like to display the
value of the text box ,so I'd like to assign a text box for each shift to
display the value "total" for each skill using the date of the main form.

Thanks a lot for your quick reply

Date Shift Skills Total
01/08/2006 9:00 TSLM 1
01/08/2006 12:00 PA 2
02/08/2006 9:00 TSLM 1
02/08/2006 9:00 PA 3
03/08/2006 10:00 PA 2
04/08/2006 10:00 TSLM 1
05/08/2006 16:00 TSLM 2
 
Hi Pietro,

this is the ControlSource for a textbox on a form or report

I have added line breaks to make it easier to read

you will need to make substitutions for:
Table or Query name
Date_fieldname
date_controlname
responsible_controlname

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Hi Pietro,

if you want the calculation in a query instead of a calculated control
on a form, you can do this:

on the grid:

field --> CountSkills: dcount( ... )

There are more efficient ways to do this

for example, you can make a totals query grouped by Date and Responsible
and count the records

ie:

Name --> qCountSkills

SELECT Date_fieldname, Responsible, Count(*) as CountSkills
FROM Tablename

Then, in your query where you want this info, add qCountSkills and link
on the date and responsible fields. Then put CountSkills on the grid

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
TOOOOOOOOOOOOOOO many thanks for you "strive4peace"
but it seems that you got me wrong ,I'll try to be more clear :

what should i write in the controlsource of the text box on a form to disply
the total of the agents whose skills is "TSLM" and shift "9:00" on the date
that equals the Form's date ,based on the below query

Date Shift Skills Total of agents
Thank you again
 
Hi Pietro,

you can modify the dCount function for different criteria... I gave you
the logic, you need to understand it ;)

What is the data type for Shift?

if your form is continuous or datasheet, then you should put the
calculation in your query since a form can only display one value in an
unbound control

The example I gavce you uses Date and Responsible for the conditions to
match. If you have other conditions, you can modify the criteria

"Date_fieldname =#" & nz(date_fieldname) & "#"
"Text_fieldname ='" & nz(text_fieldname) & "'"
"Number_fieldname =" & nz(number_fieldname)

use AND to concatenate the conditions

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
oops!

me. is used in the code behind the form... not in a ControlSource

the condition should be:
Date_fieldname =#" & nz(date_controlname)
& "# AND responsible ='" & nz(responsible_controlname) & "'"


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top