SQL IN VBA Syntax error (Between operator without And in Query expression) (1 Viewer)

[sphynx]

Ive done a good 4 hours of googling and searching to no avail so would appreciate some direction.

I have the following code:-

Private Sub Command71_Click()
Dim SDate, FDate As Date

Me.Text74 = Me.DTPicker7 - 1
SDate = Me.Text74

Me.Text76 = Me.ActiveXCtl68 + 1
FDate = Me.Text76

DoCmd.RunSQL "SELECT Process_Tbl.ProcessID, ProductDetail_Tbl.ProductCode, Process_Tbl.BatchNo, Product_Tbl.ProductDesc, " & _
             "Process_Tbl.PkgDate, (((Process_Tbl!BatchNo*ProductDetail_Tbl!SliceBatch)/ProductDetail_Tbl!SliceUnit)" & _
             "*ProductDetail_Tbl!Sales£) AS Sales£, [Sales£]*[WasteTarget] AS WTarget£, [TWasteSlices]/" & _
             "((Process_Tbl!BatchNo*ProductDetail_Tbl!SliceBatch)-[TWasteSlices]) AS [TWaste%], [Sales£]*[TWaste%] AS TWaste£, " & _
             "ProductDetail_Tbl.WasteTarget, [WasteTarget]-[TWaste%] AS [WDiff%], [TWaste%]-[PW%] AS [OgW%], [Sales£]*[OgW%] " & _
             "AS OgW£, IIf([TWaste%]>[WasteTarget],[WasteTarget],[TWaste%]) AS [PW%], [Sales£]*[PW%] AS PW£, " & _
             "Process_Tbl!PDSamples+Process_Tbl!QCSamples+Process_Tbl!MDSamples AS TSample, " & _
             "(((ProductDetail_Tbl!SliceBatch*Process_Tbl!BatchNo)-[TSample])/ProductDetail_Tbl!SliceUnit)-Process_Tbl!ITSCount " & _
             "AS TWasteSlices, Process_Tbl.ITSCount, Process_Tbl.Employee, Process_Tbl.WasteNote, Process_Tbl.Size, " & _
             "Process_Tbl.Finish, Process_Tbl.Colour, Process_Tbl.Weight, Process_Tbl.Dropped, Process_Tbl.ForeignBody, " & _
             "Process_Tbl.MachineDamage , Process_Tbl.Consistency, Process_Tbl.PDSamples, Process_Tbl.QCSamples, " & _
             "Process_Tbl.MDSamples, Process_Tbl.Rewraps, Process_Tbl.Spare, Process_Tbl.Short, Process_Tbl.Extra, " & _
             "Process_Tbl.MSSamples, Process_Tbl.ManDate, Process_Tbl.PProductID " & _
             "FROM Product_Tbl INNER JOIN (ProductDetail_Tbl INNER JOIN (ShiftCalc_Tbl INNER JOIN Process_Tbl " & _
             "ON ShiftCalc_Tbl.ShiftDate = Process_Tbl.PkgDate) ON ProductDetail_Tbl.ProductID = Process_Tbl.PProductID) " & _
             "ON Product_Tbl.ProductID = ProductDetail_Tbl.ProductGroup " & _
             "WHERE (((Process_Tbl.PkgDate) Between #" & SDate & "#" & " And #" & FDate & "#)) ;"
                
Me.Child52.Requery

End Sub

But keep getting the following syntax error and cant work out where I am going wrong!

Run-Time error '3075':

Between operator without And in query expression

'Product_Tbl.ProductID = ProductDetail_Tbl.ProductGroup WHERE (((Process_Tbl.PkgDate) Between #26/11/2009#'.

 

[JANR]

"WHERE (((Process_Tbl.PkgDate) Between #" & SDate & "#" & " And " & #" & FDate & "#)) ;"

Looks like you have missed the closing " to seperate AND, try an add what's marked in RED

JR

 

[MStef]

Try next:
.....Between #" & [Forms]![FormName]![FieldName] & "#" & "And #" & [Forms]![FormName]![FieldName] & "#)) ;"

 

[sphynx]

Thanks for the quick response but unfortunately that gives me the following error

Compile Error
Expected: End of Statement

 

[sphynx]

MStef, unfortunately your suggestion gives me the following error:

Runtime error '2342':
A RUNSQL action requires an argument consisting of a SQL Statement

Any other suggestions would be most welcome

 

[JANR]

Runtime error '2342':
A RUNSQL action requires an argument consisting of a SQL Statement

Should have seen this first, you can't use Docmd.RunSql againt SELECT statements only INSERT INTO, UPDATE, DELETE etc.

You can however assign this SQL-string to a recordsource of a form, listbox,combobox. It depends on what you want to do.

JR

 

[sphynx]

I need to filter a query via a date range from within a form among with other criteria.

I can link the query to combo boxes for certain criteria but could not get it work for the date range

I was hoping to do this via VBA & SQL on hitting the filter button to change the query, Hence the FDate & SDate variables in my code. Basically I was going to rebuild the whole query to suit the date criteria entered.

Im not sure where to go with this now!!!

Any ideas would be welcome

 

[JANR]

To assign a SQL-String to a RowSource or RecordSource you can something like this:

Private Test_Sub()
Dim strSQL As String
  strSQL = "SELECT.........."
  Me!RecordSource = strSQL
  Me!Requery
End Sub

JR