Printable View
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:
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:
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:
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:
if problems with
try Long Integer and anything else that looks possibleVB Code:
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:
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
What format is that field in Access ?Quote:
t cant work because the ReceiptDate in Access is is in "dd/mm/yyyy" format.
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:
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 -
(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)VB Code:
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:
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:
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:
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:
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).
See if that returns something.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
If it doesn't remove the WHERE clause completely - so that you can rule out some other connection or DB error itself.
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.Code:dim strSQL as String
strSQL = "SELECT * FROM Receipt;"
Debug.Print strSQL
.Open strSQL, con, adOpenKeyset, adLockPessimistic
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.
If the format is not going to change, then text dates may be the best.
When I use the date ranges you last mentioned, I get correct results..
I have attached screen shots.
Also a shot of the DB contents for others to see.
What country are you in ? (relates to your Windows Date settings)
It really is a 'lucky dip' as to what VB and Access and Windows decide to do when they are interpreting your DD/MM/YYYY
Try editing your dates in the DB from 04/04/2005 to 04/Apr/2005
Do that with the other dates, and run the pgm again.
If it then works, then you know you should not trust the system's guess at what ../04/.... means. You should use ../Apr/.... instead.
I am attaching the Original and the one I tweaked.
I have also used .MoveFirst to ensure that the loop through records is picking up all records. (On my runs, it made no difference, but it is a good habit).
What exactly do you mean by this?Quote:
Originally Posted by RobCrombie
Dates cannot be edited and stored differently in a DB - they are stored in a quadword format that has nothing to do with the display presentation you see.
Most of the confusion I've seen on the forum about dates is directly related to this point. People think they are seeing dates a certain way and then construct weak queries around those assumptions.
I agree with you completely that using an APR instead of 04 format for month is clearly less ambiguous and will probably work for this person...
It's all about coming up with a date formatting concept that works and then using it consistently.
OOPS
You are correct.
Normally I wouldn't be tweaking the DB directly like that.
Probably what I should have said is any dates that are being interpreted by code for storage should use Apr instead of 04, so that the correct date is stored. I did mention earlier that Access stores it internally in it's own way, I just forgot to tell myself that, when I quickly said 'try editing your dates'
i managed to get it done... Guys, Thanks for all your help. :wave:
RobCrombie,
i have no idea why your file "orig and ItsBeenRobbed cant work on my PC.
When you say 'cant work on my PC' what exactly happens (or not) ?
The pgm runs I assume ?
Just when you select the date range you don't see all the records you expect ?
I Looked at the solution you last posted, and noticed that you hadn't really solved the International Date settings problem, You had just made individual variations between using DD/MM/YYYY and MM/DD/YYYY in different places in your pgm.
That was not the way to solve this problem, it is just 'band aiding' it.
I have just run your pgm now, and I am attaching the results.
You haven't said which country you are in, but because we are getting mixed results, I can bet it is not Australia.
So you should either move to Australia, or come up with a solution that is 'fool proof'.
I am also attaching the contents of your DB. If you study them you will see that my run of your pgm returns the three records that the system is forced into thinking are Apr or May.
The other two records can be interpreted as being Dec or Nov, and I guess that is what is happening, in an Australian run of your pgm.
You haven't shown the code that was writing the data to the DB. I am guessing that it is not 'date proof'.
As I said in my first post, I am frightened of Dates, and have invented an alternative approach which uses Longs', which is 'fool proof'
I can tell, that you wil not be open to that approach, so instead you should post all of your code, and really l i s t e n to the other experts.
hi,
i m from Malaysia. i get your point. the only thing is i need to change the format before it display on the grid which i havent done it.
when i run your pgm, i put 03/04/2005 to 30/04/2005, i get the result: 04/04/2005 only
for 10/04/2005 to 30/04/2005 the result is 24/05/2005
the result seems totally out of control.
Like I said in my post (#40) - formatting of dates for selection is something that has to be worked out and consistently applied. You will go around in circles if you do not have a firm foundation to work with.Quote:
Originally Posted by vivian2u
You must come up with a format (yyyy-mm-dd or dd mmm yyyy or whatever works with your regional settings and version of DB) - determine that it does work and then consistently apply it in your program. This R&D portion of development cannot be skipped over.
Once you have a format that works, wrap the logic for "formatting" that into a nice FUNCTION buried in a module somewhere - and use that function everywhere for date translation.
We like to have a function that can translate from "stored" format, "selection" format and "display" format - all with the same function - based on a "output type" parameter.
The 'system' is playing 'russian roulette' with it's guesses.
You have to make it absolutely 'Fool proof', so it can no longer be allowed to guess.
You haven't jumped to take up my 'Longs approach'.
Thus you should let the experts in handling Dates, have a look.
I'll attach your project(in zip format), so they can more easily open it.
You should also post the code, that will be used to store the dates into the DB, as it is vital that it be 'Date proof', as well.