Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 June 5th, 2005, 06:57 AM
Authorized User
 
Join Date: Mar 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default record.source WHERE date comparison

Access 2003 ADP

I have a report in which I prompt for selection criteria in a pop-up form:
    SysCmd(acSysCmdGetObjectState, acForm, "popfrmEventReportFilter")

I then build a WHERE statement using data from the pop-up form like this:
    If (Forms("popfrmEventReportFilter").optConfirmed) Then
        strWhere = "Events.EventConfirmedFlag <> 0"
    If Not (IsNull(Forms("popfrmEventReportFilter").cmbParent Event)) Then
        strWhere = strWhere & "Events.EventParentEventID = " &

Forms("popfrmEventReportFilter").cmbParentEvent
    etc...

I then build a Me.RecordSource statement using strWhere:
    Me.RecordSource = _
    "SELECT Events.*, Locations.* " _
    & "FROM Events LEFT OUTER JOIN " _
    & "Locations ON Events.EventLocationID = Locations.LocID " _
    & strWhere & " ORDER BY Events.EventLookup, Events.EventID "

THE ABOVE WORKS PERFECTLY

I then try to introduce a date comparison and its a nightmare!

I have tried so many syntaxes for comparing dates that I could not possibly list them all here even

if I could remember them all!

The books I have seem to be saying I should use # (although it is not clear whether this is still true

for RecordSource SQL statements within an ADP). I have certainly tried # and single quotes and

neither, and I have tried converting the table-date to text - all without success. Perhaps I have

been warm once or twice without realising it!

For example, this statement (my best guess):
    Events.EventStartDateTime >= #01/01/2004# AND Events.EventEndDateTime <=

#12/31/2005#
produces the error:
    Invalid SQL Statement. Check the server filter on the form record source

So what is the syntax to include this logic:
    WHERE table-date (in datetime format) >= form-date (text box - Short Date format)
within a Me.RecordSource statement?



R
__________________
R
 
Old June 5th, 2005, 09:49 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Sounds like a frustrating experience!

Since you've found the key to comparing dates - using the hash (#) - I'm going to suggest that you're overlooking something obvious.

Take a good look at strWhere just before you set the RecordSource and make sure it is ALL built correctly. Make sure you have balanced parenthesis. Make sure that there is a boolean operator (e.g. "AND" or "OR") between each of your comparisons. And make sure the dates within the hashes are valid dates. E.g. #2/30/2005# is not a valid date.

Your example is perfect SQL syntax. But if you concatenate that with what you already have in strWhere without putting in a boolean operator before the date comparison, the syntax will be wrong.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old June 6th, 2005, 05:26 PM
Authorized User
 
Join Date: Mar 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For anyone else who has this problem please note that the following WHERE statement works:
WHERE Events.EventStartDateTime >= '31/10/2004'

I think this works because, being non-American, my short date format is set to dd/mm/yyyy.

All the text books are written for Americans by Americans so they just tell you the date format is mm/dd/yyyy!

R
 
Old January 5th, 2006, 08:03 PM
Registered User
 
Join Date: Jan 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have a similar issue. I wrote a query to import data from Access database into excell spreadsheet. Query is :
sSQL = " SELECT x.a, x.Date, x.b, x.c, x.d, x.e & _
            " FROM x" & _
            " Where (x.a)= '" & vara & "'" & _
            "AND (x.b)='" & varb & "'" & _
            "AND (x.c)='" & varc & "'" & _
            "AND (x.Date) >= " & varDatex & ""

the variables vara, varb, varc, and varDatex are passed to the query using a text boxes in a UserForm. All goes well except all the dates are selected. If I use "<=" comparison, no data is selected. No error is reported. How can I fix it so only data within specifioed date range is selected? I already formate date using CDate (txtDate.txt) function. Seems to me that dates in Access database are all > any date I enter and < any date I enter??? Thanx for help






Similar Threads
Thread Thread Starter Forum Replies Last Post
SOLVED - VBA Help For Row Source and Record Source eusanpe Access VBA 4 May 13th, 2008 11:58 AM
Date Comparison jroxit Classic ASP Databases 5 October 5th, 2007 05:39 PM
Date comparison rajuru Beginning PHP 2 February 19th, 2005 10:33 AM
Date Comparison hoffmann Classic ASP Databases 7 October 21st, 2004 09:00 AM
Date Comparison cmiller PHP How-To 3 June 19th, 2003 02:58 PM





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