SQL Statement in VBA

[skw8966]

I have the following SQL statement in VBA with a Form_On Load procedure.

frmPOD!MonSht = "SELECT Sum(qryPODEntry.Shts) AS SumOfShts from qryPODEntry HAVING (((qryPODEntry.fkID)=1) AND ((DatePart('w',[PODDate]))=2) AND ((qryPODEntry.Category)='POD'))"

frmPOD relates to a subform on my main form (frmMain) and qryPODEntry refers to a query which I have linked to frmMain as well.

When executing the statement, I receive this error.

Runtime error '2113'
The value you entered isn't valid for this field.

Any ideas??

 

[jadams0173]

You could try to open the query as a recordset and then assign the recordset to the recordsource of the form.

 

[TheAceMan1]

How are ya skw8966 . . .

Try [blue]DSum[/blue] instead! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[skw8966]

How do I list multiple criteria with the DSum?

 

[genomon]

DSum help in Access shows you how - the third parameter is a WHERE clause, but without the word "WHERE".

All I ask is a chance to prove that money can't make me happy.

 

[skw8966]

This is what I have using the directions from the Access Help.

frmPOD!MonSht = DSum(Shts, qryPODEntry, "fkID=[frmMain]![pkID] and [Category]='POD' and Dayoftheweek=2")

I'm getting a type mismatch. I've verified the spellings of all the objects.

 

[genomon]

Bad syntax in the first 2 params - try:
DSum("[Shts]", "qryPODEntry", etc....

All I ask is a chance to prove that money can't make me happy.

 

[skw8966]

Now when I open the form, I get an this error.

Runtime error '2001'
You canceled the previous operation.

 

[genomon]

Breakpoint the first line of code & walk it thru line-by-line; post back the line of code that generates the error.
Please also post the full text of your current DLookup as well.
Also a great idea to read up on error handling strategies - there is a great section about it in Access help files.
Finally, have a look at faq102-5096 and faq222-2244.

All I ask is a chance to prove that money can't make me happy.

 

[TheAceMan1]

skw8966 . . .

. . . and this:

[blue]   Dim Cri As String
   
   Cri = "(fkID = " & frmMain!pkID & ") AND " & _
         "([Category]='POD') AND " & _
         "([DayOfTheWeek]=2);"
   frmPOD!MonSht = DSum(Shts, qryPODEntry, Cri)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[skw8966]

I tried the code you provided. It tells me "Object Required" and refers to that code.

 

[skw8966]

I moved the code from the OnLoad procedure to an OnClick button procedure within the form.

Now I get this error.

You entered an invalid argument in a domain aggregrate function.

 

[TheAceMan1]

skw8966 . . .

Is [blue]DayOfTheWeek[/blue] a field of the table? . . . as its not an access function?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[skw8966]

I got it working.

here's the code.

frmPOD!MonSht = DSum("Shts", "qryPODEntry", "[qryPODEntry]![Category]='POD' AND [qryPODEntry]![DayofWeek]=2")

Thanks for all the help. I really appreciate it.

 

[genomon]

Please do yourself and your users a favor and incorporate some righteous error handling to your procedures...

All I ask is a chance to prove that money can't make me happy.