VB6 - Retrieving records that fall within a date rage

[Giftx]

Hello everyone,
please I need clarification on date range calculation. My objective is to retrieve all records that is 90 days old from today's date:

I am using:

 Between Now() and DateAdd("d", -90, Now())

The above code worked but it doesn't look right to me and it is missing some records.

Any hint will be appreciated.

Giftx.

[RobDog888]

I assume that you are storing date and time as Now() will return a full date/time. Also remember that when you consider date, it counts from 12:00 am until 11:59 pm but in the database world its only comparing 12:00 am as the cutoff.

[Giftx]

Hi Rob.
in that case, I think using "Date" should be appropriate since I don't care about the time.

So what will the correct syntax be?
Thanks.
Giftx.

[RobDog888]

Can you provide an example of how you are storing your dates in the database? Date only or date and time?

[Giftx]

Rob, also, if I use Date within my code, it sorounds the Date with quotes like so: "Date" and that gives me error.

[Giftx]

Date only:

5/22/2008

[RobDog888]

"Date" is a reserved word in Access and other dbs.

" Between #" & Now() & "# AND "# & DateAdd("d", -90, Now()) & "#"

So you are wanting todays date (which is actually all of yesterday abd before. remember the 12:00 am issue) and 90 before ?

[AsmIscool]

If you are after records less than 90 days old then you don't even need the comparison.

Where recDate > Now - 90 will do.

[Giftx]

Rob,
when I plugged in your code all the code is hilited in red, signifying error somewhere.

This is my sql:

strSQL = "SELECT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, " _
& "Original_Check_Number, Amount_of_Check, Original_Check_Date, " _
& "EmployeeInfo.Status, tblL3Desc.L3, tblL3Desc.L3_Desc " _
& "FROM (EmployeeInfo Right JOIN tblUnclaimed ON EmployeeInfo.L1 = tblUnclaimed.L1 " _
& "AND EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) LEFT JOIN tblL3Desc " _
& "ON EmployeeInfo.L1 = tblL3Desc.L1 AND EmployeeInfo.L3 = tblL3Desc.L3 " _
& "AND EmployeeInfo.L5 = tblL3Desc.L5 " _
& "WHERE tblUnclaimed.Original_Check_Date = #" & DateAdd("d", -90, Date) & "# " _
& "AND tblUnclaimed.Status = 'U' ORDER BY tblL3Desc.L3, tblUnclaimed.PassNumber;"

Could you plug it in for me - appropriately?
Thanks.

Giftx

[Giftx]

Hi AsmIscool,
I want to get all records that is 90 days old from today.
Thanks.

Giftx.

[RobDog888]

Red? that is a breakpoint and not an error. If you do get an error what is the message that popups in the msgbox?

strSQL = "SELECT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, " _
& "Original_Check_Number, Amount_of_Check, Original_Check_Date, " _
& "EmployeeInfo.Status, tblL3Desc.L3, tblL3Desc.L3_Desc " _
& "FROM (EmployeeInfo Right JOIN tblUnclaimed ON EmployeeInfo.L1 = tblUnclaimed.L1 " _
& "AND EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) LEFT JOIN tblL3Desc " _
& "ON EmployeeInfo.L1 = tblL3Desc.L1 AND EmployeeInfo.L3 = tblL3Desc.L3 " _
& "AND EmployeeInfo.L5 = tblL3Desc.L5 " _
& "WHERE tblUnclaimed.Original_Check_Date <= #" & DateAdd("d", -90, Date) & "# " _
& "AND tblUnclaimed.Status = 'U' ORDER BY tblL3Desc.L3, tblUnclaimed.PassNumber;"

[Giftx]

Thanks Rob.
Mission accomplished.
That worked.

Giftx.

[Giftx]

Hi Rob,
I think I hastily closed/RESOLVED this thread.

My objective is to retrieve all records that is 90 days old from today's date.

Using the code you gave me, when I looked at the output, it included records from 2002 as well as 2007.

I think it needs a little tweaking.

Below is the code I used:

& "WHERE tblUnclaimed.Original_Check_Date <= #" & DateAdd("d", -90, Date) & "# " _

Thanks.
Giftx.
Giftx.

[RobDog888]

I unresolved your thread and merged your new thread back into this one.

The query shouldnt matter as the DateAdd function is adding days. We will need to add a >= part to chop off the older then 90 records or just use a Between again but you stated you wanted everything older then 90 days but what is the actual date range you need then?

[Giftx]

My actual date range is all record with today's date going back 90 days. Which should be

 Between date - 90 AND Date

Giftx.

[Giftx]

Rob,

This is what I did:

Dim Todaydate As Date

Todaydate = DateAdd("d", - 90, Date)

When I ran the query, Todaydate = 5/23/2008.
Then I plugged in "Todaydate" in my sql.

Look at the hilited code within my sql. This code worked. It gave me 14 records - Records ranging from 5/29/2008 to 7/31/2008
My Sql code:

strSQL = "SELECT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, " _
& "Original_Check_Number, Amount_of_Check, Original_Check_Date, " _
& "EmployeeInfo.Status, tblL3Desc.L3, tblL3Desc.L3_Desc " _
& "FROM (EmployeeInfo Right JOIN tblUnclaimed ON EmployeeInfo.L1 = tblUnclaimed.L1 " _
& "AND EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) LEFT JOIN tblL3Desc " _
& "ON EmployeeInfo.L1 = tblL3Desc.L1 AND EmployeeInfo.L3 = tblL3Desc.L3 " _
& "AND EmployeeInfo.L5 = tblL3Desc.L5 " _
& "WHERE tblUnclaimed.Original_Check_Date Between #" & Todaydate & "# AND Now() " _
& "AND tblUnclaimed.Status = 'U' ORDER BY tblL3Desc.L3, tblUnclaimed.PassNumber;"

is this correct or do I need more tweaking?

Thanks.
Giftx.

[si_the_geek]

You should never simply append a Date value to a String, as the format you get will vary depending on the Regional Settings on the computer at that moment. This is even more true with SQL statements, as having the wrong format will cause errors and/or return the wrong data.

Instead of simply appending you should use the Format function, as shown in the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[RobDog888]

You should never simply append a Date value to a String, as the format you get will vary depending on the Regional Settings on the computer at that moment. This is even more true with SQL statements, as having the wrong format will cause errors and/or return the wrong data.

Yup, agree and was asking about the stored format earlier. Figured we would get there one step at a time.

Can you provide an example of how you are storing your dates in the database? Date only or date and time?

@Giftx, 14 records correct? Dont know as we dont know how many are actually in your table within that range.

[leinad31]

For BETWEEN clause, I suggest you make it a habit to use the lesser value as the range start and the larger value as the range end as there are database engines that will return no records when start > end.

[Giftx]

Rob and all the other good people,
thanks for your input. All the date enumerations got my head spinning but I got it

And Yes, I physically went into my DB and created a query in design view, Ran the query an there were 14 records that match the criteria based on all the date calculation examples given.

God bless you all and you all have a great weekend.
Giftx.

[RobDog888]

Cool, we are all glad to have helped.

Ps, dont forget to Resolve your thread from the Thread Tools menu > "Mark Thread as Resolved" so others know the issue is completed.