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  1693 
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******@pcdata sheet.com www.pcdatasheet.com
"CLarkou" <cl*****@memrb. com.cy> wrote in message
news:db******** *************** ***@posting.goo gle.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 ?
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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00")
MakeUSDateTime = b & " " & Tm & "#"
End Function
Public Function MakeUSDateExact Time(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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00") & ":" & Format(DatePart ("s",
InDateTime), "00")
MakeUSDateExact Time = b & " " & Tm & "#"
End Fu************* @memrb.com.cy (CLarkou) wrote in message news:<db******* *************** ****@posting.go ogle.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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00")
MakeUSDateTime = b & " " & Tm & "#"
End Function
Public Function MakeUSDateExact Time(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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00") & ":" & Format(DatePart ("s",
InDateTime), "00")
MakeUSDateExact Time = b & " " & Tm & "#"
End Fu************* @memrb.com.cy (CLarkou) wrote in message news:<db******* *************** ****@posting.go ogle.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**********@h otmail.com> wrote in message
news:92******** *************** ***@posting.goo gle.com... 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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00")
MakeUSDateTime = b & " " & Tm & "#"
End Function
Public Function MakeUSDateExact Time(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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00") & ":" & Format(DatePart ("s",
InDateTime), "00")
MakeUSDateExact Time = b & " " & Tm & "#"
End Fu************* @memrb.com.cy (CLarkou) wrote in message
news:<db******* *************** ****@posting.go ogle.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_djsteel e@NOSPAM_canada .com> wrote in message news:<18******* *************@r ogers.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**********@h otmail.com> wrote in message
news:92******** *************** ***@posting.goo gle.com... 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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00")
MakeUSDateTime = b & " " & Tm & "#"
End Function
Public Function MakeUSDateExact Time(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(InDa teTime)
b = Left(a, Len(a) - 1)
Tm = Format(DatePart ("h", InDateTime), "00") & ":" &
Format(DatePart ("n", InDateTime), "00") & ":" & Format(DatePart ("s",
InDateTime), "00")
MakeUSDateExact Time = b & " " & Tm & "#"
End Fu************* @memrb.com.cy (CLarkou) wrote in message
news:<db******* *************** ****@posting.go ogle.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 will work to clean the
data, but I'm having trouble putting those rules into efficient SQL.
The table that I'm dealing with has just under 9M rows...
|
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 other point.
And i would like to have a list of all steps from point A to point B
to make some changes on each step (this is required by the...
|
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 1jan2003. How do I remove the dates
, 2jan2003 til 31jan2003 without removing them from the table, from the
Query? (Because I want to use the data 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 entered values in
it, the data i get was '4/1/2004 4:28:12 PM'. i only want this field
to hold date and no time. can somebody tell me how to get rid of...
|
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 names, date they
first became a customer, and about 3 fields of addional info.
I.e.
| | |
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
002 2 5/10/2004
The actual table has all transaction data as below.
|
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
002 2 5/10/2004
The actual table has all transaction data as below.
|
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 ranges, ie between 24/09/2004
and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?
If I enter the "Between" criteria on different lines it...
|
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
year & month
When I try to place a date filter 'Between x And y ' on an expression
field
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
