DSUM working in some fields, not others

[nick1408]

Hopefully an easy one - why on my yearly planner form is the DSUM working in some fields and not others? The formula is essentially the same all the way down.

Thanks guys.

 

[JHB]

Because date criteria in queries must be in US format = mm/dd/yyyy.
For dates where there can not be any doubt whether that one is month and the other day, there is not a problem, like 26-11-2016, but 07-01-2017 show the value for 01-07-2017 and versa visa.
So you need to rewrite your all query criteria like that:

...[Week Ending]=#" & Format([weekEnding14];"mm/dd/yyyy") & "#"

And you don't need the query name.

[Weekly Predicted Effort]![Week Ending]

More "easier" is:

[Week Ending]

 

[nick1408]

Crap! Makes sense I guess. Will I still get it to display in the "rest of the world" format?

Thanks for the tip on the [week ending] as well. I wasn't sure so went down the 'safe' route

 

[JHB]

.. Will I still get it to display in the "rest of the world" format?

What do you mean?

 

[nick1408]

Do I need to change weekEnding14 to mm/dd/yyyy also? I want it to display as did/mm/yyyy

 

[JHB]

As I showed you, only in the query criteria.
It will not affect how the dates are showed in the form.
But why not try it, it is much faster/easier.
Post back if you get trouble.

 

[nick1408]

Mate, works a treat! thanks for the help!

 

[nick1408]

Same question but this time my query has times in it as well. Where do I put the format in the following:

=DSum("[NPS Per Week]","[Weekly Baseline NPS]","[Weekly Baseline NPS]![Week Ending]=#" & Format([weekEnding7],"mm/dd/yyyy") & "#")

The following is what I thought it should be but doesn't work:

=DSum("[NPS Per Week]","format([Weekly Baseline NPS],"mm/dd/yyyy")","[Weekly Baseline NPS]![Week Ending]=#" & Format([weekEnding7],"mm/dd/yyyy") & "#")

 

[JHB]

Use the function DateValue.

"... DateValue([Week Ending])=

If it is not what you're looking for then show what you've and what you want to get.

 

[nick1408]

The above is the query and I want to place it in this form where the #Type! error is:

I tried to use DateValue but it threw up the error above

 

[JHB]

Show the control source.
Or post your database again, because what you posted earlier doesn't have the time, only the date.

 

[nick1408]

Here is a copy again. Query names are at the top of the pics

 

[JHB]

What is the name of the below form, it seems I can't find any which have a type error.

 

[nick1408]

Yearly NPS planner

 

[JHB]

Try the below:

=DSum("[NPS Per Week]","[Weekly Baseline NPS]","Datevalue([B][COLOR=Red][Week][/COLOR][/B])=#" & Format([weekEnding7],"mm/dd/yyyy") & "#")

 

[nick1408]

Try the below:

=DSum("[NPS Per Week]","[Weekly Baseline NPS]","Datevalue([B][COLOR=Red][Week][/COLOR][/B])=#" & Format([weekEnding7],"mm/dd/yyyy") & "#")

Perfect! thanks mate