DSUM generating "The expression you entered contains invalid syntax" error

[Tarl105]

Whenever I attempt to enter the following DSUM expression in the Control Source property of a text box on my main form, it generates the above-mentioned error message. Using it without the criteria works. Can someone please assist me off the ledge? I have been battling this for about three hours.



Here is my DSUM expression:

=DSUM(“[ThawEmbThwd]”,”qryEmbThawFile”,”[CryoFileID]= [Forms]![frmActCCHEmb]![frmEmbCryoFile_Sub].[Form].[frmEmbThawFile_Csub].[Form]![txtCryoFileID]")

Thanks,

Tarl105

[RuralGuy]

The target appears to be on a SubForm of a SubForm. Try moving the reference outside of the quotes:
=DSUM(“[ThawEmbThwd]”,”qryEmbThawFile”,”[CryoFileID]= " & [Forms]![frmActCCHEmb]![frmEmbCryoFile_Sub].[Form].[frmEmbThawFile_Csub].[Form]![txtCryoFileID])

[Tarl105]

RG, thanks for your quick response!

I would like to verify that the following is your suggestion, I want to make sure that I didn't screw it up in copying:
=DSUM(“[ThawEmbThwd]”,”qryEmbThawFile”,”[CryoFileID]= *" &* [Forms]![frmActCCHEmb]![frmEmbCryoFile_Sub].[Form].[frmEmbThawFile_Csub].[Form]![txtCryoFileID])

Thanks.

[RuralGuy]

=DSUM("[ThawEmbThwd]","qryEmbThawFile","[CryoFileID]= " & [Forms]![frmActCCHEmb]![frmEmbCryoFile_Sub].[Form].[frmEmbThawFile_Csub].[Form]![txtCryoFileID])
...No "*" in what I posted. This does not work if the frmActCCHEmb form is not open.

[Tarl105]

Thanks for your 10:45 AM reply RG.

I entered the code exactly as illustrated...still no joy. Exactly the same error message.

The frmActCCHEmb main form is open at the time that I attempt to enter this into the field's ControlSource.

If I truncate the criteria expression, it works fine. It has something to do with that and I am at my wit's end.

CryoFileID is present in the query feeding the subsubform and as a non-visible text field in the subsubform..[frmEmbThawFile_CSub].

Any other ideas RG or are you deciding that I'm a lost cause? ;>)

Whatever the outcome, thanks for being cordial enough to at least give it a look.

Tarl105

[RuralGuy]

It could be the depth of the nested SubForms. How about an invisible TextBox on the top most form (frmActCCHEmb) with a ControlSource of:
=frmEmbCryoFile_Sub.Form.frmEmbThawFile_Csub.Form! txtCryoFileID
...but leave it visible until it works as you want then reference *that* control in your DSum() function.

[Tarl105]

RG,

I shall give that a try. It sounds better than anything else I have tried on my own! ;>)

I will keep you posted.

This mission will be hacked!!

Tarl105

[Tarl105]

RG,

Interesting outcome of this adventure:
In using your latest tip: =frmEmbCryoFile_Sub.Form.frmEmbThawFile_Csub.Form! txtCryoFileID in ta text box on the main form, I substituted txttotamt for txtCryoFileID and I noticed that the result that I was seeking...specifically, the total amount of a field in the subsubform....appeared in that textbox...without using DSUM!!!
Still don't know why the DSUM construct didn't work but your solution gives me what I need just fine!!
Must be that 8,300 foot Rocky Mountain air that gives you such a clear head!!
Stay low and keep movin' RG and thanks!!!

Tarl105

[RuralGuy]

Excellent! I'll admit the air *is* nice, as well as the stars (billions and billions I'd say). Are you ready to follow the link in my sig and mark this thread as Solved?

[Tarl105]

RG,

I would truly like to give you your props....IF I COULD GET ANYTHING OTHER THAN THE THREE DEFAULT OPTIONS ON THE ^%$#*@# Thread Tools pulldown!

Kevin forwarded your message, with a link to the "Solved" image, which I followed. That's wher Mr. Murphy reared his ugly head and that's where I am now stuck!

This cannot be that difficult but I seem to be doing a pretty good job of making it so!

Where did I screw up?

[Tarl105]

RG,

As a follow-on to my last tirade. I finally stumbled onto the "Mark This Thread As Solved" key.

I marked it as "Solved".

Thanks again for your timely help. I was on the ledge.;>)

Tarl105

[RuralGuy]

Ain't computers fun?

[Nasserhj]

Thank you for help

[RuralGuy]

Glad we could help.