473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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

5 808
MikeTheBike
639 Expert 512MB
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
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
[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
MikeTheBike
639 Expert 512MB
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
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: Sorisio, Chris | last post by:
Ladies and gentlemen, I've imported some data from a MySQL database into a Python dictionary. I'm attempting to tidy up the date fields, but I'm receiving a 'mx.DateTime.Error: cannot convert...
11
by: | last post by:
Hi! I will get the date from now (DateTime.Now is 18.12.2003) but exact 16 years ago (18.12.1987). when I make something like this: //we set min. employee age to 16 years TimeSpan...
3
by: Darren Clark | last post by:
System.DateTime.Now it supposedly exists .. but doesnt seem to exist on my machine? How can i get the current date from the systme?
2
by: mp | last post by:
I am using : SystemDate= System.DateTime.Now.Year.ToString() +System.DateTime.Now.Month.ToString()+System.DateTime.Now.Day.ToString(); ...
4
by: John J. Hughes II | last post by:
I have several functions that save the time when they did certain tasks to an SQL table. This was working fine as far as I could tell but I now have systems that are updating thousands of records...
4
by: Alan Silver | last post by:
Hello, I have a user control that has a property StartYear. Logically enough, this takes an Int32 value. I have no problem doing something like ... <ctls:fred id="frdFred" StartYear="2000"...
2
by: Rene A | last post by:
Hi, I want to get the current date minus 60 days, how can i do that. This is what i have /want: DateTime now = DateTime.Now; Result = DateTime.Now.ToString("yyyyMMdd") + "')-60; it must...
3
by: Curious | last post by:
I have code below. But it won't compile. Error: Cannot implicitly convert type 'System.TimeSpan' to 'System.DateTime' DateTime now = DateTime.Now.TimeOfDay; DateTime openTime =...
0
by: Curious | last post by:
I have the code below. But it won't compile. //Compiling error: Cannot implicitly convert type 'System.TimeSpan' to 'System.DateTime' DateTime now = DateTime.Now.TimeOfDay; DateTime...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.