How to use variables in MS Access 2003 Query for reports

Question

0.00/5 (No votes)

See more:

I had written following query in table adapter for report.

SQL

SELECT     RTGSEntries.ReceiptDate, RTGSEntries.ReceiptNo, RTGSEntries.DepositorName, RTGSEntries.DepositorContact, RTGSEntries.AccountNo, 
                      RTGSEntries.AccHolderName, Banks.BankName, Branches.BranchName, RTGSEntries.IFCS, RTGSEntries.Fees, RTGSEntries.Amount, 
                      @Start AS FromDate, @End AS ToDate
FROM         ((RTGSEntries INNER JOIN
                      Banks ON RTGSEntries.BankId = Banks.BankId) INNER JOIN
                      Branches ON RTGSEntries.BranchId = Branches.BranchId)
WHERE     (RTGSEntries.ReceiptDate BETWEEN @Start AND @End)

When I executing query (in query builder) i'm getting this error

VB

Error in SELECT clause: expression near '@'.
Error in WHERE clause near '@'.
Unable to parse query text.

pls help me.... thx in advance

Posted 30-Sep-12 5:35am

Ravi Sargam

Updated 30-Sep-12 5:44am

RaisKazi

v2

Add a Solution

Comments

Sandeep Mewara 30-Sep-12 14:22pm

Are @Start & @End declared and set before this query execution?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-09-30T10:31:00

Solution 1

To declare variables in MS Access 2003 query, you need to do it in this way:

SQL

PARAMETERS dStart DateTime, dEnd DateTime;
SELECT ... dStart AS DateFrom, dEnd AS DateTo
...
WHERE ... BETWEEN #dStart# AND #dEnd#

Posted 30-Sep-12 10:31am

Maciej Los

Comments

Ravi Sargam 17-Oct-12 12:20pm

Thx for reply ML
I written following query

PARAMETERS ReceptId NUMBER
SELECT RTGSEntries.RTGSEntryId, RTGSEntries.ReceiptNo, RTGSEntries.ReceiptDate, RTGSEntries.DepositorName, RTGSEntries.DepositorContact,
RTGSEntries.BankId, Banks.BankName, RTGSEntries.AccountNo, RTGSEntries.AccHolderName, RTGSEntries.BranchId, Branches.BranchName,
RTGSEntries.IFCS, RTGSEntries.Amount, RTGSEntries.Fees, RTGSEntries.Note1
FROM ((RTGSEntries INNER JOIN
Banks ON RTGSEntries.BankId = Banks.BankId) INNER JOIN
Branches ON RTGSEntries.BranchId = Branches.BranchId)
WHERE (RTGSEntries.RTGSEntryId = #ReceptId#)

but i'm getting this error

"Unable to parse query text."

pls give reply

Maciej Los 17-Oct-12 13:34pm

Try this:

PARAMETERS ReceptId NUMBER;
SELECT RE.RTGSEntryId, RE.ReceiptNo, RE.ReceiptDate, RE.DepositorName, RE.DepositorContact, RE.BankId, BA.BankName, R.AccountNo, RE.AccHolderName, RE.BranchId, BR.BranchName, RE.IFCS, RE.Amount, RE.Fees, RE.Note1
FROM RTGSEntries AS RE
LEFT JOIN Banks AS BA ON RE.BankId = BA.BankId
LEFT JOIN Branches AS BR ON RE.BranchId = BR.BranchId
WHERE RE.RTGSEntryId = ReceptId

A # is used only with datetime parameters/values. Use ALIASES.