473,898 Members | 3,809 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Passing a date parameter to an Access query in ASP

17 New Member
I have the followig code:


Expand|Select|Wrap|Line Numbers
  1. set conn=Server.CreateObject("ADODB.Connection")
  2. conn.Provider=provider
  3. conn.Open dbpath
  4. set rs = Server.CreateObject("ADODB.recordset")
  5.  
  6. ...
  7.  
  8.  
  9. sql="SELECT department, job, start, [end] FROM Qry_AssignedWeek WHERE employee='" & employee & "' ORDER BY start"
  10. rs.open sql, conn, sunday
Where the Qry_AssignedWee k is the following query in Access2003:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS sunday DateTime;
  2. SELECT *
  3. FROM Qry_AssignedNotCancelled
  4. WHERE (((Qry_AssignedNotCancelled.start) Between [sunday] And [sunday]+7)) OR (((Qry_AssignedNotCancelled.end) Between [sunday] And [sunday]+7));

this code generates the ASP error:

Provider (0x80020005)
Type mismatch.
/schedule/ViewMyShifts.as p, line 70


The "sunday" variable is a string representing a date in format "dd/mm/yyyy"

I have tried to change the "sunday" in "rs.open sql, conn, sunday" with many different options:
int(sunday)
CDate(sunday)
#23/09/2007#

etc.

All returning various errors.

Please, can anyone guide me around this problem?
Sep 20 '07 #1
2 6108
markrawlingson
346 Recognized Expert Contributor
You could try something like this... and scrap the msAccess query all together.

This would calculate when sunday is, and return all records which fall within the last sunday to the next sunday. In the case of this week, as an example, it would return all records from Sunday the 15th of september to Sunday the 22nd of september.

Expand|Select|Wrap|Line Numbers
  1. If WeekDay(Date()) = 1 Then 'it's sunday
  2.    dDateStart = Date() ' it's sunday today so we want to start from today's date
  3. Else
  4.    'Today is not sunday, so we need to find when sunday is. To do this, we'll find the integer value of the weekday, and count backwards to sunday.
  5.    'If today is friday weekDay will return 6 - sunday would be 5 days ago. So weekday*-1 = -6 + 1 = -5
  6.    iDateChange = (WeekDay(Date())*-1)+1
  7.    dDateStart = DateAdd(d,iDateChange,Date())
  8. End If
  9. 'Now we have our start date, we just need our end date, which is easy
  10.  
  11. dDateEnd = DateAdd(d,7,dDateStart)
  12.  
  13. sSQL="SELECT department, job, start, datefield FROM yourtable WHERE employee='" & employee & "' AND datefield BETWEEN " & dDateStart& " AND " & dDateEnd & " ORDER BY datefield"
  14. rs.open sql, conn, 3, 3
  15.  
  16.  
I also don't know why you have rs.open sql, conn, sunday

You can't pass a variable through your recordset opening statement. This statement accepts: your sql query, the connection string to your db, and your vb constants. (adReadOnly, adLockOptomisti c, etc)

Hope this helps,

Sincerely
Mark

I have the followig code:


Expand|Select|Wrap|Line Numbers
  1. set conn=Server.CreateObject("ADODB.Connection")
  2. conn.Provider=provider
  3. conn.Open dbpath
  4. set rs = Server.CreateObject("ADODB.recordset")
  5.  
  6. ...
  7.  
  8.  
  9. sql="SELECT department, job, start, [end] FROM Qry_AssignedWeek WHERE employee='" & employee & "' ORDER BY start"
  10. rs.open sql, conn, sunday
Where the Qry_AssignedWee k is the following query in Access2003:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS sunday DateTime;
  2. SELECT *
  3. FROM Qry_AssignedNotCancelled
  4. WHERE (((Qry_AssignedNotCancelled.start) Between [sunday] And [sunday]+7)) OR (((Qry_AssignedNotCancelled.end) Between [sunday] And [sunday]+7));

this code generates the ASP error:

Provider (0x80020005)
Type mismatch.
/schedule/ViewMyShifts.as p, line 70


The "sunday" variable is a string representing a date in format "dd/mm/yyyy"

I have tried to change the "sunday" in "rs.open sql, conn, sunday" with many different options:
int(sunday)
CDate(sunday)
#23/09/2007#

etc.

All returning various errors.

Please, can anyone guide me around this problem?
Sep 20 '07 #2
scf1984
17 New Member
Thanks! it's working:

Expand|Select|Wrap|Line Numbers
  1. sunday = CDate(sunday)
  2. sql="SELECT department, job, start, [end] FROM Qry_AssignedNotCancelled WHERE employee='"
  3. sql=sql & employee & "' " & "AND start BETWEEN #" & sunday & "# AND #" & sunday + 7 & "#"
  4.  
  5. rs.open sql, conn
