Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

Setting a SQL Date parameter in a Where clause

I am creating a VBA Access SQL statement to use two stored date variables to create a where clause parameter.

Can someone help me with the syntax?

Here is the code snippet.

If Month(Date) = 1 Then
        DateTo = Format((DateSerial(Year(Date), 1, 1) - 1), "mm/dd/yyyy")
        DateFrom = Format(DateSerial(Year(Date) - 1, 12, 1), "mm/dd/yyyy")
    Else
        DateTo = Format(DateSerial(Year(Date), Month(Date), 1) - 1, "mm/dd/yyyy")
        DateFrom = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm/dd/yyyy")
    End If


          "WHERE (((dbo_BarBillsProration.InsuranceOrderID)='1') AND ((dbo_BarBills.Status)='POSTED') AND ((dbo_BarBills.Type)='FINAL') AND " & _
          "((dbo_BarBills.EffectiveDateTime) Between " & DateTo & "And " & DateFrom & ")) " &


Thanks

Glen
 _
0
GPSPOW
Asked:
GPSPOW
1 Solution
 
Gustav BrockCIOCommented:
All you need is this:

    DateTo = Format(DateSerial(Year(Date), Month(Date), 0), "\#mm\/dd\/yyyy\#")
    DateFrom = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "\#mm\/dd/\yyyy\#")

regardless of the month.

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now