Date not coming up!

  • Thread starter Thread starter mem
  • Start date Start date
M

mem

Hi, I am trying to get results in a query using the between two dates. (ie
between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie
1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some
reason 1/31/2008 will not come up in the results. I have 5 fields that need
to show but are not. In the past I have had not problem but since I got my
new computer I can not get the results. Any help is greatly appreciated. I
have Access 2002.
 
The problem is that 1/31/2008 10:27:16 AM is after 1/31/2008 00:00:00.
(midnight).
All dateTime fields in Access have a time component. If no time is
specified the time component is midnight.

You can do one of the following.
Field: DateValue(YourDateField)
Criteria: Between #1/1/2008# and #1/31/2008#

OR (faster)
Field: YourDateField
Criteria: >= #1/1/2008# and <#2/1/2008#

OR
if you don't care if you get any records that have a time component of
midnight
Field: YourDateField
Criteria: Between #1/1/2008# and #2/1/2008#

OR
Field: YourDateField
Criteria: Between #1/1/2008# and DateAdd("s",60*60*24-1,#1/31/2008#)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
The problem is that your field includes time. If you specifiy
< = #1/31/2008#
#1/31/2008# will be looking at
1/31/2008 12:00:00 AM

Any row that has 1/31/2008 will be seen as greater than that. You may try
DateValue([SomeDate]) <= #1/31/2008#
 
Thank you! I do have one more question. Sorry! How would I write it in
code? I have a form set up that the user types in two dates (start and end)
and then it will open the query based on those dates. Here is my code:

Dim FName As String
FName = "frm_NCs_Disp_Scp"

Dim WAll As String, WField As String, WMaterial As String, WProcess As
String

WAll = "startdate between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
WField = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LF'"
WMaterial = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LM' OR 'LP'"
WProcess = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'SF' OR 'SQ' OR 'LR' OR 'LT'"

If Me.optAll.Enabled = True Then
DoCmd.OpenForm FName, , , WAll
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WField
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WMaterial
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WProcess
End If
End Sub


Klatuu said:
The problem is that your field includes time. If you specifiy
< = #1/31/2008#
#1/31/2008# will be looking at
1/31/2008 12:00:00 AM

Any row that has 1/31/2008 will be seen as greater than that. You may try
DateValue([SomeDate]) <= #1/31/2008#
--
Dave Hargis, Microsoft Access MVP


mem said:
Hi, I am trying to get results in a query using the between two dates. (ie
between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie
1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some
reason 1/31/2008 will not come up in the results. I have 5 fields that need
to show but are not. In the past I have had not problem but since I got my
new computer I can not get the results. Any help is greatly appreciated. I
have Access 2002.
Click to expand...
Click to expand...
 
WAll = "DateValue(startdate) between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
--
Dave Hargis, Microsoft Access MVP


mem said:
Thank you! I do have one more question. Sorry! How would I write it in
code? I have a form set up that the user types in two dates (start and end)
and then it will open the query based on those dates. Here is my code:

Dim FName As String
FName = "frm_NCs_Disp_Scp"

Dim WAll As String, WField As String, WMaterial As String, WProcess As
String

WAll = "startdate between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
WField = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LF'"
WMaterial = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LM' OR 'LP'"
WProcess = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'SF' OR 'SQ' OR 'LR' OR 'LT'"

If Me.optAll.Enabled = True Then
DoCmd.OpenForm FName, , , WAll
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WField
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WMaterial
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WProcess
End If
End Sub


Klatuu said:
The problem is that your field includes time. If you specifiy
< = #1/31/2008#
#1/31/2008# will be looking at
1/31/2008 12:00:00 AM

Any row that has 1/31/2008 will be seen as greater than that. You may try
DateValue([SomeDate]) <= #1/31/2008#
--
Dave Hargis, Microsoft Access MVP


mem said:
Hi, I am trying to get results in a query using the between two dates. (ie
between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie
1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some
reason 1/31/2008 will not come up in the results. I have 5 fields that need
to show but are not. In the past I have had not problem but since I got my
new computer I can not get the results. Any help is greatly appreciated. I
have Access 2002.
Click to expand...
Click to expand...
Click to expand...
 
Thanks I tried that and I get run-time error 2501. The openform action was
canceled. For some reason I cannot get it to see the date without the time.
I also did in the query <=#1/31/2088# and it gives me that date and
everything else after. I did not have this problem with my old computer and
are wondering if it is something in the settingd? I am completely fustrated!
Thanks!

Klatuu said:
WAll = "DateValue(startdate) between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
--
Dave Hargis, Microsoft Access MVP


mem said:
Thank you! I do have one more question. Sorry! How would I write it in
code? I have a form set up that the user types in two dates (start and end)
and then it will open the query based on those dates. Here is my code:

Dim FName As String
FName = "frm_NCs_Disp_Scp"

Dim WAll As String, WField As String, WMaterial As String, WProcess As
String

WAll = "startdate between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
WField = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LF'"
WMaterial = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LM' OR 'LP'"
WProcess = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'SF' OR 'SQ' OR 'LR' OR 'LT'"

If Me.optAll.Enabled = True Then
DoCmd.OpenForm FName, , , WAll
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WField
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WMaterial
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WProcess
End If
End Sub


Klatuu said:
The problem is that your field includes time. If you specifiy
< = #1/31/2008#
#1/31/2008# will be looking at
1/31/2008 12:00:00 AM

Any row that has 1/31/2008 will be seen as greater than that. You may try
DateValue([SomeDate]) <= #1/31/2008#
--
Dave Hargis, Microsoft Access MVP


:

Hi, I am trying to get results in a query using the between two dates. (ie
between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie
1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some
reason 1/31/2008 will not come up in the results. I have 5 fields that need
to show but are not. In the past I have had not problem but since I got my
new computer I can not get the results. Any help is greatly appreciated. I
have Access 2002.
Click to expand...
Click to expand...
Click to expand...
 
The DateValue can't be inside the quotes. It will just be seen as a string.
Here is the correct way:

WAll = Cstr(DateValue(startdate)) & " between # " & _
Me![Start] & "# AND #" & _
Me![End] & "#"

--
Dave Hargis, Microsoft Access MVP


mem said:
Thanks I tried that and I get run-time error 2501. The openform action was
canceled. For some reason I cannot get it to see the date without the time.
I also did in the query <=#1/31/2088# and it gives me that date and
everything else after. I did not have this problem with my old computer and
are wondering if it is something in the settingd? I am completely fustrated!
Thanks!

Klatuu said:
WAll = "DateValue(startdate) between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
--
Dave Hargis, Microsoft Access MVP


mem said:
Thank you! I do have one more question. Sorry! How would I write it in
code? I have a form set up that the user types in two dates (start and end)
and then it will open the query based on those dates. Here is my code:

Dim FName As String
FName = "frm_NCs_Disp_Scp"

Dim WAll As String, WField As String, WMaterial As String, WProcess As
String

WAll = "startdate between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
WField = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LF'"
WMaterial = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LM' OR 'LP'"
WProcess = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'SF' OR 'SQ' OR 'LR' OR 'LT'"

If Me.optAll.Enabled = True Then
DoCmd.OpenForm FName, , , WAll
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WField
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WMaterial
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WProcess
End If
End Sub


:

The problem is that your field includes time. If you specifiy
< = #1/31/2008#
#1/31/2008# will be looking at
1/31/2008 12:00:00 AM

Any row that has 1/31/2008 will be seen as greater than that. You may try
DateValue([SomeDate]) <= #1/31/2008#
--
Dave Hargis, Microsoft Access MVP


:

Hi, I am trying to get results in a query using the between two dates. (ie
between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie
1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some
reason 1/31/2008 will not come up in the results. I have 5 fields that need
to show but are not. In the past I have had not problem but since I got my
new computer I can not get the results. Any help is greatly appreciated. I
have Access 2002.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help. Although it is still not working now I get Type
mismatch. I am not a novice to Access so I know there is something more then
just the program itself. I did not have this problem with the last hard
drive. The only way I can get it to work is to have the * for like after the
date. For some reason it is reading the date as a string. I can't for the
life of me figure out why. I have been playing with it all day.

Klatuu said:
The DateValue can't be inside the quotes. It will just be seen as a string.
Here is the correct way:

WAll = Cstr(DateValue(startdate)) & " between # " & _
Me![Start] & "# AND #" & _
Me![End] & "#"

--
Dave Hargis, Microsoft Access MVP


mem said:
Thanks I tried that and I get run-time error 2501. The openform action was
canceled. For some reason I cannot get it to see the date without the time.
I also did in the query <=#1/31/2088# and it gives me that date and
everything else after. I did not have this problem with my old computer and
are wondering if it is something in the settingd? I am completely fustrated!
Thanks!

Klatuu said:
WAll = "DateValue(startdate) between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
--
Dave Hargis, Microsoft Access MVP


:

Thank you! I do have one more question. Sorry! How would I write it in
code? I have a form set up that the user types in two dates (start and end)
and then it will open the query based on those dates. Here is my code:

Dim FName As String
FName = "frm_NCs_Disp_Scp"

Dim WAll As String, WField As String, WMaterial As String, WProcess As
String

WAll = "startdate between #" & _
Me![Start] & "# AND #" & _
Me![End] & "#"
WField = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LF'"
WMaterial = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'LM' OR 'LP'"
WProcess = "[NC_DATE_CLS] between #" & _
Me![Start] & "# AND #" & _
Me![End] & "# AND [NC_TYPE] = 'SF' OR 'SQ' OR 'LR' OR 'LT'"

If Me.optAll.Enabled = True Then
DoCmd.OpenForm FName, , , WAll
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WField
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WMaterial
ElseIf Me.OptField.Enabled = True Then
DoCmd.OpenForm FName, , , WProcess
End If
End Sub


:

The problem is that your field includes time. If you specifiy
< = #1/31/2008#
#1/31/2008# will be looking at
1/31/2008 12:00:00 AM

Any row that has 1/31/2008 will be seen as greater than that. You may try
DateValue([SomeDate]) <= #1/31/2008#
--
Dave Hargis, Microsoft Access MVP


:

Hi, I am trying to get results in a query using the between two dates. (ie
between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie
1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some
reason 1/31/2008 will not come up in the results. I have 5 fields that need
to show but are not. In the past I have had not problem but since I got my
new computer I can not get the results. Any help is greatly appreciated. I
have Access 2002.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top