Form Textbox count where date is between 2 dates

[chris-uk-lad]

Hi,

In a form i have a cell that counts the number of new business that belong to a request type, which i have done by including within the cell:

=DCount("[Request Type]","Sheet1","[Request Type] = 'New Business'")

However i would like it to count the number of new business that has arrived in upto this day last week and cant seem to find a way to use standard SQL in the control source.

Thanks

 

[MagicMan]

Before the closing bracket just add
& "' AND [ArrivedDate] > #" & dte???DaysAgo & "#" & "' AND [ArrivedDate] < #" & dteSevenDaysAgo & "#"
Use help for the date function to set dte???DaysAgo and dteSevenDaysAgo

 

[chris-uk-lad]

After reading that current dates in VB are Now, Date, and Time i tried as you suggested.

=DCount("[Request Type]","Sheet1","[Request Type] = 'New Business'" & "' AND [Date Received] < #" & Date() & "#")

But this gives a #Error? error, guessing that its returning thr wrong value, not the number jobs

 

[MagicMan]

It all depends on the format of Date Received. If it is stored as a date, then make Date ... Date(). If it is stored as text length 10, like I store dates that I will have in SQL where statements (dates are a nightmare to retrieve in date format since the date no matter how it is displayed is stored as 8 digits date, decimal point, 8 digits time and the 8 digit date is a number (number of days > 1990 i believe)) then remove the #'s and use Date().

 

[chris-uk-lad]

the Received Date on my database is in standrad Date/Time format (short length)> im digging away at the syntax but cant put anything together to give a result. Date() definately matches my database's date so thinking its because of the relation to the New Business count.

 

[stopher]

After reading that current dates in VB are Now, Date, and Time i tried as you suggested.

=DCount("[Request Type]","Sheet1","[Request Type] = 'New Business'" & "' AND [Date Received] < #" & Date() & "#")

But this gives a #Error? error, guessing that its returning thr wrong value, not the number jobs

An extra single quote has crept into your solution. Try:

=DCount("[Request Type]","Sheet1","[Request Type] = 'New Business'" & " AND [Date Received] < #" & Date() & "#")

hth
Chris

 

[chris-uk-lad]

That worked a treat, damn extra comma!!

While on it, is there a way to count between Received Date and Current Date minus a week, without creating a new column in the original database?

 

[MagicMan]

Current date less 7 days = DateAdd("D",-7,date()) but I do not understand your request. Date received is a table date that differs for each record, is it not?

 

[chris-uk-lad]

I have 2 form fields, one that works out jobs that have come in upto this time last week, the second works out the jobs that have come in since then (for this week)

 

[MagicMan]

First form

=DCount("[Request Type]","Sheet1","[Request Type] = 'New Business'" & " AND [Date Received] < #" & DateAdd("D",-7,date()) & "#")

Second Form

=DCount("[Request Type]","Sheet1","[Request Type] = 'New Business'" & " AND [Date Received] >= #" & DateAdd("D",-7,date()) & "#" & " AND [Date Received] < #" & date() & "#")

 

[chris-uk-lad]

Well the first line works great , the second seems like it should all values are being returned as 0, even one i created specifically to test so think the date maybe wrong.

 

[MagicMan]

Hi,
I just tested the expression on a Date field in my database. It worked fine. Check the table definition of the date, it may be a text field like I mentioned earlier.
Bob

 

[chris-uk-lad]

ive just used a sample of data from my database to check this, im sure theres a problem somewhere cause the numbers are wrong. On running the expression but using it as greater than todays date (of which there should be nothing), it returns as 6.

=DCount("[Request Type]","Sheet1","[Request Type] = 'PLA (New)'" & " AND [Date Received] > #" & Date() & "#")

After some modding of the table, the following are the 6 it returns, the last 6 entries into the table. Why???

Date Received
19/08/2008
20/08/2008
21/08/2008
21/08/2008
22/08/2008
27/08/2008​

 

[MagicMan]

Hi,
If you take a copy of the the table, change [Date Received] field to Number, format Double, you should see the date looks like this ... 39697.1509259259. The 39697 is the number of days since Jan 1, 1900. The 1509259259 is the time. The [Date Received] > #Date()# would translate to if 39687.1509259259 (27/08/2008) > 39699.1003259259 (date now) and would count as false or 0 in the dcount for the last record of the 6 in your example. (I may be off a day or 2 with the number 39699 but the concept is still correct). If Dcount is returning the value of 1 for that date, then the database field is not Date format! Let me know the result when you change it to number.
Smiles
Bob

 

[chris-uk-lad]

Result from changing to number/double:
(only 5 values, deleted the oldest of the 6 to try something, bottom newest/top oldest)

Date Received
39,680.00
39,681.00
39,681.00
39,682.00
39,687.00​

 

[MagicMan]

It looks like you have a valid date in the database, and the Dcount appears correct. I really do not understand how those entries are getting counted.

 

[chris-uk-lad]

Seems the expression for Received is incorrect also. I created a new table of 5 dates, only 1 being in the past week, the rest being several months back, and used the expression suggested before:

=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] >= #" & DateAdd("d",-7,Date()) & "#" & " AND [Date Received] < #" & Date() & "#")

Yet still shows all 5, where it should only show one as being used in the past week. If your struggling i dont know how i have a chance lol :x

 

[chris-uk-lad]

looks like it could be something with DateAdd as ive just tried using

=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] >= #" & DateAdd("d",-7,Date())

and it changes to

=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] < #" & Date() & "#")

**EDIT, and experimenting with DateAdd("d",-7,Now() as seems to show different results**

 

[MagicMan]

You still haven't given me an answer on the table definition for [Date Received].
Is it Date/Time or Text?
The results from converting your dates to Double show .00 in the decimal(time) component, meaning these are not true Access dates, but dates with the time stripped, either by VB code or reformating.
To work with dates,
1) you must know exactly what format the date is stored.
2) you must compare accounting for that format EG: database date 39699.1501023456 is greater than 39699 or dbdate of Sept 2 is greater than Sept 2
Or
You can use the appropriate Functions EG: DateValue, DateAdd, DateDiff, DatePart, #date#, Date(), Date$, Date, Now(), Time(), etc.
Another annoying component to dates, if stored as text, is the format, since 8/25/08 (MM/DD/YY) but 25/8/08 (DD/MM/YY) is not a valid date. A date stored as text as 8-25-2008 does not match 8/25/2008 but does match #8/25/2008#. The Date stored as Date format displayed as 8/25/2008 is greater than #8/25/2008#. Enough with the examples.....the point, when dealing with dates, you must know the source format or convert the date to a format that will accomodate the function you wish to perform.

 

[chris-uk-lad]

the Received Date on my database is in standrad Date/Time format (short length)

i was in the bleief that you knew this hence why we trield the decimalisation of the date.