(the varaiable "sunday" is given as a string "dd/mm/yyyy")

But I am still quite puzzled...
Is there a way to run an Access parameterized query from ASP?




You could try something like this... and scrap the msAccess query all together.

This would calculate when sunday is, and return all records which fall within the last sunday to the next sunday. In the case of this week, as an example, it would return all records from Sunday the 15th of september to Sunday the 22nd of september.

Expand|Select|Wrap|Line Numbers
  1. If WeekDay(Date()) = 1 Then 'it's sunday
  2.    dDateStart = Date() ' it's sunday today so we want to start from today's date
  3. Else
  4.    'Today is not sunday, so we need to find when sunday is. To do this, we'll find the integer value of the weekday, and count backwards to sunday.
  5.    'If today is friday weekDay will return 6 - sunday would be 5 days ago. So weekday*-1 = -6 + 1 = -5
  6.    iDateChange = (WeekDay(Date())*-1)+1
  7.    dDateStart = DateAdd(d,iDateChange,Date())
  8. End If
  9. 'Now we have our start date, we just need our end date, which is easy
  10.  
  11. dDateEnd = DateAdd(d,7,dDateStart)
  12.  
  13. sSQL="SELECT department, job, start, datefield FROM yourtable WHERE employee='" & employee & "' AND datefield BETWEEN " & dDateStart& " AND " & dDateEnd & " ORDER BY datefield"
  14. rs.open sql, conn, 3, 3
  15.  
  16.  
I also don't know why you have rs.open sql, conn, sunday

You can't pass a variable through your recordset opening statement. This statement accepts: your sql query, the connection string to your db, and your vb constants. (adReadOnly, adLockOptomisti c, etc)

Hope this helps,

Sincerely
Mark
Sep 20 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
17363
by: zlatko | last post by:
There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures (update, append) should be activated in order to transfer data to other tables. I tried to avoid any coding in VB, as I am not a professional, but I have found a statement in an article, that, unlike select queries, form's Input Property can't be...
1
3869
by: Michael DeLawter | last post by:
Using Access 2002. I have a chart in a report that is currently based on a query in which the user enters the start and end date for the chart to display. Both the start and end dates have been identified as parameters so that the date range can be found without causing an error. So far so good, report works as it should. Here's the problem. There are several reports that need to be printed at the end of month that use the same...
10
2993
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 2jan2003 etc later in other queries) -kenneth
7
2470
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 filled (with Now()) and the purchase date is entered manually. Meantime I became aware that instead of Now() I should use Date() for the date
4
4486
by: Tony | last post by:
Hey guys, I use Google Groups quite a bit as it is an enormous wealth of information, and now I need some help. I have created a query using parameters to capture a range of date, the date is also formatted. test: Format(,"mm/dd/yy") Between And
2
1594
by: Tony | last post by:
Hello everyone, Okay so here is my pickle! I have a field called ARCH_DATE within a table which has a date stored like "9/1/2004 23:50:00". What I would like to do is create a date range which prompts a user to ONLY to enter a format like "mm/dd/yy". I am not interested in the time component, I really am interested in the date. I have exhuasted my resources on this one, any help would be greatyl appreciated. Tony
0
3332
by: Zlatko Matić | last post by:
Hi everybody! Recently I was struggling with client/server issues in MS Access/PostgreSQL combination. Although Access is intuitive and easy to use desktop database solution, many problems appear when someone is trying to use it as front-end for real server database systems such as PostgreSQL or MySQL. One of these problems is regarding pass-through queries and parameters. I wanted to have all the code on client, while executing it on...
2
6522
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result. I then want to search through the records and select those with dates (as caluclated above) within a user defined range, and so I am using a parameter query. However, this query returns dates outside of the range and appears to have particular...
1
2116
by: ShadesOfGrey | last post by:
Hello again. Thanks to Gord, I have this cool query that puts my student attendance records in a calendar format in Access 2003. PARAMETERS Text ( 255 ), IEEEDouble; TRANSFORM First(IIf(=1,"S","I")) AS Type SELECT Year() AS , Month() AS , & " " & AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl. = Attendance_tbl. WHERE ((( & " " & )=) AND ((DatePart("yyyy",))=))
3
2947
by: Fred's | last post by:
Hi Folks, I have a report which the record source is a query name: Query3 and in my Query3 I have this parameter "between And ". Therefore, when I open my report, it will ask for a Start Date And an EndDate. Now I will like to have this date in the report footer of my acess report.
0
9993
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9841
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10858
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10949
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9662
agi2029
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7187
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5882
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6077
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4296
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.