Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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

Reply With Quote
  #3 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.