Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Whats wrong with this query


hi i tried following query and its working but with one problem,
i passed Date parameter from 1st to 5th but result is from beginning date to current date.
What am i doing wrong.?
 
rs.Open "Select * FROM BD where BDate between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and To = 'ABC' or To = 'XYZ' and IDTNo = 0 and Status_Rec = 2 Order by BNo", con, adOpenKeyset, adLockOptimistic

Open in new window

0
crystal_Tech
Asked:
crystal_Tech
  • 3
  • 3
1 Solution
 
mlmccCommented:
Try it this way.

Just like multiplication and addition, AND and OR have an order
AND is first so I think you need to put the OR in ( )
As written it is evaluated as

 
rs.Open "Select * FROM BD where 
(BDate between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and To = 'ABC')
 or
( To = 'XYZ' and IDTNo = 0 and Status_Rec = 2) Order by BNo", con, adOpenKeyset, adLockOptimistic

Open in new window


I think you really want

 
rs.Open "Select * FROM 
(BD where BDate between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and
( To = 'ABC' or To = 'XYZ')
 and IDTNo = 0 and Status_Rec = 2 Order by BNo", con, adOpenKeyset, adLockOptimistic

Open in new window


mlmcc
0
 
Bhavesh ShahLead AnalysistCommented:
Hi

if you using access then cdate will work.
if you using sql server then you need to use convert.

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
crystal_TechAuthor Commented:
< Micheal >
your first query is correct, and i tried it but output is nothing
if rs.recordcount > 0 then
 msgbox "Record/s Not Found"
end if

Recordcount is zero.
i tried with different date parameter
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
crystal_TechAuthor Commented:
i tried following query but result is nothing
rs.Open "Select * FROM BD where
(BDate between CDATE('" & DTPickerFrom.Value & "') and CDATE('" & DTPickerTo.Value & "') and To = 'ABC')
 or
(To = 'XYZ' and IDTNo = 0 and Status_Rec = 2) Order by BNo", con, adOpenKeyset, adLockOptimistic
0
 
mlmccCommented:
Can you tryi it in the database?

Try using just part of it in the application to see that there is data for each piece or if there is one that has no data.

To = 'ABC'
IDTNo = 0
To = 'XYZ'
Status_Rec = 2
BDate >= CDATE('" & DTPickerFrom.Value & "')"
BDate <= CDATE('" & DTPickerTo.Value & "')"

Once they all work you can try putting them together.  It may be there is no data for some part of it.

mlmcc
0
 
crystal_TechAuthor Commented:
strange..
i tried using just single single part (Parameter) and there is data, then
i tried putting them together and its working like nothing was happened..!!
i compared previous one with new one and its same to same....

i got my answer and point is yours. but just i want to know
why it wasn't working before.?

0
 
mlmccCommented:
No idea unless it was using data from cache rather than a real refresh.

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now