Urgent! Wierd DSUM behavior

  • Thread starter Thread starter Craig M. Bobchin
  • Start date Start date
C

Craig M. Bobchin

Hi all,

I've been developing an app in Access 2003 with the MDB being in Access
2000 format.

In one form I have several running totals based on a value in a combobox
and the data being entered into a subform.

The Actual DSUM code is:

=DSum("[Hours]","tbltimetrackdata","[Date]=forms!frmTimeEntry!
[txtFriday]" & " And [ResourceID]=forms!frmTimeEntry![cboResourceID]")

I have 7 of these dsum fields on the main form.

The fields work perfectly in Access 2003. BUT, when we run the app in
Access 2K and the user tries to enter data in the subform, they get an
error message saying there is a syntax error in the DSUM() query and
that it is missing an operand. They can click through the error message
and still enter the data, get no more error messages and the fields work
perfectly after that.

What could be causing the disparate behavior/error message in 2000 and
not in 2003?

Thanks

Craig
 
Sounds to me like [txtFriday] and/or [cboResourceID] might be Null in
those cases.

HTH,
TC [MVP Access]
 
cboResourceID in not null, but txtFriday may be. But why does it work in
'03 and not 2k?

Sounds to me like [txtFriday] and/or [cboResourceID] might be Null in
those cases.

HTH,
TC [MVP Access]
Click to expand...
 
I would guess you get different results in the two versions,. because
you are not entering the exact same data in the exact same way. A singl
keystroke might make all the difference, depending on how your form
behaves "behind the scenes". Try entering the exact same data in the
exact same way on an exact same copy of the database, once in each
version.

But also, I misread your code. On checking again, I think that it would
/not/ get upset if either or both of those values were null. So I'm
sorta back to square one! Try posting the exact text of the message
you're getting.

HTH,
TC [MVP Access]
 
I have tried entering the exact same data the exact same way in the same
file in both versions. Still occurs. The exact message is:

Syntax error (missing operator) in query expression '=DSum
("[Hours]","tbltimetrackdata","[Date]=forms!frmTimeEntry![txtFriday]" &
" And [ResourceID]=forms!frmTimeEntry![cboResourceID]")'.

On the 2k version it happens as soon as someone tries to change the date
in the subform (1st field). They can click the error message away and
proceed normally with no further error messages and everything works
fine. In 2003 there is no error message at all. Hence my conclusion that
there was some change between versions.



I would guess you get different results in the two versions,. because
you are not entering the exact same data in the exact same way. A singl
keystroke might make all the difference, depending on how your form
behaves "behind the scenes". Try entering the exact same data in the
exact same way on an exact same copy of the database, once in each
version.

But also, I misread your code. On checking again, I think that it would
/not/ get upset if either or both of those values were null. So I'm
sorta back to square one! Try posting the exact text of the message
you're getting.

HTH,
TC [MVP Access]
Click to expand...
 
I think the the syntax of the query expression should be:

=DSum("[Hours]","tbltimetrackdata","[Date] = #" & forms!frmTimeEntry!
[txtFriday] & "# And [ResourceID] = " & forms!frmTimeEntry![cboResourceID])
Click to expand...

so that the actual values from the form are referenced. (Assuming that
[txtFriday] is a date value and [ResourceID is long or integer)

Craig M. Bobchin said:
Hi all,

I've been developing an app in Access 2003 with the MDB being in Access
2000 format.

In one form I have several running totals based on a value in a combobox
and the data being entered into a subform.

The Actual DSUM code is:

=DSum("[Hours]","tbltimetrackdata","[Date]=forms!frmTimeEntry!
[txtFriday]" & " And [ResourceID]=forms!frmTimeEntry![cboResourceID]")

I have 7 of these dsum fields on the main form.

The fields work perfectly in Access 2003. BUT, when we run the app in
Access 2K and the user tries to enter data in the subform, they get an
error message saying there is a syntax error in the DSUM() query and
that it is missing an operand. They can click through the error message
and still enter the data, get no more error messages and the fields work
perfectly after that.

What could be causing the disparate behavior/error message in 2000 and
not in 2003?

Thanks

Craig
Click to expand...
 
Is there any remote chance that [txtFriday] or [cboResourceID] contains
a bracket or somesuch? (Unlikely, I guess.)

Are the two versions of Access on different PCs? If so, do the pc's
have the same default date formats in control panel? It might be
something to do with how the date check is evaluated on each pc.

If that was it, I would try the following change (carefully!):

"[Date]=forms!frmTimeEntry![txtFriday]" & ...

to:

"[Date]=" &
format$(cdate(forms!frmTimeEntry![txtFriday]),"\#mm-dd-yyyy\#") & ...

HTH,
TC [MVP Access]
PS I may be off soon. It's 5am here!
 
Not quite!

If you're going to use the #...# date format, you need to ensure that
the date is in American (month/day/year) format - regardless of the
PC's control panel settings. Also you'd need quote marks around the
resource ID value:

... And [ResourceID] = """ & forms!frmTimeEntry![cboResourceID] &
"""")

But I think you are basically on to it. I just sqeezed in, before you
got there :-)

HTH,
TC [MVP Access]
 
TC,
... And [ResourceID] = """ & forms!frmTimeEntry![cboResourceID] &
"""")
Click to expand...

The quotes would depend on whether ResourceID was a text or number data type
field.

--
Wayne Morgan
MS Access MVP


TC said:
Not quite!

If you're going to use the #...# date format, you need to ensure that
the date is in American (month/day/year) format - regardless of the
PC's control panel settings. Also you'd need quote marks around the
resource ID value:

... And [ResourceID] = """ & forms!frmTimeEntry![cboResourceID] &
"""")

But I think you are basically on to it. I just sqeezed in, before you
got there :-)

HTH,
TC [MVP Access]
Click to expand...
 
As this is on a client's system I'll have to check with the IT dept. To
verify.
 
ResourceID is a numeric Long Int.
I'll check the date settings in the control panel for the Access 2k
machines. But the app forces MM/DD/YYYY for all dates.

So what would the final dsum() code be?

TC,
... And [ResourceID] = """ & forms!frmTimeEntry![cboResourceID] &
"""")
Click to expand...

The quotes would depend on whether ResourceID was a text or number data type
field.
Click to expand...
 
Craig said:
So what would the final dsum() code be?
Click to expand...

Just make the change I suggested, to your previous code.

I'd rewrite it in full, but I am off to sleepy-bye's now!

TC [MVP Access]
 
i would reccomend avoiding domain aggregate functions like the plague.
if you had a real db engine-- like MSDE or SQL Server; you could do
subqueries reliably without crapping out.

MDB just randomly craps out when queries get too complex; so i
reccomend not using subqueries in access; or queries on top of queries.

MDB is for babies.
 
MDB just randomly craps out when queries get too complex; so i
reccomend not using subqueries in access; or queries on top of queries.
Click to expand...

Aaron, as I have said to you before, and it seems I will have to say it
again:

None of us have /any problems/ with queries "crapping out" on a regular
basis. None of us spend /any time/ rewriting them for the reasons you
state. You are the /only person/ who has these problems on a regular
basis. /All of the rest of us/ have written complex, nested queries
that work perfectly, from the start of day one.

Your problems here are because you /don't know how to write them
properly/. There is /absolutely nothing whatever wrong/ with writing
nested queries in Access/Jet, if you do it properly - whcih you clearly
can not do. You are /misleading the readers/ repeatedly about this
issue. Your advice on this issue is /irresposible/, and /incorrect/.
Readers would do well, to ignore it.

TC [MVP Access]
 
you don't have problems with queries crapping out?

or you're used to building temp tables every 30 seconds?

i consider, query on top of query; on top of query-- MDB craps out--

im MISLEADING?

you are the jerk that's support an obsolete technology.

you are the jerk that is forcing these mdb wimps to be stuck getting
paid $15/hour.

i'm the one that says grow up; use a real db or go back to school and
learn a real career path

mdb queries CRAP OUT all the friggin time
mdb TABLES crap out all the friggin time

you guys copy and paste and copy and paste 100 times a day

'oh but we have tony's super-duper frontend scooper'

GAG

keep all your tables and queries in one place; it's a much much much
better solution

MDB queries crap out randomly; and redmond can eat shit until they
start fixing it
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSum Problem 4
DSum Countrol Source Help 5
Help with expression 4
Dsum Problem 3
Dsum-in subform_footer (passing value back into subform 6
Parameter Query with DSum 1
Dsum I have almost craked it BUT 7
Problem with OLE server or Activex control-URGENT 1

Back
Top