run-time errror '13 on forms on open event

[sdel_nevo]

Hi Guys



i am getting a run-time errror '13' when this code run on a forms on open event

Dim PreplannedCapacity As string
PreplannedCapacity = Nz(DSum("PrePlannedCapacity", "UNEXAreaCapacityPrePlan", "Area = '" & Forms!UNEXCapacitys!Process & "'" And "WeekNumber = #" & Forms!UNEXCapacitys!DATE & "#"), 0)


Me.Text12 = PreplannedCapacity
End Sub

any ideas how i can sort this,

many thanks

Steve

[RuralGuy]

Try running it in the On Load event instead.

[sdel_nevo]

Hi RuralGuy

i get the same error their also.

PrePlannedCapacity = number
UNEXAreaCapacityPrePlan = table name
Area =text
Forms!UNEXCapacitys!Process = text field
WeekNumber =is a number

above is what each part of the code is could it be an issue with the dim type? sorry if my terminalogy is wrong

steve

[RuralGuy]

Is this running in the UNEXCapacitys form?

[RuralGuy]

Your dsum() function will return a number, not a string.

[RuralGuy]

This does not look right either:
"Area = '" & Forms!UNEXCapacitys!Process & "'" And "WeekNumber = #"
I believe it should be:
"Area = '" & Forms!UNEXCapacitys!Process & "' And WeekNumber = #"

[orange]

Is "PrePlannedCapacity" really a field in table "UNEXAreaCapacityPrePlan"?

I think you have an issue with double quote in your expression:

PreplannedCapacity = Nz(DSum("PrePlannedCapacity", "UNEXAreaCapacityPrePlan", "Area = '" & Forms!UNEXCapacitys!Process & "'" And "WeekNumber = #" & Forms!UNEXCapacitys!DATE & "#"), 0)

I would try (untested):

PreplannedCapacity = Nz(DSum("PrePlannedCapacity", "UNEXAreaCapacityPrePlan", "Area = '" & Forms!UNEXCapacitys!Process & "' And WeekNumber = #" & Forms!UNEXCapacitys!DATE & "#"), 0)
Good luck.


OOops: I see Allan has responded while I was interrupted with a Skype call. You're going to need to reconcile your data types.

[sdel_nevo]

Hi Ruralguy

i have changed the code as you recommended, i now get the erro "Run-time error '3075': Systntax error in date in query expression 'Area ='KDM' And WeekNumber =#4'

i will keep trying

[sdel_nevo]

Hi Orange

sorry i did not see your reply untill i posted back.

im sure im doing something really silly but at the moment i just can't see it, i will plod on and hopefully get my head around it

Steve

[orange]

Hmmm?
If PrePlannedCapacity is a number then you do NOT need to enclose in quotes.
I would call the control by a different name to avoid confusion, For example, txtPrePlannedCapacity if it is a text/string, or intPrePlannedCapacity or dblPrePlannedCapacity (or similar) if it was a number. I'm not sure how you are getting week number from a date. See this for the Datediff() function
Perhaps you should show us your table definition to see field names and data types. And a description of exactly what you are trying to do in plain English.

Good luck with your project.

[sdel_nevo]

Hi Orange

sorry for the late reply,

not sure why but when i changed the code to this PreplannedCapacity = Nz(DSum("PrePlannedCapacity", "UNEXAreaCapacityPrePlan", "Area = '" & Forms!UNEXCapacitys!Process & "' And WeekNumber = #" & Forms!UNEXCapacitys!DATE & "#"), 0)

as advised i was getting the error message above, when i made a new blank form and copied the code onto it it worked as expected!!!


not sure why but it was sure getting worring

any way alls working now sorry for not getting back sooner, been a manic day

Kind regards

Steve

[orange]

Glad you have it resolved!

[RuralGuy]

Thanks for posting back with your success and solution. Be sure to mark this thread as Solved.