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?
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
5 808
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
First MTB, user is a global variable, and i need the date to also filter away all the past dates as well.
[User] is a text var on the datasheet, but User is a Global var that hold the current user logged into the db
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 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 : - 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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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?
|
by: mp |
last post by:
I am using :
SystemDate= System.DateTime.Now.Year.ToString()
+System.DateTime.Now.Month.ToString()+System.DateTime.Now.Day.ToString();
...
|
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...
|
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"...
|
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...
|
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 =...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
|
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...
| |