Working with dates and SQL string

[mdweezer]

I have a simple form to show a report and the records it shows are based off of the standard "due today", "5 days late", "10 days late" and "More than 15 days late"

Is my code correct for filtering the SQL? I recall having to surround the dates with # but will this suffice?

If intReportOption = 1 Then
LateDate = Date
Else: intReportOption = 2
LateDate = Date - 5
Else: intReportOption = 3
LateDate = Date - 10
Else: intReportOption = 4
LateDate = Date - 15
End If

If intReportOption = 1 Then
qry = qry & "AND ((TBL_Items.DueDate) = " & LateDate & ")) "
Else: intReportOption = 2
qry = qry & "AND ((TBL_Items.DueDate) Between " & DueDate & " And " & LateDate & "));"
Else: intReportOption = 3
qry = qry & "AND ((TBL_Items.DueDate) Between " & DueDate & " And " & LateDate & "));"
Else: intReportOption = 4
qry = qry & "AND ((TBL_Items.DueDate) <= " & LateDate & ")) "

Thanks for the help, these forums have been a live saver!

 

[RoyVidar]

First, does you If construct work?

I'd rather try a select:

[tt]select case intReportOption
case 1
LateDate = Date
case 2
LateDate = Date - 5
case 3
LateDate = Date - 10
case else
LateDate = Date - 15
end select[/tt]

Then - yes - Access needs hash (#) as delimiters for dates, using MSDE/SQL server, single quotes, I think. Also, if your dates differs from US date format, you'll need to format them to a US recognizable date format, for instance:

[tt]"...((TBL_Items.DueDate) Between #" & format$(DueDate, "yyyy-mm-dd") & _
"# And #" & format$(LateDate, "yyyy-mm-dd") & "#));"[/tt]

But you haven't specifiec what problem/errormsg you're having, so there's only guessing...

Roy-Vidar

 

[mdweezer]

Duedate is styled as "##/##/##" and when I pull in the current date it formats that as well.

In my line

qry = qry & "AND ((TBL_Items.DueDate) Between #" & TBL_Items.DueDate & "# And #" & LateDate & "#)) "

I get a Object Required error

 

[RoyVidar]

Now this wasn't in neither your original code nor my suggestion, where DueDate, at least on my part, was assumed to be a variable. If you're refering to a table field, remove the concationation and use "...((TBL_Items.DueDate) Between TBL_Items.DueDate And #" & LateDate & "#))" (between itself and the latedate, if this is what you need - it will probably always be true, but I'm not very good on sql syntax, could it perhaps be better achieved comparing the table date with the late date thru <,>,<=.>= operators?) if it is a variable, remove the table name in front of it.

When date litterals are embedded in vba sql strings they must have a us recognizable format, for instance mm/dd/yyyy or yyyy-mm-dd.

Roy-Vidar

 

[SkipVought]

md,

You just stated...

Duedate is styled as ...

A DATE is just a NUMBER!

Today happens to be 38161, which can be FORMATTED 06/32/04. But 06/32/04 is NOT the DATE VALUE!

Other confusion comes when the DATE VALUE happens to ALSO include TIME. The Date/Time VALUE from just a minute ago was 38161.35784. So if you are stating an expression that includes a Time value (fractions of a day), you may not get the results you are expecting. Best to NORMAILZE your date values, perhapse using the Format function.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[mdweezer]

My code has been settled and it works like I want it to, thanks for the help

If intReportOption = 1 Then
qry = qry & "AND ((TBL_Items.DueDate) = #" & LateDate & "#)) "
Else
qry = qry & "AND ((TBL_Items.DueDate) between #" & Date & "# and #" & LateDate & "#)) "
End If