Solved

Setting a SQL Date parameter in a Where clause

Posted on 2013-06-22
1
553 Views
Last Modified: 2013-06-22
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
Comment
Question by:GPSPOW
1 Comment
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39268480
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to insert parameter value in table 2 24
Run Stored Procedure uisng ADO 5 23
error in For & Next statements 4 25
MS Access Weekly query 11 14
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question