cant write correctly dsum func

[nahum abramovich]

hi. I need help in the Access form I created. If you can help, I would like to know how to sum values between time and time. For example, I tried this:
dsum("[parts in holder]",[ database nahum ]", " [date=date() ]", between (12:00:00) and (18:00:00)) . not working

I thought maybe because my [date] and [time] are in *separate columns* maybe i should do this :
dsum("[parts in holder]",[ database nahum ]",
"[date=date() ]","(12:00:00)<[time]<(18:00:00)")
. not working

[ranman256]

you cannot put values/function inside quotes, they should be without quotes to resolve.
your second param is missing a quote,
and date/time uses # as a delimiter:

dsum("[parts in holder]","[ database nahum ]", "[DateFld]=#" date() & "#" )

dsum("[parts in holder]","[ database nahum ]", "[DateFld] between #" & txtTime1 & "# and #" & txtTime2 & #")

NOTE: its best to combine date and time into a single field : 12/1/2018 12:45 pm
(and not have 1 field for date and 1 field for time)

this way your date math can perform across midnight

[CJ_London]

agree with ranman and also point out that date and time are reserved words - date is a function which returns today and time returns the current time (now returns the current date and time) - so it is advisable not to use as as field names as it will confuse sql. Recommend changing them to something more meaningful such as 'inputdate'.

Also spaces in field and table names are not advised since again, they cause problems.

Finally, not mentioned but spaces before or after names won't work so this

"[ database nahum ]"

should be

"[database nahum]"

[nahum abramovich]

Thanks so much for your response.for now I can not connect the column of the [date] with the [time] column for some reason . I'd be happy to explain to you exactly what I want to do and maybe you could help me write it down properly.

I have a column of [parts in holder].
I have a column of [date].
i have a column of [time].
All this is in the [database nahum] table.

I want to get feedback of the
[parts in holder] Productivity in 3 shifts so i can see it in my form (inside 3 box) every morning.
1 shift is between (08:00:00) and (17:00:00)
shift 2 is between (17:00:00) and (00:00:00)
shift 3 is between (00:00:00) and (08:00:00)

now if i understood you correctly this are the 3 rows that i need to write.

for shift 1 (see the date -1 for the day before)

dsum("[part in holder]","[database nahum]","[date]=date()-1 between #"&(08:00:00)&"# and #"&(17:00:00)&"#")

for shift 2 - (see the date -1for the day before)

dsum("[part in holder]","[database nahum]","[date]=date()-1 between #"&(17:00:00)&"# and #"&(00:00:00)&"#")

for shift 3

dsum("[part in holder]","[database nahum]","[date]=date() between #"&(00:00:00)&"# and #"&(08:00:00)&"#").

need your help mf

[CJ_London]

not quite right

dsum("[part in holder]","[database nahum]","[date]=date()-1 and [Time] between #08:00:00# and #17:00:00#")

you can see here

[date]=date()-1

how using a date as a field name is confusing

[ranman256]

date() will not resolve inside quotes.

[June7]

date() will not resolve inside quotes.

Doesn't need to. The SQL engine understands the Date() function. The SQL string will construct referencing the Date() function and when the SQL executes it will evaluate Date() function and return the current date. Domain aggregate function will also evaluate Date() function within quote marks.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be DatabaseNahum or Database_Nahum.

[nahum abramovich]

thank you all for the help its not obvious that you are helping.
ajax- i tried your dsum but its no working. the box returns ×××error×××

[nahum abramovich]

not working mf

[isladogs]

As you are still having trouble, consider creating a tblShifts with 3 fields: ShiftNumber (PK - integer), StartTime & EndTime. It will have 3 records.
Now reference the ShiftNumber instead of the time values in your DSum formulas.
This may make it easier to resolve issues

Also as already stated by both ajax & ranman, do rename the Date field to prevent confusion & avoid risk of unpredictable results and combine with your equally badly named Time field
However, please don't call the combined field DateTime!

[June7]

I tested the syntax suggested by Ajax and it works. Provide a sample of your data. If you want to provide db for analysis, follow instructions at bottom of my post.

[nahum abramovich]

NOTE: its best to combine date and time into a single field : 12/1/2018 12:45 pm


Can you please teach me how to do it? I've set the column to GENERAL DATE (12/1/2018 12:45:05)
but there is no view of time in the column w... just dates.

[isladogs]

Add a new record and include a time e.g. 09:30:00 as well as a date. It will then be displayed
You may need to widen the field in table / form to see it.

Existing records will have a time equal to 00:00:00 (which isn't displayed) until you add the time
Those records can be updated by concatenating the date and time fields using an update query.
After that delete your time field