DSum not working, please help!

[Gina Whipp]

I am trying to get the total hours taken if your time ahs been excused and
all I get is Type Mismatch 13 error. It works if I remove 'And
"[aExcused]=" - 1' but I need that to show me total excused hours. What am
I doing wrong.

Thanks in advance,
Gina

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

 

[aaron.kempf]

MDB isn't reliable enough for a single user and a single record.

use SQL Server and a subquery instead of this trivial domain aggregate
crap

 

[Gina Whipp]

Not EXACTLY the answer I was looking for... NEXT

MDB isn't reliable enough for a single user and a single record.

use SQL Server and a subquery instead of this trivial domain aggregate
crap

I am trying to get the total hours taken if your time ahs been excused
and
all I get is Type Mismatch 13 error. It works if I remove 'And
"[aExcused]=" - 1' but I need that to show me total excused hours. What
am
I doing wrong.

Thanks in advance,
Gina

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

 

[Robert Morley]

It looks to me like you've got an extra quote in there, just before the
"And"...it should be:

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "' And "[aExcused]=" - 1)


Also, is aAssociateID a string or a number? If it's a number, that would be
another problem...remove the single quotes.


Rob

 

[Douglas J Steele]

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "' And [aExcused]= - 1")

 

[Guest]

Try :
DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &

AssociateID & "' And [aExcused] = " - 1)

The And has to be inside the quotation marks. By putting the double quote
after the single quote, you have taken it out. Note that AssociateID must be
of type text, otherwise you wouldn't need the single quotes.

Jim B

I am trying to get the total hours taken if your time ahs been excused and
all I get is Type Mismatch 13 error. It works if I remove 'And
"[aExcused]=" - 1' but I need that to show me total excused hours. What am
I doing wrong.

Thanks in advance,
Gina

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

 

[Gina Whipp]

aAssociateID is a string

It looks to me like you've got an extra quote in there, just before the
"And"...it should be:

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "' And "[aExcused]=" - 1)


Also, is aAssociateID a string or a number? If it's a number, that would
be another problem...remove the single quotes.


Rob

I am trying to get the total hours taken if your time ahs been excused and
all I get is Type Mismatch 13 error. It works if I remove 'And
"[aExcused]=" - 1' but I need that to show me total excused hours. What
am I doing wrong.

Thanks in advance,
Gina

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

 

[Gina Whipp]

Thank you, thank you, thank you....

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "' And [aExcused]= - 1")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to get the total hours taken if your time ahs been excused
and
all I get is Type Mismatch 13 error. It works if I remove 'And
"[aExcused]=" - 1' but I need that to show me total excused hours. What am
I doing wrong.

Thanks in advance,
Gina

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

 

[Gina Whipp]

Thank you, thank you, thank you....

It looks to me like you've got an extra quote in there, just before the
"And"...it should be:

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "' And "[aExcused]=" - 1)


Also, is aAssociateID a string or a number? If it's a number, that would
be another problem...remove the single quotes.


Rob

I am trying to get the total hours taken if your time ahs been excused and
all I get is Type Mismatch 13 error. It works if I remove 'And
"[aExcused]=" - 1' but I need that to show me total excused hours. What
am I doing wrong.

Thanks in advance,
Gina

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

 

[John Vinson]

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

The third argument of DSum needs to be constructed as a text string
that looks like a SQL WHERE clause selecting the desired records. In
this case you have the word AND *outside* the string you're building.
In addition, since the -1 is a constant, it should be inside the
quotes. It can be really confusing!

If AssociateID is a Text datatype you need the quotemarks:

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "' And [aExcused]= -1")

If AssociateID is a Number (which would be more common, but I don't
know how your table is set up) you don't need the singlequotes:

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = " &
AssociateID & " And [aExcused]= -1")


John W. Vinson[MVP]

 

[Robert Morley]

No problem.


Rob

 

[Gina Whipp]

aAssociateID is set up as a text per clients request

Thanks for the reply!

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "'" And "[aExcused]=" - 1)

The third argument of DSum needs to be constructed as a text string
that looks like a SQL WHERE clause selecting the desired records. In
this case you have the word AND *outside* the string you're building.
In addition, since the -1 is a constant, it should be inside the
quotes. It can be really confusing!

If AssociateID is a Text datatype you need the quotemarks:

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = '" &
AssociateID & "' And [aExcused]= -1")

If AssociateID is a Number (which would be more common, but I don't
know how your table is set up) you don't need the singlequotes:

DaysOff = DSum("SumOfaTimeDecimal", "qryDaysOff", "aAssociateID = " &
AssociateID & " And [aExcused]= -1")


John W. Vinson[MVP]