Date Comparison

[Bonaldi]

I have a table with a daily log of info called (big surprise here)
Daily_Log and I have filled it with every date starting last November
until way into 2012

On each record (with the Date as the key field) I have a byte field
with a 1 indicating that a given organisation is trading on that day
and a 0 if not. (i.e. I initialised all the values with 1's and then
zeroed out the weekends and public holidays.)

So, to find out how many "trading" days have occurred between a
"start" and "end" date all I have to do is invoke a dsum of this field
filtering the records between the given dates.

So far, so simple.

IMPORTANT - I am based in the UK and my windows setting reflects this
and Access USUALLY responds accordingly.

i.e. In the UK we write dates in ascending order. Hence, if I set the
format on a text control to "medium date" and enter 01/02/11 Access
will show me "01-Feb-2011" (as opposed to the US version 02-Jan-2011.)

(I stress the "USUALLY" because I have found exceptions to this before
and strongly expect that I am in the same territory again - though I
can't see where!)

Now on my opening menu FORM I want to show the number of trading dates
that have occurred since the first of that month.

Hence I have txtTrading_Date = Date() to give me
today's date
txtMonth_Start_Date =
DateSerial(Year(Date()), Month(Date()), 1) to give me the full date
of the first of the month. These work perfectly.

and the computed number of trading days as:

DSum("[DL_Open]","Daily_Log","[DL_Date] between
[txtMonth_Start_Date] and [txtTrading_Date]")

This also works perfectly.

But within a query! I am getting ready to shoot someone - or myself.

I want to run an update query down a set of records with fields
Start_Date and End_Date to enter the appropriate value into the field
"Duration".

I use exactly the same dsum as before (obviously substituting the
field names for the form's txt controls) but it just does not want to
know.

I have tried every combination I can imagine to try and make this work
- obviously testing as a select query first to see the answer before
applying it to an update.

I even try a simplified version (albeit providing the wrong answer,
but anything to get the ball rolling!) of simply counting every value
greater than the start date (rather than a "between" test.)

Imagining that it was having a problem reconciling some kind of UK v
US date thing (which there shouldn't be because this is an Access
query operating on an Access table where Access has total control of
how it stores - end hence reads back - it's own internal date
structure) I even inseted the DateSerial reconstruction of the
deconstructed parts of the date field. A long road round, for sure,
but as long as it works.

Yeah - but it doesn't. Still no difference.

Always the same result - 303, which just happens to be the sum of all
the 1s in that field in the Daily_Log table.

i.e. It ignores any attempt to filter anything out and allows every
record from the Daily_Log through to be summed.

I would be deliriously happy to receive any pointers towards a
solution. I am really stuck on my project until I get over this
obstacle!

And a very happy new year to allow who even took the trouble to read
this far!

RD

 

[Bonaldi]

Hi Marshall and thank you very much for your input.

However I don't think that I have explained myself properly.

My "welcome" form, just as a courtesy/intro thing to the user, shows
the number of trading days since the start of the month. The user can
look at it, ignore it, whatever.

Like I said, it works perfectly but I only cited it to show that it
works perfectly on forms and "seems" (I can put it no stronger) to
point to some intrinsic difference about how the dsum works when it is
the data source for a form's text box from when it is used inside a
query - any type of query.

However, I do want to use my query in the entirely conventional
manner:

a) as a one off backfill operation I want to fill in the "duration" on
existing records for completed trades (i.e. there is BOTH a start and
and end date).

For this, when I can prove that it works, I will just hit the run
button and that will be the backfill completed - no need for any code
anywhere.

b) There will always be some live trades. i.e. Records where the start
date date is entered but the end date is null.

Hence, I will have a subform (as a continuous form) showing all live
trades and, alongside the start date for each, the number of trading
days to date that each has been open. In this case, the start date for
the filter will be the "Start_Date" field from the table and the end
will simply be Date().

But, no matter what I do, I cannot get this dsum thing to work in any
kind of query, neither select nor update.

If you have any further ideas, i would be delighted to receive them,
thank you.

Regards

RD

 

[Bonaldi]

Wait - hold that last result!

Got it Marshall!

My apologies for not first having read your posting more carefully but
when i re-read it ans saw the "format" stuff .....

Anyway, if I stick a format, as you describe around everything (even -
and this seems ridiculous to me - the very Access function Date() )
then it all works perfectly.

Thank you very much for the info.

Regards

RD