select statement(Resolved)

hi

I would like to display the invoice between two dates . there are from the starting date to the end date. Please help me in the select statement.

Heres my select statement, but cant works:

rs_rct.Open "Select * from Receipt where DateValue(ReceiptDate) >= '" & DateValue(txtFrom) & "' <= '" & DateValue(txtTo) & "'", con, adOpenKeyset, adLockPessimistic

:blush:

Try

VB Code:

rs_rct.Open "Select * from Receipt where DateValue(ReceiptDate) BETWEEN '" & DateValue(txtFrom) & "' AND '" & DateValue(txtTo) & "'", con, adOpenKeyset, adLockPessimistic

i dont know its not works either. Any other suggestion ?? :blush:

hi,
If I have 2 different months, this statement cant works, it works will when the starting date and the end date are in the same months.
rs_rct.Open "Select * from Receipt where DateValue(ReceiptDate) BETWEEN '" & DateValue(txtFrom) & "' AND '" & DateValue(txtTo) & "'", con, adOpenKeyset, adLockPessimistic

I changed the 2 dates format to dd/mm/yy.

Private Sub DTPicker2_Change()
txtFrom.Text = DTPicker2.Value
txtFrom.Text = Format$(txtFrom.Text, "dd/mm/yyyy")
End Sub

Private Sub DTPicker3_Change()
txtTo.Text = DTPicker3.Value
txtTo.Text = Format$(txtTo.Text, "dd/mm/yyyy")
End Sub


How can I display the invoice between two dates. there are from the starting date to the end date. Please help.

If you have Access, you can use it's 'whatever its called' to visually build a query.
You can see the results straight away in a grid.
When you are happy that you are getting what you want, you can then copy and paste the query(that Acces writes for you), into your vb code.
There are a couple of small tweaks required to the code, which I am sure someone here can then fix after you post it here.

i cant get the collect query to display the invoice between two dates(receiptDate). there are from the starting date to the end date. Please help.

I am no expert on using dates in queries, because I am frightened of them.
There can be complications between US and other countries due to the MM DD yy and the dd mm yy that non-us people use.
That may be contributing to your problem.
If you used mmm instead of mm would that help.
(Mind you I am no expert with dates).
I avoid problems by converting all my dates to a long, and storing them that way. Any queries I build just compare longs with each other, and 'they' don't even suspect that they represent dates.

the problem is I changed the receiptDate to dd/mm/yyyy.

this cant work when the month is different:
rs_rct.Open "Select * from Receipt where DateValue(ReceiptDate) BETWEEN '" & DateValue(txtFrom) & "' AND '" & DateValue(txtTo) & "'", con, adOpenKeyset, adLockPessimistic

please help

Couple of suggestions

Convert the Dates to Longs prior to comparing them.
Also I would declare a couple of variables(longs), and place the contents of the textboxes into them. Then use the Variables in your Select

VB Code:

Dim lngFrom as Long
 Dim lngTo as Long
  lngFrom = cLng(cDate(txtFrom.text)))
  lngTo =cLng(cDate(txtFrom.text)))
  Debug.Print lngFrom & "   " & lngTo 'Look at these later to see if they look ok.
 
rs_rct.Open "Select * from Receipt where cLng(ReceiptDate) BETWEEN '" & lngFrom & "' AND '" & lngTo & "'", con, adOpenKeyset, adLockPessimistic

I just made all that up, so don't bet the family home, that it will work.

No doubt this in the SQL will cause problems

VB Code:

where cLng(ReceiptDate) BETWEEN

But no doubt some SQL guru, can tell us how to 'cast' it into a Long equivalent.
As I said, I'm making it up as I go along.

no. It cant work

Not sure if Cast will work with Jet.
And not sure if Long is the Type to use with Jet
Give it a try

VB Code:

rs_rct.Open "Select * from Receipt where Cast(ReceiptDate as Long) BETWEEN '" & lngFrom & "' AND '" & lngTo & "'", con, adOpenKeyset, adLockPessimistic

if problems with

VB Code:

Cast(ReceiptDate as Long)

try Long Integer and anything else that looks possible

it has run time error - method "open" of object_Recordset' failed.

when I put 04/04/2005 in the txtFrom , the IngFrom ="38446"

it cant works.

