473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date and Query in adp

I'm running into a problem using dates in a query which supplied data
to a report. The WHERE clause of the query is dynamically generated,
based on selections made on a form. Users enter dates in an unbound
text box, with the format set to 'dd/mm/yyyy'.

In the SQL code generated in the report_open event, I set the dates in
the WHERE clause to:

strDates = Forms![frmreports].cboDateField.Column(1) & " BETWEEN
CONVERT(DATETIME, '" & Forms![frmreports].txtStartDate.Value & "', 103)
AND CONVERT(DATETIME, '" & Forms![frmreports].txtStopDate.Value & "',
103)"

with the CONVERT function, and assumed this would elimiate the problems
with Regional Settings on client machines. However, I am getting a
DateConversion error when the report is opened with the dates
31/03/2005 and 18/05/2005 (start and end)

Can anyone give me a suggestion?

TIA

Tim Pascoe

Nov 13 '05 #1
5 2307
Tim:
Date parameters need to be surrounded by pound signs (#) in order to
properly work. This means that the resultant strDates should spit out
something that looks like this:
YourDateField BETWEEN #Date1# and #Date2#

This tells Access to treat the values as dates. If you have the form
fields already formatted as dates on the form, you shouldn't have to
convert them using the CONVERT function, but I could be wrong.

Good luck!
Jana

Nov 13 '05 #2
Jana,

Sorry, Maybe I wasn't clear enough. As an adp (using SQL-Sever 2000),
this is where the confusion is coming in, I think. It's not really
Access that is evaluating the query, and I found I had to use the
CONVERT function to get the query to return data at all. Now the
problem is that it does not always respect the format of the date.

:)

Tim

Nov 13 '05 #3
ti********@cciw.ca wrote in news:1116446319.453342.180740
@g44g2000cwa.googlegroups.com:
I'm running into a problem using dates in a query which supplied data
to a report. The WHERE clause of the query is dynamically generated,
based on selections made on a form. Users enter dates in an unbound
text box, with the format set to 'dd/mm/yyyy'.

In the SQL code generated in the report_open event, I set the dates in
the WHERE clause to:

strDates = Forms![frmreports].cboDateField.Column(1) & " BETWEEN
CONVERT(DATETIME, '" & Forms![frmreports].txtStartDate.Value & "', 103)
AND CONVERT(DATETIME, '" & Forms![frmreports].txtStopDate.Value & "',
103)"

with the CONVERT function, and assumed this would elimiate the problems
with Regional Settings on client machines. However, I am getting a
DateConversion error when the report is opened with the dates
31/03/2005 and 18/05/2005 (start and end)

Can anyone give me a suggestion?

TIA

Tim Pascoe


Are you sure Forms![frmreports].txtStartDate.Value is returning the string
"31/03/2005"? Could it be returning something else? Have you tried
(air script)
Format(Forms![frmreports].txtStartDate.Value,"dd\/mm\/yyyy")?

If this doesn't help perhaps you could Debug.Print the string and be
completely sure what you are sending.

As an aside, and I am not suggesting you do this, I always format the date
as "YYYY-MM-DD" and cast it as datetime or smalldatetime (for those
unfamiliar with T-SQL, smalldatetime is what one gets after one eats
garlic). I have never had any problem with this.
--
Lyle

To subject an enemy belligerent to an unfair trial, to charge him with an
unrecognized crime, or to vent on him our retributive emotions only
antagonizes the enemy nation and hinders the reconciliation necessary to a
peaceful world.

Justice Frank Murphy
Yamashita v. Styer, 327 U.S. 1 (1946)
Nov 13 '05 #4
Tim,

Convert will work fine with dates in dd/mm/yyyy format when you specify
a style of 103 and it will work fine for the american mm/dd/yyyy format
when you specify style 101. However, how will the server know which
one to use? How does it know what the settings are on the client?

I think that you will need to convert all dates used as criteria to the
backwards american format on the client side (I say this because I
think that SQL favors dates in american style) using the format
function. You can then pass it safely to the server for processing
without worrying about locale. For example,

strDates = Forms![frmreports].cboDateField.Column(1) & " BETWEEN
'" & Format(Forms![frmreports].txtStartDate.Value,"mm/dd/yyyy") & "'
AND '" & Format(Forms![frmreports].txtStopDate.Value,"mm/dd/yyyy") & "'

Bill E.
Hollywood, FL

Nov 13 '05 #5
<blush> I didn't catch that.

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Russell | last post by:
I'm having a fit with a query for a range of dates. The dates are being returned from a view. The table/field that they are being selected from stores them as varchar and that same field also...
2
by: BlackFireNova | last post by:
I have an Access 2003 mdb which contains software records. I need to sort on a particular type of software, and then identify and count how many copies there are per each group of that type...
10
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...
7
by: Nicolae Fieraru | last post by:
Hi All, I have a table tblProducts where I have four fields:\ Index, ProductName, EnterDate (as Date/Time - Medium Date), PurchaseDate (Date/Time - Medium Date) The EnterDate is automatically...
4
by: Peter Bailey | last post by:
I have a vba string taht dynamically creates the query which has two dates in it that it grabs off an open form as a string from the textbox. What I generate in vba is: SELECT DOSMBK.Date,...
12
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...
3
by: manning_news | last post by:
Using A2K. I've been asked to modify a report currently requiring only one date parameter to now accept a date range. The main report has 2 subreports and is not bound to a table or query. The...
11
by: Dixie | last post by:
How can I programatically, take some Date/Time fields present in a table in the current database and change their type to text? dixie
10
by: Daniel | last post by:
In Microsoft Access I can write a query that includes the criteria: Between Date()-7 And Date() to retrieve the records from a table that fall within the last week. I'm trying to write a...
2
by: sixdeuce62 | last post by:
Hello, I am trying to create a query that will prompt me to enter the parameter value if beginning date and ending date. I have created everything I need in the query, but I have to manually go...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.