Run Time error

[DMT Dave]

Hi Guy's this one i can't see the trees nor the wood for the trees !!!



I am trying to return a count of how many records are within the date (myDate) then move them to next week (myDate + 7)

The code I have here comes up with run time error see below, how can I group the DelTo and The Status of Records That Are Planning Or On Hold ?

Ideally in the strRS string, show the records in the MsgBox Before Continuing to Move to Next Week but only show 1 record per DelTo ?

So i can have 6 records for 1 x Joe Bloggs that says Planning, i Can have 3 x Records for Fred Bloggs that says On Hold, but grouping the rrecordset and displaying 1 line On MsgBox such as:

Fred Bloggs On Hold
Joe Bloggs Planning

Also unsure If we can add the quantity of each one also

Fred Bloggs On Hold x 3
Joe Bloggs Planning x 6

Do you want to continue to move the x 9 records to next week

myDate = Me.cboShipmentDateIndex1    NewDate = DateAdd("d", 7, myDate)
    strPlan = "Planning"
    strHold = "On Hold"
    Set rs = CurrentDb.OpenRecordset("Select tblEdit.DelTo, tblEdit.ShipmentDate, tblEdit.Status From tblEdit WHERE Status = '" & strPlan & "' Or Status = '" & strHold & "' And ShipmentDate = #" & myDate & "# GROUP BY DelTo")
    i = rs.RecordCount
    strRS = strRS & rs.Fields("DelTo") & " - " & rs.Fields("Status") & vbCrLf
    If MsgBox("Do You Want To Move: " & i & " " & "Records" & vbNewLine & _
    strRS & vbNewLine & _
    vbTab & "From: " & myDate & vbNewLine & _
    vbTab & "to: " & NewDate & " " & "?", vbQuestion + vbYesNo, "MOVE RECORDS") = vbNo Then
    DoCmd.CancelEvent
    Else
    Do Until rs.EOF
    rs.Edit
    rs.Fields("ShipmentDate") = NewDate
    rs.MoveNext
    Loop
    End If

ShipmentDate Is a Field set as Date

[ranman256]

Make a query,not sql,
it will tell you the problem, then you can correct it.

[Welshgasman]

You need to include the field mentioned in some way, even if not null.
I' surround your ors with brackets to be on the safe side.
Recordcount might not be correct unless you move last before looking at it?

[CJ_London]

remove the GROUP BY DelTo part of the code
or if you need it, either include the other fields or remove them

[DMT Dave]

Okie Dokie guys thank you will test as suggested

[DMT Dave]

So I am going to test the SQL based on this query