One day we may find there is a Cast or Convert that would do what I want.
In the mean time go back to your code
NB I've used mmm instead of mm in these

VB Code:

Private Sub DTPicker2_Change()
txtFrom.Text = DTPicker2.Value
txtFrom.Text = Format$(txtFrom.Text, "dd/mmm/yyyy")  '<== mmm
End Sub
 
Private Sub DTPicker3_Change()
txtTo.Text = DTPicker3.Value
txtTo.Text = Format$(txtTo.Text, "dd/mmm/yyyy")   '<== mmm
End Sub
 
'It is cleaner to do this prior to your sql statement
 Dim dtFrom as date
 Dim dtTo as date
  dtFrom = cDate(txtFrom.text)
  dtTo = cDate(txtFrom.text)
  Debug.Print dtFrom & "   " & dtTo 'Look at these later to see if they look ok.
 
rs_rct.Open "Select * from Receipt where ReceiptDate BETWEEN '" & dtFrom & "' AND '" & dtTo & "'", con, adOpenKeyset, adLockPessimistic

You may have to use your DateValue or similar (but what do I know ?)

PS As I mentioned, I have evolved an approach that avoids using Date arguments conpletely, so I am just guessing here.
If anyone who does use Dates in their SQL statements wishes
to chip in, I will not be offended.

it cant work because the ReceiptDate in Access is is in "dd/mm/yyyy" format.

This may be benefiicial now, or one day in the future -

SQL statements must use mm/dd/yyyy date format

Quote:

---

t cant work because the ReceiptDate in Access is is in "dd/mm/yyyy" format.

---

What format is that field in Access ?
Is it a string field ?
Is it a Date/Time field ?

If it is the latter, then don't be fooled by what Access shows you.
It is like a 'used car salesman', as it shows you what it thinks you would like to see.
How it stores it internally is different, and I have read that it is in US format (It is probably not mm dd yyyy, but it does think us whilst it deals with it.)
It attempts to show you what you might expect by peeping at your pc's date/time settings.

the receiptDate is Text format (dd/mm/yyyy) in Access

Is there pre-existing production data ?
If not, can you change the way you store the date to dd mmm yyyy ?
That way you avoid any US vs non-US issues.

PS dd/mmm/yyyy if you prefer

even i changed it in dd/mmm/yyyy, still cant get the correct date

I would do one of three things -
Create a small sample that demonstrates the problem, and post the project and the MDB as an attachment, to here.
OR
Wait a few hours until the normal users wake up, and they will step in with their real SQL Date query experience (since mine is non-existent, because I have avoided these issues with my Long storage trick)
OR
Re-type your question, and post it into the DB forum, where you will get a near instant answer.
(I say re-type it, as all of the above would be too much to ask them to read)

If you do decide to post it here, I will have a look at it, and try to see what is happening.

even i changed it to dd/mmm/yyyy format. it still compare only the dd and not the month. How can i solved this out?

How to write the query to display the invoice between two dates(receiptDate). there are from the starting date to the end date. the format of the receiptDate is dd/mm/yyyy in access. the starting date and end date also in the format dd/mm/yyyy Please help.

i still cant work out this. Someone please help !!

If it was something that I used I could do it in my head.
Since it is something I don't use (Date fields in queries), the only way I could work it out, is by running it and checking what I was getting (debugging).

Can you attach a small example ?

Here is some code that should help you.

VB Code:

Option Explicit
 
Private Sub Form_Load()
Dim d1 As Date, d2 As Date, x As Date
  d1 = Format("05/02/2005", "dd/mm/yyyy") ' convert to "2/5/2005"
 ' MsgBox Format(d1, "d mmm yyyy") ' prints "5 Feb 2005"
' d1 = CDate("02/05/2005")
  d2 = CDate("12/02/2005")
  x = CDate("05/01/2005")
' MsgBox Format(x, "d mmm yyyy") ' prints "1 May 2005"
 If x >= d1 And x <= d2 Then Beep ' May > Feb AND May < Dec
End Sub

RobCrombie:

pls find that the attached file is a sample of it.

Hi,
Thanks for that.
I have to cook lunch/eat lunch/and get to squash in 90 mins.
When I get back tonight I will definiitely look into it (in say 5 hours).

I built a quick query in Access -

