Date Queries - #10/01/2004# - #1/10/2004#

Date Queries - #10/01/2004# - #1/10/2004#

Post by clarko » Wed, 20 Oct 2004 14:38:59


I have a field with dates in an Access database. The format of date is
short date "19/06/2004". In my regional settings I have as date format
"dd/mm/yyyy".

When I create a query from design to get the data from 10/01/2004 in
criteria I type #10/01/2004#. In SQL I checked and had #1/10/2004#
which is opposite.

The problem is that I am creating query from VBA and by settings date
to be #10/01/2004#, I don't get any records. When I create it from
DESIGN I get results.

Is there a reason for this ?
 
 
 

Date Queries - #10/01/2004# - #1/10/2004#

Post by PC Datashe » Wed, 20 Oct 2004 14:47:08

When using #'s, dates must be in the American format of MM/DD/YYYY.
(Courtesy of Chuck Grimsby)

 
 
 

Date Queries - #10/01/2004# - #1/10/2004#

Post by Christina » Wed, 20 Oct 2004 15:28:15

Thanks a lot, I will try it and let you know.



*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

Date Queries - #10/01/2004# - #1/10/2004#

Post by dreadnough » Wed, 20 Oct 2004 22:31:43

You might find these handy:

Function MakeUSDate(x As Variant)
If Not IsDate(x) Then Exit Function
MakeUSDate = "#" & Format(Month(x), "00") & "/" & Format(Day(x), "00")
& "/" & Format(Year(x), "0000") & "#"
End Function

Public Function MakeUSDateTime(InDateTime) As String
'returns a datestring in the form #mm/dd/yy hh:nn# - to the minute
Dim a As String, b As String, c As String, Tm As String
a = MakeUSDate(InDateTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart("h", InDateTime), "00") & ":" &
Format(DatePart("n", InDateTime), "00")
MakeUSDateTime = b & " " & Tm & "#"
End Function

Public Function MakeUSDateExactTime(InDateTime) As String
'returns a datestring in the form #mm/dd/yy hh:nn# - to the second
Dim a As String, b As String, c As String, Tm As String
a = MakeUSDate(InDateTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart("h", InDateTime), "00") & ":" &
Format(DatePart("n", InDateTime), "00") & ":" & Format(DatePart("s",
InDateTime), "00")
MakeUSDateExactTime = b & " " & Tm & "#"
 
 
 

Date Queries - #10/01/2004# - #1/10/2004#

Post by dreadnough » Wed, 20 Oct 2004 22:32:02

You might find these handy:

Function MakeUSDate(x As Variant)
If Not IsDate(x) Then Exit Function
MakeUSDate = "#" & Format(Month(x), "00") & "/" & Format(Day(x), "00")
& "/" & Format(Year(x), "0000") & "#"
End Function

Public Function MakeUSDateTime(InDateTime) As String
'returns a datestring in the form #mm/dd/yy hh:nn# - to the minute
Dim a As String, b As String, c As String, Tm As String
a = MakeUSDate(InDateTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart("h", InDateTime), "00") & ":" &
Format(DatePart("n", InDateTime), "00")
MakeUSDateTime = b & " " & Tm & "#"
End Function

Public Function MakeUSDateExactTime(InDateTime) As String
'returns a datestring in the form #mm/dd/yy hh:nn# - to the second
Dim a As String, b As String, c As String, Tm As String
a = MakeUSDate(InDateTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart("h", InDateTime), "00") & ":" &
Format(DatePart("n", InDateTime), "00") & ":" & Format(DatePart("s",
InDateTime), "00")
MakeUSDateExactTime = b & " " & Tm & "#"
 
 
 

Date Queries - #10/01/2004# - #1/10/2004#

Post by Douglas J. » Thu, 21 Oct 2004 06:19:42

Why bother with functions like that, Terry? All you need is the built-in
Format function.

See http://www.yqcomputer.com/ "The Access Web" for
a far simpler approach.

--
Doug Steele, Microsoft Access MVP
http://www.yqcomputer.com/
(no e-mails, please!)
 
 
 

Date Queries - #10/01/2004# - #1/10/2004#

Post by Chuck Grim » Thu, 21 Oct 2004 07:02:10


Personally, I prefer to use the date ambiguous ISO format:
YYYY-MM-DD HH:NN:SS
and quote marks (either single or double).

Neither Access or SQL are confused by that format, and few users as
well. (Although many users might look at you strangely as you type it
in! <Grin>)


On 19 Oct 2004 06:31:43 -0700, XXXX@XXXXX.COM (Terry Bell)






--
Snow Is Gods Way Of Telling Us To Take A Day Off.
 
 
 

Date Queries - #10/01/2004# - #1/10/2004#

Post by dreadnough » Thu, 21 Oct 2004 13:08:42

Mainly because I didn't know of that method - I was just trying to
save the poster some work, as the first reply didn't suggest a
solution.
Thanks for the tip anyway