Query date used by format error (1 Viewer)

[n3wguys]

Dear All,
I have wrote in VBA access, and when i run combobox on this form, in Month1, i saw #name?, in datasheet. help me:banghead:



Private Sub Combo33_AfterUpdate()
[Form_Monthlyreportbydate].RecordSource = "PARAMETERS [forms].[Report Menu].[combo33] DateTime; " & _
"TRANSFORM Sum(NZ([Net1],0)) AS Expr1 " & _
" SELECT Format([dateprod],'mmmm-yyyy') AS Month1, speciesbybuyer2.dateprod, speciesbybuyer2.Buyer, speciesbybuyer2.Species1, speciesbybuyer2.Grade, Sum(NZ([Net1],0)) AS [Total Of Net(m3)] " & _
" FROM speciesbybuyer2 " & _
" WHERE (((Format([Dateprod], 'mmm-yyyy')) = [Forms].[Report Menu].[Combo33])) " & _
" GROUP BY Format([dateprod],'mmmm-yyyy'), speciesbybuyer2.dateprod, speciesbybuyer2.Buyer, speciesbybuyer2.Species1, speciesbybuyer2.Grade " & _
" PIVOT speciesbybuyer2.Bandsaw In (1,2,3,4,5,6,7,8,9,10,11,12);"


End Sub

 

[Isskint]

Not 100% sure but Sum(NZ([Net1],0)) AS [Total Of Net(m3)] looks wrong. If you are creating a field in a query 'on the fly' then the field name should NOT be in square brackets, as this implies a field name of a table. Try Sum(NZ([Net1],0)) AS Total_Of Net(m3)

 

[n3wguys]

thank you for supporting
but here i was found error in month1. on datasheet month1 coloumn,display like
#name?.. but for another data could be view be well.

 

[Isskint]

The problem then may be in Format([dateprod],'mmmm-yyyy')
Try including the table name as well eg: Format(speciesbybuyer2.dateprod,'mmmm-yyyy').

Access displays #Name? in a control when the name that you supplied as the source of the control's value is not valid. For example, you might have misspelled the name, or the source (table name) might have been renamed or deleted.

So it could be the spelling of a field name is wrong (dateprod?). Failing that you have a space after Expr1 and before SELECT. This may be causing a problem as you only need 1 space, but that would affect everyting not just Month1.

 

[n3wguys]

hi friends,

that's made me confuse because i have check all but no problem.
okay, actually, when i wrote the code in vba access with control Combo33_AfterUpdate()

the error message in vba access, to show "mmmm-yyyy" in format, and then i had replace with 'mmmm-yyyy', after that when i ran the form,

all data could to show expect coloum month..

here i attach a picture..hope u can help me

remarks : picture5 -I have replaced by ", but after i have replaced by '
so will picture 6 starting.