469,646 Members | 1,596 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

Debug--Query does not include the specified expression as part of aggregate function

260 100+
I've tried to put together some code for a form. This code actually compiles (and I've very proud of that!) but it gives me this error when running and points to line 41 in the code below.
"Run-time error 3122--You tried to execute a query that does not include the specified expression 'ORDER_NUM' as part of an aggregate function."

What I want to do is this:
1. Enter a box number being returned. This will be entered into a dummy field [txtScanCapture] by a scanner, and if it has 3-4 digits, it transfers into txtScan_Box_Num. (For those who have been helping me, I have decided *not* to enter the customer number on box return because it's pointless as far as I can tell.) This BOX_NUM was previously assigned to a [Cust_Num] and [Order_Num] in tblBOX when the box was shipped.
2. Automatically insert date() into [DATE_BOX_RETURN] on subfrmBOX_SHIPPING which is on frmBOX_SHIPPING.
3. Look in tblORDERS for the [Order_Num] and [Cust_Num] matching the [Box_Num] just scanned. If field [Date_Ret] is empty, insert date(). If it isn't empty, then that implies it was done on a previous box return.

I don't know how mangled my code is but I gave it my absolute best effort. Can you please look at my code and tell me what all might be wrong with it? I'm not sure if I have my If statments correctly nested or if my strsql is in the right place. Note I have two uses of strsql--is that ok? These are the things I'm just not sure on. Suggestions to eliminate my run-time error will also be greatly appreciated!

PS. For those who have helped on a similiar problem, I changed the names of two fields on the main form...since I won't be scanning the cust_num I changed the names of two fields, taking out the "Scan" reference.
Also, I've got a couple of open posts that I need to kind of wrap-up/close but I want to get all similar problems solved so I can put my final solutions in. Things might still change slightly. ;-)

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Public strLastScan As String
  5. Public db As DAO.Database
  6.  
  7. Private Sub Form_Open(Cancel As Integer)
  8.     Set db = CurrentDb
  9. End Sub
  10.  
  11. Private Sub txtScanCapture_AfterUpdate()
  12.     Dim strSQL As String
  13.  
  14.     Select Case Len(Me.txtScanCapture)
  15.     Case 3, 4
  16.         'Box
  17.         'Is box registered in database?
  18.         If DCount("BOX_NUM", _
  19.                       "tblBOX", _
  20.                       "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  21.                 'Box does not exist in DB
  22.                 MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
  23.         Else
  24.                 Me.txtScan_Box_Num = Me.txtScanCapture
  25.                 'Box exists.
  26.                 'Set received date=now
  27.                 strSQL = "UPDATE tblBOX " & _
  28.                          "SET    [DATE_BOX_SHIP]=Date()" & _
  29.                          "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  30.                 DoCmd.SetWarnings (False)
  31.                 DoCmd.RunSQL strSQL
  32.                 DoCmd.SetWarnings (True)
  33.                 Me.subfrmBOX_RECEIVING.Requery
  34.                'Use the box_num to obtain the cust_num & order_num from tblBOX
  35.                'but I don't know how to store the values to use in the .FindFirst below
  36.                 strSQL = "SELECT   [CUST_NUM]" & _
  37.                         ", [ORDER_NUM]" & _
  38.                         "FROM     tblBOX  " & _
  39.                         "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _
  40.                         "GROUP BY [CUST_NUM]"
  41.                 With db.OpenRecordset(strSQL, dbOpenSnapshot)
  42.                     If .RecordCount = 0 Then
  43.                         MsgBox "There is no record of this box shipping"
  44.                         'Do whatever you want to handle this case
  45.                     Else
  46.                         Me.txtScan_Box_Num = !BOX_NUM
  47.                         Me.tb_Cust_Num = !CUST_NUM
  48.                         Me.Max_ORDER_NUM = !ORDER_NUM
  49.                     End If
  50.                     Call .Close
  51.                 End With
  52.         End If
  53.  
  54.         'Update the DATE_RET in tblOrders where necessary
  55.         With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  56.             Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  57.             If Not .NoMatch Then
  58.                 If IsNull(![DATE_RET]) Then
  59.                     Call .Edit
  60.                     ![DATE_RET] = Date
  61.                     Call .Update
  62.                 End If
  63.             End If
  64.             Call .Close
  65.         End With
  66.         strLastScan = "Box"
  67.  
  68.     Case Else
  69.         'Some sort of error or user error
  70.         MsgBox "Box Numbers can only be 3 or 4 digits."
  71.     End Select
  72.  
  73.     Me.txtScanCapture = ""
  74. End Sub
Thanks to those of you who continue to help my pitiful self. hahaha.
May 26 '10 #1

✓ answered by DanicaDear

NeoPa helped me make a few changes to my code and here is the final, working piece.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Public db As DAO.Database
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Set db = CurrentDb
  8. End Sub
  9.  
  10. Private Sub txtScanCapture_AfterUpdate()
  11.     Dim strSQL As String
  12.  
  13.     Select Case Len(Me.txtScanCapture)
  14.     Case 3, 4
  15.         'Box
  16.         'Is box registered in database?
  17.         If DCount("BOX_NUM", _
  18.                   "tblBOX", _
  19.                   "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  20.             'Box does not exist in DB
  21.             MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
  22.         Else
  23.             Me.txtScan_Box_Num = Me.txtScanCapture
  24.             'Box exists.
  25.             'Set received date=now
  26.             strSQL = "UPDATE tblBOX " & _
  27.                      "SET    [DATE_BOX_RETURN]=Date() " & _
  28.                      "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  29.             DoCmd.SetWarnings (False)
  30.             DoCmd.RunSQL strSQL
  31.             DoCmd.SetWarnings (True)
  32.             Me.subfrmBOX_RECEIVING.Requery
  33.             'Use the box_num to obtain the cust_num & order_num from tblBOX
  34.             'but I don't know how to store the values to use in the .FindFirst below
  35.             strSQL = "SELECT   * " & _
  36.                      "FROM     [tblBOX] " & _
  37.                      "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'"
  38.             With db.OpenRecordset(strSQL, dbOpenSnapshot)
  39.                 If .RecordCount = 0 Then
  40.                     MsgBox "There is no record of this box shipping"
  41.                     'Do whatever you want to handle this case
  42.                 Else
  43.                     Me.txtScan_Box_Num = !BOX_NUM
  44.                     Me.tb_Cust_Num = !CUST_NUM
  45.                     Me.Max_ORDER_NUM = !ORDER_NUM
  46.                 End If
  47.                 Call .Close
  48.             End With
  49.         End If
  50.  
  51.         'Update the DATE_RET in tblOrders where necessary
  52.         With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  53.             Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  54.             If Not .NoMatch Then
  55.                 If IsNull(![DATE_RET]) Then
  56.                     Call .Edit
  57.                     ![DATE_RET] = Date
  58.                     Call .Update
  59.                 End If
  60.             End If
  61.             Call .Close
  62.         End With
  63.  
  64.     Case Else
  65.         'Some sort of error or user error
  66.         MsgBox "Box Numbers can only be 3 or 4 digits."
  67.     End Select
  68.  
  69.     Me.txtScanCapture = ""
  70. End Sub
  71.  
  72.  
I appreciate everyone's help on these scanning issues. I'm "over the hump" as they say and the rest of the DB should be a downhill slide. Thanks thanks thanks again.

7 5606
MikeTheBike
637 Expert 512MB
@DanicaDear
Hi

Withoutlooking at all the code, the specific error you have give should we fixed if this
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT   [CUST_NUM]" & _ 
  2.                         ", [ORDER_NUM]" & _ 
  3.                         "FROM     tblBOX  " & _ 
  4.                         "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _ 
  5.                         "GROUP BY [CUST_NUM]" 
is changed to this
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT   [CUST_NUM]" & _ 
  2.                         ", [ORDER_NUM]" & _ 
  3.                         "FROM     tblBOX  " & _ 
  4.                         "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _ 
  5.                         "GROUP BY [CUST_NUM], [ORDER_NUM]" 
ie. add the offending field to thr GROUP BY clause,

or pehaps this is what you intended (using an aggregate function)
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT   [CUST_NUM]" & _ 
  2.                         ", Max([ORDER_NUM]) " & _ 
  3.                         "FROM     tblBOX  " & _ 
  4.                         "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'" & _ 
  5.                         "GROUP BY [CUST_NUM]" 
??

MTB
May 28 '10 #2
NeoPa
32,203 Expert Mod 16PB
If you leave Skype up I will try to catch you later tonight. It will probably be easier to explain in words with the database to look at what has changed and what should match what.

I'll explain what needs to be changed, and possibly even post in here to explain what is going on.

I know what it used to do, but I'm not really following the explanation of what's changed in the underlying logic (and this is critical as its not very typical logic for a form).
May 28 '10 #3
NeoPa
32,203 Expert Mod 16PB
BTW. Mike's suggestion is all perfectly valid. It makes sense.

However, as he doesn't understand your logic either, it's simply a fix to the specific problem, rather than a viable way forward for your project.

You need to understand why what you have written is not right. let me see if I can illustrate quickly :
Table = [tblBox]
Expand|Select|Wrap|Line Numbers
  1. BOX_NUM  CUST_NUM  ORDER_NUM
  2.   001      AAA       00001
  3.   002      AAA       00002
  4.   003      BBB       00003
  5.   004      BBB       00003
Where [CUST_NUM]='BBB' the obvious value to return for [ORDER_NUM] would be '00003'. For 'AAA' however, if you are grouping by [CUST_NUM], what value would be returned for [ORDER_NUM]?

If you include [ORDER_NUM] in the GROUP BY clause (as per Mike's first solution) then box '001' and '002' are no longer in the same grouping. Problem avoided.

If you use Max() around [ORDER_NUM] (as per Mike's second) then it knows to return the larger value '00002'.

Does this clarify the issue, and why a better understanding is required before we can properly direct you?
May 28 '10 #4
DanicaDear
260 100+
Yes, NeoPa, I see your point clearly. The Max() function, (Mike's second proposal) is probably what I need. I haven't tried Mike's code yet but hope to have it complete by evening.

As for what has changed and the underlying logic...it's similar to the Box_Shipping except I want to *not* scan the customer number with every box number scan. I still want it to apply date() to the tblBOX and tblORDERS. Any confusion beyond that is probably just my mangling of the code. I tried to take what you gave me for Box_Shipping and make it work for Box_Receiving. I'm off work today and baby is going to Grandma's. When I get my errands complete I'll sign on Skype and maybe I'll catch you on later.
May 28 '10 #5
NeoPa
32,203 Expert Mod 16PB
I must admit that I missed that this was about Receiving rather than Shipping.

Although we discussed this in more detail last Friday on Skype, I think it may be helpful to post the code we eventually came up with. It may help to clarify for any later readers what it was all about.
Jun 1 '10 #6
DanicaDear
260 100+
NeoPa helped me make a few changes to my code and here is the final, working piece.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Public db As DAO.Database
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Set db = CurrentDb
  8. End Sub
  9.  
  10. Private Sub txtScanCapture_AfterUpdate()
  11.     Dim strSQL As String
  12.  
  13.     Select Case Len(Me.txtScanCapture)
  14.     Case 3, 4
  15.         'Box
  16.         'Is box registered in database?
  17.         If DCount("BOX_NUM", _
  18.                   "tblBOX", _
  19.                   "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  20.             'Box does not exist in DB
  21.             MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
  22.         Else
  23.             Me.txtScan_Box_Num = Me.txtScanCapture
  24.             'Box exists.
  25.             'Set received date=now
  26.             strSQL = "UPDATE tblBOX " & _
  27.                      "SET    [DATE_BOX_RETURN]=Date() " & _
  28.                      "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  29.             DoCmd.SetWarnings (False)
  30.             DoCmd.RunSQL strSQL
  31.             DoCmd.SetWarnings (True)
  32.             Me.subfrmBOX_RECEIVING.Requery
  33.             'Use the box_num to obtain the cust_num & order_num from tblBOX
  34.             'but I don't know how to store the values to use in the .FindFirst below
  35.             strSQL = "SELECT   * " & _
  36.                      "FROM     [tblBOX] " & _
  37.                      "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'"
  38.             With db.OpenRecordset(strSQL, dbOpenSnapshot)
  39.                 If .RecordCount = 0 Then
  40.                     MsgBox "There is no record of this box shipping"
  41.                     'Do whatever you want to handle this case
  42.                 Else
  43.                     Me.txtScan_Box_Num = !BOX_NUM
  44.                     Me.tb_Cust_Num = !CUST_NUM
  45.                     Me.Max_ORDER_NUM = !ORDER_NUM
  46.                 End If
  47.                 Call .Close
  48.             End With
  49.         End If
  50.  
  51.         'Update the DATE_RET in tblOrders where necessary
  52.         With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  53.             Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  54.             If Not .NoMatch Then
  55.                 If IsNull(![DATE_RET]) Then
  56.                     Call .Edit
  57.                     ![DATE_RET] = Date
  58.                     Call .Update
  59.                 End If
  60.             End If
  61.             Call .Close
  62.         End With
  63.  
  64.     Case Else
  65.         'Some sort of error or user error
  66.         MsgBox "Box Numbers can only be 3 or 4 digits."
  67.     End Select
  68.  
  69.     Me.txtScanCapture = ""
  70. End Sub
  71.  
  72.  
I appreciate everyone's help on these scanning issues. I'm "over the hump" as they say and the rest of the DB should be a downhill slide. Thanks thanks thanks again.
Jun 1 '10 #7
NeoPa
32,203 Expert Mod 16PB
I certainly enjoyed it Danica. Good luck with the rest of it :)
Jun 1 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Lori2836 via AccessMonster.com | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.