too few many parameters expected

[DMT Dave]

Hi Guy's hope everyone is safe and well



Can you help with this one please ?

I have 2 query's one is called qryPostCodes and the other is called qryPostCodesAll

These are based on a Date from a combo box

qryPostCodes has got a criteria "Planning" in the status field, when I open the query it returns all records that are planning for that date (it is set to group by) as there maybe more than 1 record with the same postcode and only needing to return 1 record per postcode
qryPostCodesAll has got a criteria "<> "Collection" Or "Collection Ready" in the status field, when I open the query it returns all records that are apart from the criteria for that date (it is set to group by) as there maybe more than 1 record with the same postcode and only needing to return 1 record per postcode

When i run this through a recordset i am receiving runtime error 3061 too few many parameters expected 1.

The reason i want to run through a recordset so it can start at the first record and loop through the postcodes and add to google maps to view locations

Can anyone let me know where i am wrong ?

Also, do i have the loop correct ? if not, please advise ?

Kindest

Dim myPostCodes As String, MyURL As StringDim iQty As Integer, MyInput As Integer
Dim rs As DAO.Recordset


mPlan = "Planning"
mDate = Format(Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1, "mm/dd/yyyy")


MyInput = InputBox("Do You Want To View Map With:" & vbNewLine & vbNewLine & _
"1" & vbTab & "What Is Left Planning" & vbNewLine & vbNewLine & _
"2" & vbTab & "The Full Week", "ENTER MAP OPTION")


Select Case MyInput
Case 1
iQty = DCount("PostCode", "qryPostCodes")
Case 2
iQty = DCount("PostCode", "qryPostCodesAll")
End Select


If MyInput = "1" Then
Set rs = CurrentDb.OpenRecordset("qryPostCodes")
Do Until rs.EOF
rs.MoveFirst
myPostCodes = rs.Fields("PostCode")
MyURL = "https://www.google.co.uk/maps/dir/" & myPostCodes
OpenFile = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE " & MyURL, vbMaximizedFocus)
Loop
End If


If MyInput = "2" Then
Set rs = CurrentDb.OpenRecordset("qryPostCodesAll")
Do Until rs.EOF
rs.MoveFirst
myPostCodes = rs.Fields("PostCode")
MyURL = "https://www.google.co.uk/maps/dir/" & myPostCodes
OpenFile = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE " & MyURL, vbMaximizedFocus)
Loop
End If

.......................................
I Have now tried writing the Recordset and comes up with your query does not include Specified expression "ShipmentDate" as of an aggregate function
I thought i had conquered this by changing the code as below ?

If MyInput = "1" ThenSet rs = CurrentDb.OpenRecordset("Select tblEdit.ShipmentDate, tblEdit.PostCode, tblEdit.Status From tblEdit " & _
"WHERE ShipmentDate = #" & mDate & "# And Status = '" & mPlan & "' GROUP BY PostCode;")
Do Until rs.EOF
rs.MoveFirst
myPostCodes = rs.Fields("PostCode")
rs.MoveNext
Loop
MyURL = "https://www.google.co.uk/maps/dir/" & myPostCodes
OpenFile = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE " & MyURL, vbMaximizedFocus)
End If

[Minty]

Remove the tbledit.ShimentDate from your SELECT , and probably the status as well, as you haven't included that in the group by either, or add them to the grouping to include them in your output.

[DMT Dave]

thanks minty, how do i add them to the group by ? is it comma separations ?

[pbaldy]

Yes, separated by commas.