SQL in VBA/ Date Criteria in table

[ECEstudent]

Hi, I've been stuck on this for a very long time and I would appreciate any help I can get.

I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012

this is what my code looks like so far:

Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")

The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.

Thanks.

 

[pbaldy]

Looks okay to me. ShipDate is a date/time field? You realize you have a 12 year stretch there?

 

[ECEstudent]

yea. But it's not working for me. I don't understand. Everything looks good yet it's returning all the values rather than just the ones under the criteria.

 

[pbaldy]

The only thing I noticed was the parentheses around the Between, which I wouldn't use, but I don't know that they hurt. Can you post the db here to play with?

 

[ECEstudent]

how do i post the db on here? ...I just recently started using this forum

 

[pbaldy]

This should help:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[ECEstudent]

Okay. this is it. Thank you so much!

 

[pbaldy]

ShipDate is Text, not Date/Time. That gives you an alphabetic comparison instead of the date comparison you're expecting.

 

[ECEstudent]

Oh. I didn't notice that.

...But it still doesn't fix the problem. this is so frustrating...

 

[pbaldy]

Fixed it for me (though changing it deleted the bad date). How exactly are you using this that it failed?

 

[ECEstudent]

I open up the DB Module, press F5, then enter 38A1018X003 when it prompts me to enter the part number...
Then I keep pressing enter and EXPECT to get:
1001,11,24
1010
1013

But I now get:
1001,11,24
1010,10,4
1013,31,32

Which means that the date criteria isn't working properly because it's still allowing order number 10 and 31 into the array when the only order numbers that should be in the array are the ones between 9/30/2001 and 10/1/2012

 

[pbaldy]

Haven't tried to follow the whole function, but I added this inside the rst loop:

Debug.Print rst!ShipDate

and the only dates returned were

7/20/2010
9/24/2011

both of which are inside your criteria.

 

[ECEstudent]

I'll give it a shot. Thanks a lot Pbaldy. You were really helpful!

 

[pbaldy]

Happy to help!