How to quering access db with multiply conditions

Question

1.00/5 (1 vote)

See more:

My codes are flowing, i want to select two types words 'Checked In' and 'Checked Out' from table 'notes', by filtering with date, but it's not working, please help.

SQL

SELECT (LedgersName) as [Ledgers Name], (TransDate) as [Date], (Total) as [Total], (TotalPaid) as [Total Paid], (Balence) as [Balence], (Notes) as [Notes], (LedgersID) as [Ledgers ID], (TransID) as [Trans ID] from MasterTrans where Notes='Checked In' or Notes='Checked Out' and TransDate between #" & dtpForm.Value.Date & "# And #" & dtpTo.Value.Date & "#

[edit]SHOUTING removed - OriginalGriff[/edit]

Posted 14-Jul-14 8:02am

Member 10927831

Updated 14-Jul-14 8:18am

OriginalGriff

v2

Add a Solution

Comments

[no name] 14-Jul-14 14:12pm

Screaming at us does not help. And, "not working" tells us nothing about your problem.

OriginalGriff 14-Jul-14 14:18pm

DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.

Maciej Los 14-Jul-14 14:52pm

"Not working" isn't informative!

nilesh sawardekar 14-Jul-14 15:20pm

you want both 'checked in' and 'checked out', u mean to say?
what output or error your getting.

nilesh sawardekar 16-Jul-14 2:27am

Maciej Los gives you good example.. beside this sometime date format also creates trouble for filtering data datewise.. so use "CDate( #" & dtFrom.ToString("MM\/dd\/yyyy") & "#)"
kind of formats

2 solutions

Solution 1

Your access query should like this

SQL

SELECT Db_Manager.DbName, Db_Manager.Description
FROM Db_Manager where createddate between
#13/07/2014# and #15/07/2014#

and in C#

SQL

SELECT Db_Manager.DbName, Db_Manager.Description
FROM Db_Manager where createddate between
'#' + " & dtpForm.Value.Date & " + '#' and '#' + " & dtpTo.Value.Date & + " + '#'

Posted 14-Jul-14 8:30am

suresh shewale

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 · Accepted Answer · 2014-07-14T09:44:00

Solution 2

Please, read my comment to the question.

On the first look your query looks OK, but.. i suggest to:
1) remove ( and ) brackets around field names,
2) add parameters[^],
3) check value passed to query through DateTimePicker control.

SQL

PARAMETERS [dateFrom] DATE, [dateTo] DATE;
SELECT LedgersName as [Ledgers Name], TransDate as [Date], Total as [Total], TotalPaid as [Total Paid], Balence as [Balence], Notes as [Notes], LedgersID as [Ledgers ID], TransID as [Trans ID]
FROM MasterTrans
WHERE (Notes='Checked In' OR Notes='Checked Out') AND TransDate Between [dateFrom] And [dateTo];

Then call this query using OleDbCommand[^] together with OleDbParameters.AddWithValue[^] method.

Be aware, don't use List of reserved words in Access 2002 and in later versions of Access[^]

Posted 14-Jul-14 9:44am

Maciej Los

Updated 15-Jul-14 1:44am

v2

Comments

Member 10927831 15-Jul-14 7:21am

YOUR SOLUTION HELP ME BUT IT'S NOT WORKING TOO,,, YOUR ARE RIGHT ABOUT THE 'AND' IN 'BETWEEN' STATEMENT, BUT CANT FIND OUT THE SOLUTION, CODES ARE GIVING OUTPUT UP TO NOTES='Check In' AFTERWORDS IT'S NOT WORKING. NEED TO FIND OUT FOR THIS
OR Notes='Checked Out' AND TransDate Between [dateFrom] And [dateTo];

AND THANX, Maciej Los

Maciej Los 15-Jul-14 7:46am

See updated answer. Please add brackets: (... OR ...) AND ... It shuold resolve your problem.

Tino Fourie 15-Jul-14 13:09pm

Before you code anything it is best to have your keyboard replaced since it is only capable of typing in UPPERCASE. Also having your keyboard replaced with one that is capable of producing "lowercase" and "UPPERCASE" characters you will improve your chances of getting help here.

Most of us here rely on contributions from members here and most of us asking questions are also quite keen to help out where we can. However, I will ignore any post that is SCREAMING at me or when someone states "I don't care" because that points to one thing and that is you expect someone to give you the code that will make your problem go away without making any effort on your side to try and understand the code to avoid bothering us with the same question days later.

Be considered and respectful, we do not get paid to give you your answers unless you start paying. If you are paying I guess you can address me in any form you like as long as you pay upfront.