troubleshooting Question

Using dynamic variables in an Access 2010 SQL Query

Avatar of Floyd Sumner
Floyd Sumner asked on
Microsoft Access
4 Comments2 Solutions29 ViewsLast Modified:
What is the proper syntax for dynamic variables inside a MS Access 2010 SQL Select query?

Following is the code I'm trying to complete and I can't recall the correct syntax for placing dynamic variables inside a MS Access 2010 SQL Select query;

Dim STDate, EnDate As Date
Dim STtime, EnTime As Variant
Dim ProArea,  SQLSearch As String

ProArea = Me.ProgArea
STDate = Me.StartDT
STime = Me.cmb_StartTime
EnDate = Me.EndDT
EnTime = Me.cmb_EndTime

SQLSearch = "SELECT VehiclePool.[Unit#], VehiclePool.[Year, Make, Model], VehiclePool.Program, VehiclePool.Location, tbl_VehicleSchedule.PurposeOfTrip, " & _
"tbl_VehicleSchedule.StartDT, tbl_VehicleSchedule.EndDT INTO tbl_VehicleDatesResults " & _

"FROM VehiclePool INNER JOIN tbl_VehicleSchedule ON VehiclePool.[Unit#] = tbl_VehicleSchedule.[Unit#] " & _

"WHERE (((VehiclePool.[Unit#]) Is Not Null) AND ((VehiclePool.Program)= ProArea) AND ((tbl_VehicleSchedule.StartDT)=IsNull((([tbl_VehicleSchedule].[StartDT]) " & _
"Between STDate And EnDate) Or ([tbl_VehicleSchedule].[StartDT])=IsEmpty(([tbl_VehicleSchedule].[StartDT]) Between STDate And EnDate)) Or " & _
"(tbl_VehicleSchedule.StartDT) Is Null) AND ((tbl_VehicleSchedule.EndDT)=IsNull((([tbl_VehicleSchedule].[EndDT]) Between STDate And EnDate) Or " & _
"([tbl_VehicleSchedule].[EndDT])=IsEmpty(([tbl_VehicleSchedule].[EndDT]) Between STDate And EnDate)) Or (tbl_VehicleSchedule.EndDT) Is Null));"
SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros