Proper punctuation in VBA

[yxqzme2]

I am trying to create a query using VBA code to sperate the days worked by shifts.

i am using a combo list for the dates to be populated by a table and then i want to use a between criteria to sort the shifts (IE shift one is from 701 to 1500, Shift 2 is from 1501 to 2300 and so on)

i am only having problems with the Between function.

please ignore silly table names as i am self taught using any info i can get from the internet.

Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM WireUse "

If Me.lstCounties.ItemsSelected.Count <> 0 Then
If Me.lstCounties.ItemsSelected.Count > 0 Then
For lngLoop = 0 To Me.lstCounties.ItemsSelected.Count - 1
If lngLoop = 0 Then
strIDs = strIDs & "'" & Me.lstCounties.ItemData(Me.lstCounties.ItemsSelected(lngLoop))
Else
strIDs = strIDs + "', '" & Me.lstCounties.ItemData(Me.lstCounties.ItemsSelected(lngLoop))
End If
Next lngLoop
End If
strWhere = strWhere & "Where Wireuse.date in (" & strIDs & "') AND "
strIDs = ""
End If

strWhere = strWhere & "WireUse.TIME = Between 701 AND 1500"

strSQL = strSQL & strWhere & " order by wireuse.date, wireuse.TIME"

MyDB.QueryDefs.Delete "Shift A Query"
Set qdef = MyDB.CreateQueryDef("Shift A Query", strSQL)

MsgBox "Shift A Complete", vbInformation, "Operation Complete"

For Each varItem In Me.lstCounties.ItemsSelected
Me.lstCounties.Selected(varItem) = False
Next varItem

Forms![Main Form]![Text30].BackColor = 65280


Exit_cmdOpenQuery_Click:
Exit Sub

Err_Err_Command30_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

The problem i think is in my puntuation, is i put
strWhere = strWhere & "WireUse.TIME = Between_701_AND_1500"
it will create the query but will of course put the underscores in and that wont work. i dont know of a way to use multiple instances of "" in one line so i am stuck. i hope this is not too confusing, but would appreciate any help. Thanks in advance.

 

[HiTechCoach]

Try:

strWhere = strWhere & " WireUse.TIME  Between #7:01#  AND #15:00#"

or maybe

strWhere = strWhere & " TimeValue(WireUse.TIME)  Between #7:01#  AND #15:00#"

Note: The delimiter for date/time values is #

Note 2: To include a quote within a literal string use two (double) together or Chr(34).

Examples:

   "... Where  [MyStringField] = """ & strVar & """"

or

  "... Where [MyStringField] = " Chr(34) & strVar & Chr(34)