Solved

Setting a SQL Date parameter in a Where clause

Posted on 2013-06-22
1
552 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 5 54
Launch app from Access custom ribbon 8 33
Normalization of a table 19 74
Access 2007 - Create Query That Returns One Record Per Customer 4 31
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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