Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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
Location: , FL, USA.
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
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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 & ";";
        Next

        rst.MoveNext
        Debug.Print
    Loop

    rst.Close

End Sub

HTH,

Bob

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

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.