Querydef Syntax

  • Thread starter Thread starter Tom Olsen
  • Start date Start date
T

Tom Olsen

All,

I would like to modify the code below so that the list1
object is a listbox of Dates. If a user "Cherry Picks" or
Identifies a range of dates how would I mod the code
below. This code works great with Text.. I've used pound
signs and everything else.

IN Advance thanks for your help..

Code newbie

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Dim Varitem As Variant

Dim strcriteria As String
Dim strsql_orig As String
Dim strsql As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qrymultiselectVendors")

strsql_orig = qdf.SQL
strsql = Left(strsql_orig, Len(strsql_orig) - 1)


For Each Varitem In Me!List5.ItemsSelected

If Len(strcriteria) <> 0 Then
strcriteria = strcriteria & "," & Chr(34) & _
Me!List5.ItemData(Varitem) & Chr(34)
Else
strcriteria = Chr(34) & Me!List5.ItemData(Varitem)
& Chr(34)
End If



Next Varitem

strsql = "select * from Vendors " & _
"where contract_vendor in(" & strcriteria & ");"


If Len(strcriteria) = 0 Then


GoTo lastline
Else








qdf.SQL = strsql

DoCmd.OpenReport "RPT_Bio_Active_By_Vendor",
acViewPreview


End If

qdf.SQL = strsql_orig

lastline:

End Sub
 
Dates need to be delimited with #, not a quote (and they need to be in
mm/dd/yyyy format, regardless of what the short date format has been set to
in Regional Settings), so something like the following change should work:

For Each Varitem In Me!List5.ItemsSelected

If Len(strcriteria) <> 0 Then
strcriteria = strcriteria & "," &
Format$(Me!List5.ItemData(Varitem), "\#mm\/dd\/yyyy\#")
Else
strcriteria = Format$(Me!List5.ItemData(Varitem),
"\#mm\/dd\/yyyy\#")
End If
 
Back
Top