Pass Where clause with a variable in RS

[joeKra]

hello,

i am trying to pass the where value with a variable, so it should be dynamic, can someone help me with that as so far i ma not successful, syntax below,
any help is appreciated

Private Function tsSql(strTripStatus As Integer, TripField As String, Optional dtOccupeidDate As Date, Optional intDispatchID As Integer) As String
Dim varWhere
varWhere = "" & TripField & " between #" & FromDate & "# and #" & ToDate & "#"
"INSERT INTO tmpDispatch ( TripDate, TripTime, GroupName, Action, DEST, Address, Near, CSZ, ReturnDate, ReturnTime, ContactPerson, PhoneDay, Driver, Bus, Collect, ContractId, Comments, [Dispatch ID],[PO#],InCharge,sReturn,sPickUp, ContractDetailID, sLeaveGarage,TripStatus,OccupeidDate) " _
      & "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, ContractDetails.ContractDetailID, Dispatch.sLeaveGarage," & strTripStatus & " ,#" & dtOccupeidDate & "#" _
      & "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 " _
     [COLOR="Red"] & " Where '" & varWhere & "';"[/COLOR]

 

[pbaldy]

Try this:

& " Where " & varWhere

You don't want the single quotes around the whole thing, and the semi-colon is optional anyway. If you're still stuck, this may help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[pbaldy]

ps: watch for spaces between lines too.

 

[joeKra]

Thanks for reply, still not working, what do you meant which spaces between lines should i watch of?

 

[pbaldy]

What are the results of the Debug.Print?

 

[joeKra]

here is the Debug.print Results:

INSERT INTO tmpDispatch ( TripDate, TripTime, GroupName, Action, DEST, Address, Near, CSZ, ReturnDate, ReturnTime, ContactPerson, PhoneDay, Driver, Bus, Collect, ContractId, Comments, [Dispatch ID],[PO#],InCharge,sReturn,sPickUp, ContractDetailID, sLeaveGarage,TripStatus,OccupeidDate) 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, ContractDetails.ContractDetailID, Dispatch.sLeaveGarage,3 ,#12:00:00 AM#FROM (Customers INNER JOIN Contracts ON Customers.CustomerId = Contracts.CustomerId) INNER JOIN ((Dispatch INNER JOIN ContractDetails ON Dispatch.ContractDetail
ID = ContractDetails.ContractDetailID) INNER JOIN (Destination INNER JOIN ContractStops ON Destination.PlaceID = ContractStops.PlaceID) ON ContractDetails.ContractDetailID = ContractStops.ContractDetailID) ON Contracts.ContractId = ContractDetails.ContractID where contractdetails.ReturnDate between #6/1/2012# and #6/6/2012#

 

[joeKra]

Another reason why not to use RunSql...., i had misspelled a field name caught it with CurrentDb,execute.....
Thanks anyway

 

[pbaldy]

No problem. Did you catch this?

...#12:00:00 AM#FROM (Customers INNER JOIN...

Note there's no space between # and FROM. That's the type of thing I was talking about earlier. Sometimes Access seems to be able to figure it out, most of the time it causes an error.

 

[joeKra]

yes, BTW how can i replace a Date\time if no value it should stay blank without 12:00 AM?

 

[pbaldy]

Not sure where you're trying to use it, but generally you can use the Nz() function to replace a Null value with a given default value.