Dateadd and datetime.now: cannot make them work for me....

[Cmayes95]

I am trying to create a schedule that will filter on load by username (got this part covered) and by date. The date portion MUST show ONLY today plus 7 days. Ignoring olf dates and dates more then 7 days ahead....

So when the user clicks their "View Schedule" button, it loads a datasheet form and filters the user like so:

'DoCmd.OpenForm "frmSchedul e" acFormDS, , [User] = User'

But i cannit figure out how to add the time restriction to the where feild. The date they are supposed to work is stored in [wrkDate].....

Can anyone help?

[MikeTheBike]

Hi

First thing I think this

DoCmd.OpenForm "frmSchedule" acFormDS, , [User] = User

should like this

DoCmd.OpenForm "frmSchedule", acFormDS, , [User] = 'User'

assuming [User] is a text field.

To incorporate a date filter you need something like this

DoCmd.OpenForm "frmSchedule", acFormDS, , [User] = 'User' AND wrkDate = #" & Format(DateAdd("d",7,Date),"mm/dd/yyyy") & "#"

If you computer default date format is Americate/Continental then you will not need the Format() function

HTH


MTB

[Cmayes95]

First MTB, user is a global variable, and i need the date to also filter away all the past dates as well.

[Cmayes95]

[User] is a text var on the datasheet, but User is a Global var that hold the current user logged into the db

[MikeTheBike]

If User is a global variable the code you posed will not work, even with the syntax error corrected.
I think your code should look like this

DoCmd.OpenForm "frmSchedule", acFormDS, , [User] = '" & User & "' AND wrkDate = #" & Format(DateAdd("d",7,Date),"mm/dd/yyyy") & "#"

This should return all records for the specified User with a wrkDate 7 days from to-day.

[NeoPa]

I recommend you never use positional parameters for procedures with more than two parameters at most, and certainly not for those where you want to use default values. Another piece of good advice is to formulate your filter into a string separately from the procedure call.

So, something like the following is what you're looking for :

Dim strSQL As String

strSQL = "([User]='%U') AND " _
       & "([wrkDate] Between Date() And DateAdd('d',7,Date()))"
strSQL = Replace(strSQL, "%U, User)
Call DoCmd.OpenForm(FormName:="frmSchedule" _
                  , View:=acFormDS _
                  , WhereCondition:=strSQL)

I assume [User] is a string variable. It's not clear from your original code nor from your question.

As Date() is actually a valid reference in itself there's no need to convert it into a literal on this occasion. However, when you do it's good practice to have a function which returns date values as valid date strings. More on this can be found at Literal DateTimes and Their Delimiters (#).

I have one I'm happy to share (Why create a new one if there's one already available? Unless you want the experience of course.) :

'SQLDate takes varDate in Date/Time or YYYYMMDD format and returns it
'formatted for use in SQL.  If blnHash then puts '#'s around it.
'2015-04-26 Updated to support SQL Server format of yyyy-m-d H:m:s.
Public Function SQLDate(ByVal varDate As Variant _
                      , Optional blnHash As Boolean = True) As String
    If IsEmpty(varDate) Or varDate = "" Then Exit Function
    If IsDate(varDate) Then
        varDate = CDate(varDate)
        If TimeValue(varDate) > 0 Then
            SQLDate = Format(varDate, IIf(DateValue(varDate) > 0 _
                                        , "yyyy\-m\-d ", "") & "HH\:nn\:ss")
        Else
            SQLDate = Format(varDate, "yyyy\-m\-d")
        End If
    ElseIf Len(varDate) = 8 Then
        SQLDate = Left(varDate, 4) & "-" & _
                  Val(Mid(varDate, 5, 2)) & "-" & _
                  Val(Right(varDate, 2))
    ElseIf Len(varDate) = 6 Then
        SQLDate = Left(varDate, 4) & "-" & Val(Right(varDate, 2)) & "-1"
    End If
    If blnHash And SQLDate > "" Then SQLDate = "#" & SQLDate & "#"
End Function