How do i count a total of a value using a From and to date.

[Lambden10]

Help Please, (Access 2003)

I have made the expression for my count which i have done successfully (as you can see on my screenshots), but i would like to use a from and to date as we would run this query on a weekly basis.

The idea is to know how many enquiries each of our lawyers have had each week.

Is this possible?
If so could someone help me with the expression please.

Regards
Kyle

[Seth Schrock]

You would use the Between statement on your date field. For example

Between #5/1/2015# And #5/7/2015#

You can also replace the explicit dates with references to controls on a form.

[Lambden10]

Thank you,

would it only pick up the date if it was on the table i was doing the query on.

Bearing in mind i have set up the relationships on my tables.

[Seth Schrock]

As long as the table that has the Date field is included in the query, then you can use it in the criteria.

[Lambden10]

Oh right okay i got it now, thank you for your help its much appreciated.

[Lambden10]

Hi,

Sorry to bother you again, i have tried the expression but i keep getting this error:

"The expression you entered contains invalid syntax"
"You may have entered a comma without a preceding value or identifier"

[Seth Schrock]

Please post the SQL for your query (inside CODE tags) so that I can look at it.

[Lambden10]

SELECT Count("AH") AS [F/E]
   ,Between #2/20/2015# And #2/24/2015# 
      AS Expr1
FROM [Conveyancing Enquiries] 
   INNER JOIN [Conveyancing F/E Totals] 
      ON [Conveyancing Enquiries].ID 
         = [Conveyancing F/E Totals].ID;

It would not let me exit becuase of this error so i have had to take out Between, thats seems to work but i get -1 as the result and this definately isn't accurate.

[Seth Schrock]

The Between statement goes in the WHERE clause and it needs to know what field it is checking. For example, if your date field is called EnquiryDate, your query would look like this:

SELECT Count("AH") As [F/E]
FROM [Conveyancing Enquiries] INNER JOIN [Conveyancing F/E Totals] ON [Conveyancing Enquiries].ID = [Conveyancing F/E Totals].ID
WHERE EnquiryDate Between #2/20/2015# And #2/24/2015#

[Lambden10]

It now looks like this, is this correct?

SELECT Count("AH") AS [F/E]
FROM [Conveyancing Enquiries] 
   INNER JOIN [Conveyancing F/E Totals] 
      ON [Conveyancing Enquiries].ID
         =[Conveyancing F/E Totals].ID
WHERE ((([Conveyancing Enquiries].Date) 
   Between #2/20/2015# And #2/24/2015#));

[Lambden10]

I have ran the query and it does now seem to be pulling through the information i need, i am just going to try a different date just to confirm.

[Lambden10]

Yes all seems okay now once again thank you for your help.

Sorry for being a pain :)

[Seth Schrock]

This is how we all learn :)

[zmbd]

You might want to update your SQL to use parameters instead of having to hard code this all of the time:

Use parameters in queries and reports

and a slightly different viewpoint:
How to use the query by form (QBF) technique in Microsoft Access

and the always useful Allen Browne:
Limiting a Report to a Date Range

and here's a generic example based on a query I run in-house using the form...

SELECT TestDate
   , ReadDate, Resample
   , Passing, TestingResult1
   , LotNo
FROM tableA
WHERE (((ReadDate) 
   Between 
      [Forms]![Form_PullDates]![Text_Start] 
   And 
      [Forms]![Form_PullDates]![Text_End]))
ORDER BY TestDate;

The form has logic behind it to check for proper date format, that start is an older date than end that they two dates are not the same etc...

[Lambden10]

Hi,

I have tried running the expression to count a particular value from a field (F/E Field) i want to calculate how many times a certain F/E has been entered but when i run the expression

Count ("PM")

It just counts how many records are in the table altogether rather than just one individual, is it the expression i am entering wrong or just the way the tables are set up.

Regards
Kyle

[zmbd]

well Count("PM") is only counting the field named PM not specified entries. That would require a conditional clause.
generic example:

SELECT tbl_people.people_sex
   , Count(tbl_people.people_sex) 
      AS CountOfpeople_sex
FROM tbl_people
GROUP BY tbl_people.people_sex
HAVING (((tbl_people.people_sex)="f"));
 

Would you post your SQL again?

[Lambden10]

SELECT Count("PM") 
   AS [F/E]
FROM [Personal Injury Enquiries];
 

[zmbd]

1) Does this give you any help:

SELECT Count("People_sex") 
   AS Wonder
FROM tbl_people
WHERE (((people_sex)="f"));
 

2) It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY important to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[Lambden10]

Hi All,

Thanks for all your help but i did eventually find what i was looking for.

this seems to work and provide me with the info i need.

PARAMETERS [Month Number] Text ( 255 ), [Search Text] Text ( 255 );
SELECT [Personal Injury Enquiries].[F/E], [Personal Injury Enquiries].Date
FROM [Personal Injury Enquiries]
WHERE ((([Personal Injury Enquiries].[F/E]) Like "*" & [Search Text] & "*") AND (([Personal Injury Enquiries].Date) Like "*" & [Month Number] & "*"));