By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,156 Members | 1,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,156 IT Pros & Developers. It's quick & easy.

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

P: 3
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 "frmSchedule" 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?
Jul 3 '17 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "([User]='%U') AND " _
  4.        & "([wrkDate] Between Date() And DateAdd('d',7,Date()))"
  5. strSQL = Replace(strSQL, "%U, User)
  6. Call DoCmd.OpenForm(FormName:="frmSchedule" _
  7.                   , View:=acFormDS _
  8.                   , 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.) :
Expand|Select|Wrap|Line Numbers
  1. 'SQLDate takes varDate in Date/Time or YYYYMMDD format and returns it
  2. 'formatted for use in SQL.  If blnHash then puts '#'s around it.
  3. '2015-04-26 Updated to support SQL Server format of yyyy-m-d H:m:s.
  4. Public Function SQLDate(ByVal varDate As Variant _
  5.                       , Optional blnHash As Boolean = True) As String
  6.     If IsEmpty(varDate) Or varDate = "" Then Exit Function
  7.     If IsDate(varDate) Then
  8.         varDate = CDate(varDate)
  9.         If TimeValue(varDate) > 0 Then
  10.             SQLDate = Format(varDate, IIf(DateValue(varDate) > 0 _
  11.                                         , "yyyy\-m\-d ", "") & "HH\:nn\:ss")
  12.         Else
  13.             SQLDate = Format(varDate, "yyyy\-m\-d")
  14.         End If
  15.     ElseIf Len(varDate) = 8 Then
  16.         SQLDate = Left(varDate, 4) & "-" & _
  17.                   Val(Mid(varDate, 5, 2)) & "-" & _
  18.                   Val(Right(varDate, 2))
  19.     ElseIf Len(varDate) = 6 Then
  20.         SQLDate = Left(varDate, 4) & "-" & Val(Right(varDate, 2)) & "-1"
  21.     End If
  22.     If blnHash And SQLDate > "" Then SQLDate = "#" & SQLDate & "#"
  23. End Function

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 634
Hi

First thing I think this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmSchedule" acFormDS, , [User] = User
should like this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmSchedule", acFormDS, , [User] = 'User'
assuming [User] is a text field.

To incorporate a date filter you need something like this
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 3 '17 #2

P: 3
First MTB, user is a global variable, and i need the date to also filter away all the past dates as well.
Jul 3 '17 #3

P: 3
[User] is a text var on the datasheet, but User is a Global var that hold the current user logged into the db
Jul 3 '17 #4

Expert 100+
P: 634
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmSchedule", acFormDS, , [User] = '" & User & "' AND wrkDate = #" & Format(DateAdd("d",7,Date),"mm/dd/yyyy") & "#"
  2.  
This should return all records for the specified User with a wrkDate 7 days from to-day.
Jul 3 '17 #5

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "([User]='%U') AND " _
  4.        & "([wrkDate] Between Date() And DateAdd('d',7,Date()))"
  5. strSQL = Replace(strSQL, "%U, User)
  6. Call DoCmd.OpenForm(FormName:="frmSchedule" _
  7.                   , View:=acFormDS _
  8.                   , 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.) :
Expand|Select|Wrap|Line Numbers
  1. 'SQLDate takes varDate in Date/Time or YYYYMMDD format and returns it
  2. 'formatted for use in SQL.  If blnHash then puts '#'s around it.
  3. '2015-04-26 Updated to support SQL Server format of yyyy-m-d H:m:s.
  4. Public Function SQLDate(ByVal varDate As Variant _
  5.                       , Optional blnHash As Boolean = True) As String
  6.     If IsEmpty(varDate) Or varDate = "" Then Exit Function
  7.     If IsDate(varDate) Then
  8.         varDate = CDate(varDate)
  9.         If TimeValue(varDate) > 0 Then
  10.             SQLDate = Format(varDate, IIf(DateValue(varDate) > 0 _
  11.                                         , "yyyy\-m\-d ", "") & "HH\:nn\:ss")
  12.         Else
  13.             SQLDate = Format(varDate, "yyyy\-m\-d")
  14.         End If
  15.     ElseIf Len(varDate) = 8 Then
  16.         SQLDate = Left(varDate, 4) & "-" & _
  17.                   Val(Mid(varDate, 5, 2)) & "-" & _
  18.                   Val(Right(varDate, 2))
  19.     ElseIf Len(varDate) = 6 Then
  20.         SQLDate = Left(varDate, 4) & "-" & Val(Right(varDate, 2)) & "-1"
  21.     End If
  22.     If blnHash And SQLDate > "" Then SQLDate = "#" & SQLDate & "#"
  23. End Function
Jul 3 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.