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

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

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

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