using between for date range

**ChipJohns** · 10:21 AM

Ok,
I've racked my brain. Tried to figure this out.. Help...!

Trying to figure out how to retrieve info within a date range. Here is what I'm doing

Code:

Dim dDateFrom
dDateFrom=FormatDateTime(1/1/2008,2) 
Dim dDateTo
dDateTo=FormatDateTime(1/20/2008,2)
 
strQ1 = "SELECT *"
strQ1 = strQ1 & " FROM smeTracking"
strQ1 = strQ1 & " WHERE smTrDate BETWEEN " & dDateFrom & " AND " & dDateTo
strQ1 = strQ1 & " ORDER BY smTrDate"
strQ1 = strQ1 & ";"
objRS1.Open strQ1

No Errors. Just no results...

What's interesting is that when I change to NOT BETWEEN... All the records show.!?

What am I missing here!?

Thanks for any assistance.

CHip

**ChipJohns** · 03-20-2008, 12:30 PM

And it works!

Code:

Dim dDateFrom
dDateFrom=#01/21/2008# 
Dim dDateTo
dDateTo=#01/25/2008#
strQ1 = "SELECT *"
strQ1 = strQ1 & " FROM smeTracking"
strQ1 = strQ1 & " WHERE smTrDate>=#" &  dDateFrom
strQ1 = strQ1 & "#" & " AND smTrDate<=#" & dDateTo
strQ1 = strQ1 & "# ORDER BY smTrDate"
strQ1 = strQ1 & ";"
objRS1.Open strQ1

**ForrestCroce** · 03-21-2008, 02:28 AM

Microsoft Access?

**ChipJohns** · 03-21-2008, 08:51 AM

YES!

How did you know

**chrishirst** · 03-21-2008, 12:24 PM

He's a mind reader

**Learning Newbie** · 03-21-2008, 02:58 PM

Frosty is always reading my mind. I wish he'd stop. I think he got my ATM PIN number.

03-20-2008, 10:20 AM	#1 (permalink) using between for date range
ChipJohns I don't know! Do you? Posts: 477 Name: Chip Johns Location: Savannah Georgia Talkupation:	Ok, I've racked my brain. Tried to figure this out.. Help...! Trying to figure out how to retrieve info within a date range. Here is what I'm doing Code: Dim dDateFrom dDateFrom=FormatDateTime(1/1/2008,2) Dim dDateTo dDateTo=FormatDateTime(1/20/2008,2) strQ1 = "SELECT " strQ1 = strQ1 & " FROM smeTracking" strQ1 = strQ1 & " WHERE smTrDate BETWEEN " & dDateFrom & " AND " & dDateTo strQ1 = strQ1 & " ORDER BY smTrDate" strQ1 = strQ1 & ";" objRS1.Open strQ1 No Errors. Just no results... What's interesting is that when I change to NOT BETWEEN... All the records show.!? What am I missing here!? Thanks for any assistance. CHip Last edited by ChipJohns : 03-20-2008 at 10:21 AM.*

03-21-2008, 02:28 AM	#3 (permalink) Re: using between for date range
ForrestCroce Half Man, Half Amazing Latest Blog Post: North Lake Union Posts: 2,917 Name: Forrest Croce Location: Seattle, WA Talkupation:	Microsoft Access? __________________ The Pacific Northwest \| Escheresque \| Avery Point, Connecticut

03-21-2008, 12:24 PM	#5 (permalink) Re: using between for date range
chrishirst Super Moderator Posts: 10,609 Location: Blackpool. UK Talkupation:	He's a mind reader __________________ Chris. ->> Links are advertising NOT optimising!! <<- Indifference will be the downfall of mankind, but who cares? Code Samples \| People Counting System

03-21-2008, 02:58 PM	#6 (permalink) Re: using between for date range
Learning Newbie Moderator Latest Blog Post: My Favorite Isaac Asimov Story Posts: 4,068 Name: John Alexander Talkupation:	Frosty is always reading my mind. I wish he'd stop. I think he got my ATM PIN number. __________________ HungarianNotation is the last resort of scoundrels. Why not the first resort? That's where it counts!

03-21-2008, 08:51 AM	#4 (permalink) Re: using between for date range
ChipJohns I don't know! Do you? Posts: 477 Name: Chip Johns Location: Savannah Georgia Talkupation:	YES! How did you know