Solved

Jet/Access Query with Parameters on VS2010

Posted on 2011-09-08
2
366 Views
Last Modified: 2012-06-27
In SQL Server, I can create a dataset with parameters by using the prefix @

e.g.
SELECT *
FROM
  (Table1 As T LEFT JOIN
  Table2 As C ON T.CodeID=C.CodeID) LEFT JOIN
  Table3 As U ON T.UserID=U.UserID
WHERE
  T.TimeIn >= @Date
  AND T.TimeIn < DateAdd("n",1,@Date)

Open in new window


I can then access the parameters using the parameter name "Date".

Now I need to make this work with Jet/Access DB as backend. It seems that it does not recognize @ as a prefix for a parameter since it give an error "Error in WHERE clause near '@'". I googled a little and found that jet uses "?" to specify a parameter but the problem is replacing both @Date from the query with ? will create 2 parameters.

What is the 'correct' way to create a parameterized query in a dataset for Jet?
0
Comment
Question by:Thomasian
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36507853
I believe it goes like this:


Parameters [Date] Date/Time;
SELECT *
FROM
  (Table1 As T LEFT JOIN
  Table2 As C ON T.CodeID=C.CodeID) LEFT JOIN
  Table3 As U ON T.UserID=U.UserID
WHERE
  T.TimeIn >= [Date]
  AND T.TimeIn < DateAdd("n",1,[Date])
0
 
LVL 22

Author Closing Comment

by:Thomasian
ID: 36507934
The 1st line ("Parameters...") is giving an error.

Enclosing the parameter names with []s works, but I have to manually add the parameters on the dataset.

Thanks
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 24
Code works but breaks when I add one section 4 20
IIF in access query 19 26
Use after update to change dates 2 16
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question