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

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

P: n/a
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 ?
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


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

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"CLarkou" <cl*****@memrb.com.cy> wrote in message
news:db**************************@posting.google.c om...
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 ?

Nov 13 '05 #2

P: n/a
Thanks a lot, I will try it and let you know.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a
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 & "#"
End Fu*************@memrb.com.cy (CLarkou) wrote in message news:<db**************************@posting.google. com>...
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 ?

Nov 13 '05 #4

P: n/a
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 & "#"
End Fu*************@memrb.com.cy (CLarkou) wrote in message news:<db**************************@posting.google. com>...
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 ?

Nov 13 '05 #5

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

See http://www.mvps.org/access/datetime/date0005.htm at "The Access Web" for
a far simpler approach.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Terry Bell" <dr**********@hotmail.com> wrote in message
news:92**************************@posting.google.c om...
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 & "#"
End Fu*************@memrb.com.cy (CLarkou) wrote in message

news:<db**************************@posting.google. com>...
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 ?

Nov 13 '05 #6

P: n/a
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
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:<18********************@rogers.com>...
Why bother with functions like that, Terry? All you need is the built-in
Format function.

See http://www.mvps.org/access/datetime/date0005.htm at "The Access Web" for
a far simpler approach.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Terry Bell" <dr**********@hotmail.com> wrote in message
news:92**************************@posting.google.c om...
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 & "#"
End Fu*************@memrb.com.cy (CLarkou) wrote in message

news:<db**************************@posting.google. com>...
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 ?

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.