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 ?
6  1684 
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 ?
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!
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 ?
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 ?
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 ?
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 ?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Thomas R. Hummel |
last post by:
Hello,
I am importing data that lists rates for particular coverages for a
particular period of time. Unfortunately, the data source isn't very
clean. I've come up with some rules that I think...
|
by: Anton.Nikiforov |
last post by:
Hello everybody!
Does someone know how to build hierarchical queries to the postgresql?
I have a table with tree in it (id, parent)
and need to find a way from any point of the tree to any...
|
by: Kenneth |
last post by:
I have a Query that consist of a lot of different sales data, and one of the
colums are different date. The date goes from 1jan2003 til 31jan2003. in
this Query I only want the salesdata for...
|
by: nick_faye |
last post by:
hi,
how can i set the dates in a fieldn in my tables in ms access to
handle only dates and no time? in creating my table, i set its type
to 'date/time' with 'short date' format. but when i...
|
by: Adam |
last post by:
Hi All,
This may be a really obvious thing that I'm missing ... but if anyone can
help, I'd appreciate it.
I have MS Access 2000:
I'm using it for a CRM type database. I have a table with...
|
by: captain |
last post by:
hi all,
I am really do not know how to get this. Tried all types of queries.
The requirement is as below
itemid maxqty Mdate (date is when the max qty went out)
001 5 5/1/2004...
|
by: captain |
last post by:
hi all,
I am really do not know how to get this. Tried all types of queries.
The requirement is as below
itemid maxqty Mdate (date is when the max qty went out)
001 5 5/1/2004...
|
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date...
|
by: Tony Miller |
last post by:
All
I have an aggregate query using the function Month & Year on a
datereceived field ie:
TheYear: Year()
TheMonth: Month()
These are the group by fields to give me a Count on another field by...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
