Full Version: selection criteria between two dates..
UtterAccess Forums > Microsoft® Access > Access Forms
vbwanneb
I have a form where I have a number of criteries I can choose between.
The criteria I choose then gives the filtering on the report I open.
one of those options are a date selection where I choose two dates..
the variables are datatypes Date the report call looks like this...what Is wrong?..
oCmd.OpenReport DocListe, acViewPreview, , "[DateCreated] >= #" & ChoiceDate1 & "# And [DateCreated] <= #" & ChoiceDate2 & "# "
Oget a weird result..but I do get records...when I enter the dates directly on the report, it works just fine..
ChoiceDate1 IS smaller than ChoiceDate2 ie. DateCreated >= 01-10-2004 And DateCreated <= 20-10-2004
Alan_G
Hi
Try using Between - something along the lines of
DoCmd.OpenReport DocListe, acViewPreview, , "[DateCreated] Between #" & ChoiceDate1 & "# And #" & ChoiceDate2 & "# "
Not tried it but something like should work
HTH
Alan
vbwanneb
Hi
It gives me the same result..what I think i wierd is that
I have some records dates 01-01-2004 too...
and using the above mentioned dates I get records starting from 17-09-2004 and until 20-10-2004 just dont get it.
Alan_G
Are ChoiceDate1 and ChoiceDate2 text boxes on your form? If so try changing it to
oCmd.OpenReport DocListe, acViewPreview, , "[DateCreated] Between #" & Me.ChoiceDate1 & "# And #" & Me.ChoiceDate2 & "# "
Again, not tried it but it should be something like that.
If it doesn't work, can you post a stripped down version of your DB and I'll take a look if you want
HTH
Alan
LittleViews
You might be evaluating time with the dates, but don't realize you are doing it. It's a real common problem.
Try formatting your two dates to be absolute dates.
vbwanneb
Hi Karen

you mean something like this?
DatoConvert = Format$(Format$(ChoiceDate1, "##/##/##"), "dd / mm / yy")
DatoConvert = Format$(Format$(ChoiceDate2, "##/##/##"), "dd / mm / yy")

?
Odid this
DateConvert1 = Format$(Format$(Me.FromDate, "##/##/##"), "dd / mm / yy")
DateConvert2 = Format$(Format$(Me.ToDate, "##/##/##"), "dd / mm / yy")
ChoiceDate1 = DateConvert1
ChoiceDate2 = DateConvert2

But I get e typemismatch error on DateConvert2
I declared DateConvert like this
dim DateConvert1, DateConvert2 as Date
so they should be matching datatypes




Teddy
Edited by: vbwanneb on Thu Oct 21 3:17:14 EDT 2004.
LittleViews
Hi, Teddy.
ates can be touchy. The rule is to start with simple and work towards complex, so let's see what we can do about simplifying code your wrote above.
All you really need is: format(myStartDate, "dd/mm/yyyy")
Make sure your formatting is *exactly* the same as what is in your table, which means you should use yyyy. Best, you can stick that right in the query without going through an intermediate variable.
But if you want to use an intermediate variable, it can be a string if you are sure you are passing the correct information.
Now then, how you can check what you are passing to what, create fields on your form called myTest1 and myTest2. If when you pass information to it you come up with something like 10/12/2004 10:05:01 you know that you are also passing a timestamp.
Also, in your original db, make sure you are not collecting timestamps.
Note that now() will carry a timestamp, but date() won't.
There are issues with ODBC date and ODBC time, but let's hope we don't need to discuss them.
vbwanneb
Im using an Ms Calender object to select my dates
thing that puzzles me is when I choose the date 01-10-2004 the format gives me 3/82/61
Im not getting that at all..
LittleViews
When you select from the calendar you are getting March 82, 1961 ? ? ?Talk about a Pisces with a grudge!
It looks like you might need to change your calendar application. I'm using a nice little freeware one that I really love.
But back to your problem, remove the calendar and select the from / to dates by typing them in and see ifyou get the right data. If yes, trash your calendar program and get a different one.
Do the minimum amount of formatting for now.
vbwanneb
I tried removing the date thingie and set it manualy - same result.
checked my db and found that the DateCreated value is set using the following:
MyDate = Date
Me.DateCreated = MyDate
and is a short date...so is the unbound field I enter the date in on the current problem form.
and it doenst seem to matter weather I pick it from the calender obj or type it I even tried grabbing the value from the calender obj in the openreport statement,,,no go
LittleViews
At what point does the date go bad?
But a meTest field on your form and instead of going myDate = Date, go me.myTest = Date. If that is good, then . . .
Take the "me" away from the field in the table that will ultimately use the date, so you have dateCreated = date and see what you get.
if you still have a problem, rename dateCreated to something else.
You can also try me.myTest = format(now(), "mm/dd/yyyy") and see what you get.
The "binding" doesn't matter until you need to put the date into the table. If you can produce a good date, but when it eventually goes into the table, it is bad, then we'll investigate at that point.
vbwanneb
Ok... Im gonna try that right away - sorry for not replying befor now, busy weekend, inlaw turned 50.
nyway...I tried this too
DoCmd.OpenReport DocListe, acViewPreview, , "DateCreated >= #01-10-2004# And DateCreated <= #14-10-2004#"
didnt work either..
Im having a look at what you suggested right now, and will let you now how it turned out in a few hours
vbwanneb
ok...I think I found the problem.

