Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 12th, 2003, 06:54 PM
Authorized User
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date in Access VBA (SQL)

I need some assistance folks on an issue that I can't get to function for me. I am trying to run a select query in A2K that has a WHERE clause of this:

WHERE (((tblMultiple.dtmDate) Between #9/1/2003# And #9/30/2003#))

That works great but I need this to be a dynamic date that uses date functions (DateAdd & DateSerial) to get the first and last day of the previous month and loads that into variables when the application starts - gdtmStart & gdtmEnd.

When I try to get this into the query as

WHERE (((tblMultiple.dtmDate) Between #' & gdtmStart & '# And #' & gdtmEnd & '#))

I get an error message stating that "The expression you entered has an invalid date value".

Could anyone help me to resolve this issue....???

Kenny Alligood
Kenny Alligood
Old September 12th, 2003, 08:04 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi Kenny,

You need double quotes in a few more spots:

Private Sub Command5_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim gdtmStart As Date
    Dim gdtmEnd As Date
    Dim strSQL As String

    Set db = CurrentDb

    gdtmStart = Me.txtStartDate
    gdtmEnd = Me.txtEndDate

    strSQL = "SELECT ID, dtmDate FROM tblMultiple " & _
                "WHERE tblMultiple.dtmDate Between #" & gdtmStart & "#" & " And #" & gdtmEnd & "#;"

    Set rst = db.OpenRecordset(strSQL)

    Do Until rst.EOF
        For Each fld In rst.Fields
            Debug.Print fld.Value & ";";



End Sub



Old September 14th, 2003, 08:35 AM
Authorized User
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts

Thanx Bob that worked the charm I needed!!

Kenny Alligood

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Unbound Date field to link with SQL VBA JackalBW BOOK: Access 2003 VBA Programmer's Reference 0 December 9th, 2007 06:29 AM
SQL Date query in Excel VBA lanewalk Excel VBA 2 September 27th, 2006 02:04 AM
Date conversion in Access VBA goels Access VBA 2 October 13th, 2004 12:26 PM
Date Difference in Access VBA amit_jain76 Access VBA 4 January 14th, 2004 11:55 AM
Access SQL/VBA help danielwajnberg Access 4 September 2nd, 2003 06:56 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.