Why this code doen't work, any ideas?

[lulo]

I'm looking for date that fall between two dates but no luck. I get a run time error.

Dim dtStart As Date, dtEnd As Date, numDay As Double

dtStart = DateSerial(Year(Now), Month(Now), 1)
dtEnd = DateSerial(Year(Now), Month(Now) + 1, 1)

numDay = dtEnd - dtStart
dtEnd = DateSerial(Year(Now), Month(Now), numDay)


gRDB_RS.Filter = "RetrainedDate Between #" & dtStart & "# AND #" & dtEnd & "#"

Also
This one works
gRDB_RS.Filter = "RetrainedDate Like " & "8/4/02"

but this one doesn't
gRDB_RS.Filter = "RetrainedDate Like " & "8/*/02"

Any suggestion friends?

Thank you very much for your help.
Lulo

 

[JohnYingling]

Try
numDay = DateDiff("d",dtStart, dtEnd)

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[lulo]

Thank you John.
But still won't work

 

[patrician]

where exactly are you getting the run-time error from vb or from the datbase you accessing. If its the datbase ensure dates are being passed properly for your locale.

 

[patrician]

where exactly are you getting the run-time error from vb or from the database you are accessing. If its the database ensure dates are being passed properly for your locale.

 

[lulo]

This is a VB Error

Run-Time error '3001'
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".

I also tried this, but no luck.
Dim dtStart As Date, dtEnd As Date, numDay As Double

dtStart = DateSerial(Year(Now), Month(Now), 1)
dtEnd = DateSerial(Year(Now), Month(Now) + 1, 1)

numDay = DateDiff("d", dtStart, dtEnd)
dtEnd = DateSerial(Year(Now), Month(Now), numDay)

If IsDate(dtStart) And IsDate(dtEnd) Then
gRDB_RS.Filter = "RetrainedDate Between #" & dtStart & "# And #" & dtEnd & "#"

End If

 

[patrician]

see msdn for answer:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q248076&

 

[lulo]

I tried MSDN but it seems to me that I just found another glitch on it.
I am not using any execute or any of the examples that caused the error on their examples.
My program sees to work fine overall situations except date ranges.
What a crap I made the jump to ADO and now I'm trap, because I can't continue until this really working.

Thanks Patrician.

 

[patrician]

have you tried to hardcode dates into the statement? for example:

gRDB_RS.Filter = "RetrainedDate Between #01/12/01# And #02/12/01#"

-and-

gRDB_RS.Filter = "RetrainedDate Between #01/12/01# And #31/12/01#"

 

[lulo]

Yes as a matter of fact that was my first attempt. Since I copy and paste the SqlStatement from MSAcccess.

It just don't work on any.
It works only when I try this
gRDB_RS.Filter = "RetrainedDate Like " & "8/4/02"

but this one doesn't
gRDB_RS.Filter = "RetrainedDate Like " & "8/*/02"


Thanks

 

[andreng]

Try this:

gRDB_RS.Filter = &quot;RetrainedDate >= &quot; & dtStart & &quot; AND RetrainedDate <= &quot; & dtEnd

 

[SemperFiDownUnda]

Problem 1, I believe if you use #'s that vb ignores regional settings and assumes American Date format of mm/dd/yy or yyyy.

You are doing client side filtering. What you may want to think about is reissueing the query to the server with the where clause having the Between.

Instead of
RetrainedDate Between #01/12/01# And #02/12/01#&quot;
get the format to be
RetrainedDate BETWEEN '12 Jan 2001' AND '12 Feb 2001'

I hate seeing people using ambigious dates

 

[petermeachem]

I agree. I always format the dates for queries.

Like

Between #&quot; & format(dtStart,&quot;dd mmm yyyy&quot & &quot;# AND #&quot; & format(dtEnd,&quot;dd mmm yyyy&quot & &quot;#&quot; Peter Meachem
peter @ accuflight.com