my date format in the table is dd/mm/yyyy
but when I open the report it looks like the format is expected to be mm/dd/yyyy
The reason I think so is:
this is what I tried at first
DoCmd.OpenReport DocListe, acViewPreview, , "DateCreated >= #01-10-2004# And DateCreated <= #14-10-2004#"
but changing it to
DoCmd.OpenReport DocListe, acViewPreview, , "DateCreated >= #10-01-2004# And DateCreated <= #10-14-2004#"

and it worked...
but that still leaves me with the formatting problem, but at least the date thing seems to work..

in the table my date format is the same ddmmyyyy (DateCreated)
and I put in a msgbox to show me the values of ChoiceDate1 and ChoiceDate2 which are the dates I choose to sort by
and they come out fine too..dd/mm/yyyy

Edited by: vbwanneb on Mon Oct 25 3:19:43 EDT 2004.
LittleViews
Great - finding the problem. O happy days! Now then, you need to create routines to pass the date using the format it expects (which you can do when your user clicks "go") OR see if you can change the format in the table so it is more consistant with how you normally express dates.
vbwanneb
The status so far.

Otried to debug what I had, and found the the correct values DO get passed to the docmd.OpenReport.

?
DoCmd.OpenReport DocListe, acViewPreview, , "DateCreated >= #" & ChoiceDate1 & "# And DateCreated <= #" & ChoiceDate2 & "# "

is
DoCmd.OpenReport DocListe, acViewPreview, , "DateCreated >= 01-10-2004"# And DateCreated <= #14-10-2004# "

so... what are we looking at here... an error in my db properties? on the table the DateCreated field is a short data data type

and... if I change ChoiceDate2 to
5-10-2004 in the variable I dont get any records at all, but I know 5 is present...
entering that date in the docmd as a fixed date.. but in a reversed form (mm/dd/yyyy) works fine...this is where I (if I hadnt removed it all) would pull hard on my hair
Edited by: vbwanneb on Mon Oct 25 7:44:03 EDT 2004.
LittleViews
Well, I see a syntax error.
Create a variable called "myCriteria" and work with it instead of the typed out doCmd statement.
Before you try pulling a record, put a field called myTestField on your form and fill it in with whatever value you get in myCriteria, as follows:
me.myTestField = myCriteria ' of course, myCriteria holds a value as shown below
myCriteria is set up like the following:
myCriteria = "dateCreated >= #" & beginDate & "# and dateEnded <= #" & endDate "# "
Once you know it looks the way it should, place myCriteria in your doCmd
DoCmd.OpenReport DocListe, acViewPreview, , myCriteria
vbwanneb
Hi Karen..

Oadded at myTest field on the form and did the following.
myCriteria = "DateCreated >= #" & ChoiceDate1 & "# and DateCreated <= #" & ChoiceDate2 & "# "
Me.myTest = myCriteria

then I chose ChoiceDate1 and 2
ChoiceDate1 = 01-10-2004
ChoiceDate2 = 11-10-2004

the value of myTest is:
DateCreated >= #01-10-2004# and DateCreated <= #11-10-2004#

In my table DateCreated contains the following dates
DateCreated
01-01-2004,01-01-2004,17-09-2004,01-01-2004,01-01-2004,01-01-2004,01-01-2004,01-01-2004
01-01-2004,20-09-2004,01-10-2004,01-10-2004,01-10-2004,01-10-2004,01-01-2004,01-01-2004
01-01-2004,12-10-2004

The record isnt sorted another field is the primary key and the table is sorted according to that.
anyway... I should get 4 matching records from this..right??
but I get 8...and the two first are from the month before

when I create a simple sql with those criteria..like this:
SELECT TBL_Order_Main_Page.DateCreated
FROM TBL_Order_Main_Page
WHERE (((TBL_Order_Main_Page.DateCreated)>=#10/1/2004# And (TBL_Order_Main_Page.DateCreated)<=#10/11/2004#));
I actualy type >=01-10-2004 and <= 11-10-2004 but it gets autoconverted??

Anyway..I get four records
Edited by: vbwanneb on Tue Oct 26 3:57:34 EDT 2004.
LittleViews
In Oracle, there is a way to convert the dates in a table on the fly to match the dates input so everything matches, but I do not know how to do this in Access.
If you declare a variable as a date, the default is m/d/yyyy, so no matter how you input it without formatting, that is the results you get. If, however, you do something like:
Private Sub xxx_Click()
Dim trialdate As Date
trialdate = test1 ' date was put into test1 as 4/20/2004
test2 = Format(trialdate, "dd-mm-yyyy") ' it is displayed as 20-04-2004
End Sub
You will get the transformation you need.
In your query, are you passing the date as a formatted date? If all you are doing is typing the date in as 20-04-2004, Access will override your desire.
If, however, the use of formatting doesn't help, we'll move on to Plan C . . . :-)
vbwanneb
Lucky the alphabet is long... Ill give it a go, but it will have to be later to night.
thanks again
vbwanneb
I think it worked!! at least...im getting a result that seems right..
but thing might have fussed my mind a bit, im even dreaming about dates..not the going out kind...
so sad...
nyway this is what I ended up with
DateConvert1 = Format(Me.FraDato, "mm/dd/yyyy")
DateConvert2 = Format(Me.TilDato, "mm/dd/yyyy")
ChoiceDate1 = DateConvert1
ChoiceDate2 = DateConvert2
myCriteria = "DateCreated between #" & ChoiceDate1 & "# and #" & ChoiceDate2 & "# "
DoCmd.OpenReport DocListe, acViewPreview, , myCriteria
Oowe you a hug... thanks for all your help...and not to mention - patience
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.