VB Code:

SELECT Receipt.ReceivedDate
FROM Receipt
WHERE (((Receipt.ReceivedDate)>"01/04/05" And (Receipt.ReceivedDate)<"31/055/055"));

(OOPs it might just be doing string comparisons. I did it in approx 60 secs, so I could do better when I have more time)
And that runs ok in Access (returns the two records in a grid)
Don't have time now but will look further tonight (in Aust)

One thought for the future though.
You do not want to live with dates being stored in a Text field as dd/mm/yyyy
That will haunt you for the rest of the DB's life.

Choices include -
use Date/Time fied instead
OR
use dd/mmm/yyyy (14/May.2005)
OR
Leave it as you have it now, BUT have a companion field, that user never sees.
That field can be Date/Time or Access's Long equivalent
When ever you store a date in your text field, you also store it into the companion field.
Then all of your future SQL queries can be against the companion field.

I will look further later

Hi,
GOOD NEWS

This worked -

VB Code:

.Open "Select * from Receipt where cdate(ReceivedDate) Between '" & DateValue(txtPeriodFr) & "' AND '" & DateValue(txtPeriodTo) & "'", con, adOpenKeyset, adLockPessimistic

Hi,
Thanks for your help.
I try to add more receivedDate into the database and testing it out. It having the same problems. When I having 2 records in April and 2 records in May. It only disply 1 records in April and 1 in May even 4 records are between the search date.

I would have gone this route:

VB Code:

.Open "Select * from Receipt where cdate(ReceivedDate) Between '" & cdate(txtPeriodFr) & "' AND '" & cDate(txtPeriodTo) & "'", con, adOpenKeyset, adLockPessimistic

Actually CDate is better then DateValue because DateValue returns a Variant and not a Date Type. ;)

I am surprised that no one mentioned that since this is in Access that you need to wrap your dates around the # chars.

VB Code:

.Open "SELECT * FROM Receipt WHERE ReceivedDate >= #" & CDate(ReceivedDate) & "# AND ReceivedDate < #" & DateAdd("d", 1, CDate(txtPeriodTo)) & "#;", con, adOpenKeyset, adLockPessimistic

Hi,

It still cant get it works. Please refer to the sample which I attached. :rolleyes:

This works, and returns the 4 records

VB Code:

Dim sSQL As String
Dim lngFrom As Long
Dim lngTo As Long
 
  lngFrom = CLng(CDate(txtPeriodFr.Text))
  lngTo = CLng(CDate(txtPeriodTo.Text))    
  
With rs_rct
 
sSQL = "Select * from Receipt where clng(cdate(ReceivedDate)) Between '" & lngFrom & "' AND '" & lngTo & "'"
.Open sSQL, con, adOpenKeyset, adLockPessimistic

cant works.

for examples:
03/04/2005 to 25/04/2005 the result is :04/04/2005 only

03/04/2005 to 31/05/2005 the result is : 24/05/2005 and 04/04/2005

still cant get the correct results. :o

I dont have winrar but what is the date format in your db? dd/mm/yyyy or mm/dd/yyyy?

the format in db is dd/mm/yyyy
the file is too big if i use zip

So many things can beat you up in date selection from VB to any DATABASE that taking a few small steps first would be very helpful...

First take RobDog's suggestion but hardwire some dates - and put the query in a string first (always better to do that).

Code:

---

dim strSQL as String
strSQL = "SELECT * FROM Receipt WHERE ReceivedDate >= #2004-01-01# AND ReceivedDate < #2004-12-31#;"
Debug.Print strSQL
.Open strSQL, con, adOpenKeyset, adLockPessimistic

---

See if that returns something.

If it doesn't remove the WHERE clause completely - so that you can rule out some other connection or DB error itself.

Code:

---

dim strSQL as String
strSQL = "SELECT * FROM Receipt;"
Debug.Print strSQL
.Open strSQL, con, adOpenKeyset, adLockPessimistic

---

What you are trying to do here is determine a DATE FORMAT that will always work - from VB to DB - keep in mind that dates are not stored in a format like dd/mm/yyyy - that's simply a display presentation.

I chose yyyy-mm-dd to make it less ambiguous. I've heard others say to use DD MMM YYYY (like 01 JAN 2004) - which is very distinct.