TO Few Parameters

[joeKra]

Hi i am having a Rs and and i am getting the to few parameters expecting 1 Error

Any help is appreciated

here is the sql:

[COLOR="Red"]'This Calls the Functon[/COLOR]
CurrentDb.Execute tssql(intTripStatus, OccupyDate1, val(Rs![Dispatch ID]))


Private Function tssql(strTripStatus As Integer, dtOccupeidDate As Date, intDispatchID As Long) As String


Dim varWhere
varWhere = "Val([Dispatch ID]) =" & intDispatchID & ""

tssql = "INSERT INTO occupiedTrip ( TripDate, TripTime, GroupName, Action, DEST, Address, Near, " _
& "CSZ, ReturnDate, ReturnTime, ContactPerson, PhoneDay, Driver, Bus, Collect, ContractId, Comments," _
& "[Dispatch ID],[PO#],InCharge,sReturn,sPickUp,[Time], ContractDetailID, sLeaveGarage,OccupyDate,TripPoint) " _
  & "SELECT ContractDetails.TripDate, ContractDetails.TripTime, Customers.GroupName, ContractStops.Action," _
  & "Destination.DEST, Destination.Address, Destination.Near,[Destination]![Town] & ', ' &[Destination]![state] & ' ' & [Destination]![zip] AS CSZ, " _
  & "ContractDetails.ReturnDate, ContractDetails.ReturnTime, Contracts.ContactPerson, Customers.PhoneDay," _
  & "Dispatch.Driver, Dispatch.Bus, Dispatch.Collect, Contracts.ContractId, Dispatch.Comments, Val([DispatchID]) AS [Dispatch ID], " _
  & "[PO#],InCharge,sReturn,sPickUp,Time, ContractDetails.ContractDetailID, Dispatch.sLeaveGarage,#" & dtOccupeidDate & "#," & strTripStatus & " " _
  & " FROM (Customers INNER JOIN Contracts ON Customers.CustomerId = Contracts.CustomerId) INNER JOIN ((Dispatch INNER JOIN ContractDetails ON Dispatch.ContractDetailID = ContractDetails.ContractDetailID) INNER JOIN (Destination INNER JOIN ContractStops ON Destination.PlaceID = ContractStops.PlaceID) ON ContractDetails.ContractDetailID = ContractStops.ContractDetailID) ON Contracts.ContractId = ContractDetails.ContractID " _
  & "Where " & varWhere
  
Debug.Print tssql
End Function

Here is the debug.print result:

INSERT INTO occupiedTrip ( TripDate, TripTime, GroupName, Action, DEST, Address, Near, CSZ, ReturnDate, ReturnTime, ContactPerson, PhoneDay, Driver, Bus, Collect, ContractId, Comments,[Dispatch ID],[PO#],InCharge,sReturn,sPickUp,[Time], ContractDetailID, sLeaveGarage,OccupyDate,TripPoint) SELECT ContractDetails.TripDate, ContractDetails.TripTime, Customers.GroupName, ContractStops.Action,Destination.DEST, Destination.Address, Destination.Near,[Destination]![Town] & ', ' &[Destination]![state] & ' ' & [Destination]![zip] AS CSZ, ContractDetails.ReturnDate, ContractDetails.ReturnTime, Contracts.ContactPerson, Customers.PhoneDay,Dispatch.Driver, Dispatch.Bus, Dispatch.Collect, Contracts.ContractId, Dispatch.Comments, Val([DispatchID]) AS [Dispatch ID], [PO#],InCharge,sReturn,sPickUp,Time, ContractDetails.ContractDetailID, Dispatch.sLeaveGarage,#3/13/1994#,2  FROM (Customers INNER JOIN Contracts ON Customers.CustomerId = Contracts.CustomerId) INNER JOIN ((Dispatch INNER JOIN ContractDetails ON Dispatch.ContractD
etailID = ContractDetails.ContractDetailID) INNER JOIN (Destination INNER JOIN ContractStops ON Destination.PlaceID = ContractStops.PlaceID) ON ContractDetails.ContractDetailID = ContractStops.ContractDetailID) ON Contracts.ContractId = ContractDetails.ContractID Where Val([Dispatch ID]) =112072

 

[pbaldy]

What happens if you copy/paste that SQL into a blank query and try to run it? Often you'll get a more descriptive error there. My guess is that something is spelled wrong.

 

[NigelShaw]

Sorry

i'll post in a minute!