Form Textbox count where date is between 2 dates
- Thread starter chris-uk-lad
- Start date
chris-uk-lad
Registered User.
- Local time
- Today, 11:04
- Joined
- Jul 8, 2008
- Messages
- 271
gah, still no luck!!
Would it be possible to send you the test database, ive been using to work this problem, out in a PM? Maybe you may have some luck assisting when you have it as this seems to be baffling :x
Would it be possible to send you the test database, ive been using to work this problem, out in a PM? Maybe you may have some luck assisting when you have it as this seems to be baffling :x
chris-uk-lad
Registered User.
- Local time
- Today, 11:04
- Joined
- Jul 8, 2008
- Messages
- 271
Its a fresh build with test values created specifically to find the root of the problem, so should be ok.
Last edited:
Chris
I couldnt open the database, but your problem is a conversion problem
Date() returns a date but by putting it outside your sql and then enclosed by ## you mess it up...
The same goes for Dateadd... try something like:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] >= DateAdd('d', -7, Date)")
or format your date to "fix" it:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] >= #" & format(DateAdd("d", -7, Date), "mm/dd/yyyy") & "#")
By putting the date outside of the quotes you are allowing/requesting access to convert date() into a string and then back into a date using the ##, allowing Access to go "wild" on you.
Never do anything Implicit if you can avoid it.
Also, you really shouldnt use spaces in your names anywhere... but I guess I told you that one before.
I couldnt open the database, but your problem is a conversion problem
Date() returns a date but by putting it outside your sql and then enclosed by ## you mess it up...
The same goes for Dateadd... try something like:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] >= DateAdd('d', -7, Date)")
or format your date to "fix" it:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] >= #" & format(DateAdd("d", -7, Date), "mm/dd/yyyy") & "#")
By putting the date outside of the quotes you are allowing/requesting access to convert date() into a string and then back into a date using the ##, allowing Access to go "wild" on you.
Never do anything Implicit if you can avoid it.
Also, you really shouldnt use spaces in your names anywhere... but I guess I told you that one before.
I am using Access 2007.
I have tested what you sent and made a few tweeks.
1) In the carried in from previous week
2) In the received this past week
This seems to work for what I think you want! I am not sure where u want the = Date-7 ... it is currently in both...take it out of the appropriate one.
Namliam, I tried your example...it returned 0 when it should have returned 5.
I have tested what you sent and made a few tweeks.
1) In the carried in from previous week
Code:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] <= #" & DateAdd("d",-7,Date()) & "#")
Code:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] <= #" & Date() & "# AND [Date Received] >= #" & DateAdd("d",-7,Date()) & "#")This seems to work for what I think you want! I am not sure where u want the = Date-7 ... it is currently in both...take it out of the appropriate one.
Namliam, I tried your example...it returned 0 when it should have returned 5.
Last edited:
chris-uk-lad
Registered User.
- Local time
- Today, 11:04
- Joined
- Jul 8, 2008
- Messages
- 271
appreacciate you both taking a look, but still isnt right and im sure its getting annoying now for you magic lol.
I want Carried in to show as 5 (take place within the week prior to today)
I want Recieved to show as 2 (the remaining 2)
Having used your amended lines, it shows to me as 0 for carried, 7 for received. Is this what you got Magic? If not then im really confused :x
I want Carried in to show as 5 (take place within the week prior to today)
I want Recieved to show as 2 (the remaining 2)
Having used your amended lines, it shows to me as 0 for carried, 7 for received. Is this what you got Magic? If not then im really confused :x
when calculating with days and dates you dont have to use dateadd...
Also double converting the date, from date(add) to string (Concatenate) to date (##) is NOT a good idea....
1) In the carried in from previous week
2) In the received this past week
Using the dates in a table from september 1 up to and including today:
?DCount("[SomeDate]","table1","[SomeDate] <= Date() AND [SomeDate] >= Date() - 7")
8
?DCount("[SomeDate]","table1","[SomeDate] < Date() - 7")
2
The date conversions are SYSTEM SPECIFIC, that is why it could be working for one, while not for the other. Keeping the date(s) clean of conversions will prevent these problems. This should work IMHO.
Also double converting the date, from date(add) to string (Concatenate) to date (##) is NOT a good idea....
1) In the carried in from previous week
Code:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] <= Date() - 7")
Code:
=DCount("[Request Type]","Sheet2","[Request Type] = 'PLA (New)'" & " AND [Date Received] <= Date() AND [Date Received] >= Date() - 7")Using the dates in a table from september 1 up to and including today:
?DCount("[SomeDate]","table1","[SomeDate] <= Date() AND [SomeDate] >= Date() - 7")
8
?DCount("[SomeDate]","table1","[SomeDate] < Date() - 7")
2
The date conversions are SYSTEM SPECIFIC, that is why it could be working for one, while not for the other. Keeping the date(s) clean of conversions will prevent these problems. This should work IMHO.
chris-uk-lad
Registered User.
- Local time
- Today, 11:04
- Joined
- Jul 8, 2008
- Messages
- 271
maybe something i should have mentioned at the start lol. im using Access 97
chris-uk-lad
Registered User.
- Local time
- Today, 11:04
- Joined
- Jul 8, 2008
- Messages
- 271
Seems thats the solution i was after, it is indeed providing the correct count for all records.
However i cannot get the Request Type to work with it, just shows all records still instead of speciying where [Request Type] = PLA(New). Am sure its just a syntax error on my part.
=DCount("[Date Received]","Sheet2","[Date Received] < Date() - 7" AND "[Request Type]='PLA (New)'")
However i cannot get the Request Type to work with it, just shows all records still instead of speciying where [Request Type] = PLA(New). Am sure its just a syntax error on my part.
=DCount("[Date Received]","Sheet2","[Date Received] < Date() - 7" AND "[Request Type]='PLA (New)'")
You are right Chris !
Yes it is... your AND should be inside the "where" clause.... Removing the offending quotes should fix the problem for you.
=DCount("[Date Received]","Sheet2","[Date Received] < Date() - 7" AND "[Request Type]='PLA (New)'")
Yes it is... your AND should be inside the "where" clause.... Removing the offending quotes should fix the problem for you.
=DCount("[Date Received]","Sheet2","[Date Received] < Date() - 7" AND "[Request Type]='PLA (New)'")
chris-uk-lad
Registered User.
- Local time
- Today, 11:04
- Joined
- Jul 8, 2008
- Messages
- 271
Thats it, all done, thank you all so much for your efforts, especially Magic whom ive probably given a migrane and Namliam who offered the final solution 
namliam, thanks for helping...
Microsoft in their help advocate the use of date functions.
They also advocate translation for comparisons.
They do this because of the way they store the date ... the functions and translations remove the time (decimal) component when it is present. Using straight math, and testing a date set using =Now() (including the time) will not work properly since the time makes Sept 7 > Date() on Sept 7. (39699.1513340215 is > 39699).
Your solution is correct but should be used with care.
Microsoft in their help advocate the use of date functions.
They also advocate translation for comparisons.
They do this because of the way they store the date ... the functions and translations remove the time (decimal) component when it is present. Using straight math, and testing a date set using =Now() (including the time) will not work properly since the time makes Sept 7 > Date() on Sept 7. (39699.1513340215 is > 39699).
Your solution is correct but should be used with care.
I know how dates work, I know about the decimals and time and stuff...
If [date received] is a " date() " then one should use < Date() if it is a "now()" then it all depends on the requirements.
M$ Can advocate everything they want, my advice/use is based upon experience and practicality. Why do Dateadd("D",-7,date()) when Date() - 7 works the same and is much easier.
Also they may advocate traslation for comparisons, but EXPLICIT translations and NOT implicit ones....
I.e.
Implicit: " [Date Received] <= # " & Date() & "#"
Explicit: " [Date Received] <= # " & format(Date(), "MM/DD/YYYY") & "#"
Or even more to the extreem, though over the top probably:
Explicit: " [Date Received] <= Dateserial(" & format(Date(), "YYYY,mm,dd") & "#"
But... why for heavens name would one convert a date to string to date??? Doing that is like bringing water to the sea...
IMHO this problem would be solved by:
" [Date Received] <= Date() - 7 "
The best because you are doing no conversions what so ever.
Then IF you feel you must... then atleast use something like:
" [Date Received] <= # " & format(Date(), "MM/DD/YYYY") & "#"
because it will explicitely FORCE access to do it right, if you dont you get headaches like this thread!
I hope none of the above is considered offensive or anything, I am trying to be constructive but know I can be a bit headstrong at times.
Please dont be offended ...
If [date received] is a " date() " then one should use < Date() if it is a "now()" then it all depends on the requirements.
M$ Can advocate everything they want, my advice/use is based upon experience and practicality. Why do Dateadd("D",-7,date()) when Date() - 7 works the same and is much easier.
Also they may advocate traslation for comparisons, but EXPLICIT translations and NOT implicit ones....
I.e.
Implicit: " [Date Received] <= # " & Date() & "#"
Explicit: " [Date Received] <= # " & format(Date(), "MM/DD/YYYY") & "#"
Or even more to the extreem, though over the top probably:
Explicit: " [Date Received] <= Dateserial(" & format(Date(), "YYYY,mm,dd") & "#"
But... why for heavens name would one convert a date to string to date??? Doing that is like bringing water to the sea...
IMHO this problem would be solved by:
" [Date Received] <= Date() - 7 "
The best because you are doing no conversions what so ever.
Then IF you feel you must... then atleast use something like:
" [Date Received] <= # " & format(Date(), "MM/DD/YYYY") & "#"
because it will explicitely FORCE access to do it right, if you dont you get headaches like this thread!
I hope none of the above is considered offensive or anything, I am trying to be constructive but know I can be a bit headstrong at times.
Please dont be offended ...
No offense, just wanted to point out that "IF" the date was created using Now() then that statement does not work. I actually read you comment and changed my current development code, removing the #'s from the dates in my Select's. I then tested, and the results were all wrong. I tried a number of things but finally had to go back to using the #'s. My database dates are full date/time created using Now() in Access 2007.
All this due to the wondeful way Microsoft decided to store dates.
Smiles
Bob
