Concat + SQL

[Bill - ESAI]

Grrr...

I'm having a time building an SQL statement to run on the Me.Filter command

I'm sure it's the format approach I'm trying to use so here's what I have
I already have the SELECT part working, it's qualifying the select statement
that I'm struggling with.
I'm using two form combo boxes with nothing but the 4 digits (set as string)
for the year. e.g.. 2007

the format of the date in the database is Date/Time


strFilter = strFilter & "Status = 'complete'" & " OR " & strManager
& " and Status = 'closed'"
strFilter = strFilter & " AND " & Year([ActualCompDate]) & " >= " &
Forms!frmMainMenu!txtYearLow & ""
strFilter = strFilter & " AND " & Year([ActualCompDate]) & " <= " &
Forms!frmMainMenu!txtYearHigh & ""


When this string hits the filter command I need it to read

Status = 'complete' OR Status = 'closed' AND ActualCompDate >= 2007 AND
ActualCompDate <= 2008

that would be assuming that 2007 & 2008 were the selected values from the
form

Me.Filter = True
Me.Filter = strFilter

My database table name is Projects, so when I tried to use
[Projects].[ActualCompDate] I get an error at run time saying something
about using the | in my statement. Which I haven't used.
If I try to use Format([ActualCompDate], 'yyyy') it won't compile because it
doesn't like the single quotes and double quotes gets me the same error as
above.
If I try to just use [ActualCompDate] I get an error at runtime saying that
[ActualCompDate] doesn't exist.

I can't seem to figure out how to format this query so I can compare a 4
digit year to the completed project date that is stored as a date/time data
type.

Bill

 

[Dirk Goldgar]

in message

Grrr...

I'm having a time building an SQL statement to run on the Me.Filter
command

I'm sure it's the format approach I'm trying to use so here's what I have
I already have the SELECT part working, it's qualifying the select
statement that I'm struggling with.
I'm using two form combo boxes with nothing but the 4 digits (set as
string) for the year. e.g.. 2007

the format of the date in the database is Date/Time


strFilter = strFilter & "Status = 'complete'" & " OR " & strManager
& " and Status = 'closed'"
strFilter = strFilter & " AND " & Year([ActualCompDate]) & " >= " &
Forms!frmMainMenu!txtYearLow & ""
strFilter = strFilter & " AND " & Year([ActualCompDate]) & " <= " &
Forms!frmMainMenu!txtYearHigh & ""


When this string hits the filter command I need it to read

Status = 'complete' OR Status = 'closed' AND ActualCompDate >= 2007 AND
ActualCompDate <= 2008

No, I don't think you do. I think you need it to read:

(Status = 'complete' OR Status = 'closed') AND Year(ActualCompDate) >=
2007 AND Year(ActualCompDate) <= 2008

I don't know what you're doing with strManager, since you haven't mentioned
it, so I'm going to leave it out and suggest code to build the above clause.

Try this:

strFilter = strFilter & "(Status = 'complete' OR Status = 'closed')"
strFilter = strFilter & _
" AND Year([ActualCompDate]) >= " & Forms!frmMainMenu!txtYearLow
strFilter = strFilter & _
" AND Year([ActualCompDate]) <= " & Forms!frmMainMenu!txtYearHigh

That may not be the most efficient clause, in terms of execution by the
query engine, but it should get the job done. If you find that the
resulting filter takes too long to apply, the filter string can be rewritten
in such a way as to avoid calling the Year() function; e.g.,

strFilter = strFilter & "(Status = 'complete' OR Status = 'closed')"
strFilter = strFilter & " AND [ActualCompDate] >= #1/1/" & _
Forms!frmMainMenu!txtYearLow & "#"
strFilter = strFilter & " AND [ActualCompDate]) < #1/1/" & _
Forms!frmMainMenu!txtYearHigh + 1 & "#"

 

[Bill - ESAI]

I'm sure I tried that at least once but your version appears to have done
the trick.

Once again, I extremely grateful

Bill

in message

Grrr...

I'm having a time building an SQL statement to run on the Me.Filter
command

I'm sure it's the format approach I'm trying to use so here's what I have
I already have the SELECT part working, it's qualifying the select
statement that I'm struggling with.
I'm using two form combo boxes with nothing but the 4 digits (set as
string) for the year. e.g.. 2007

the format of the date in the database is Date/Time


strFilter = strFilter & "Status = 'complete'" & " OR " &
strManager
& " and Status = 'closed'"
strFilter = strFilter & " AND " & Year([ActualCompDate]) & " >= "
&
Forms!frmMainMenu!txtYearLow & ""
strFilter = strFilter & " AND " & Year([ActualCompDate]) & " <= "
&
Forms!frmMainMenu!txtYearHigh & ""


When this string hits the filter command I need it to read

Status = 'complete' OR Status = 'closed' AND ActualCompDate >= 2007 AND
ActualCompDate <= 2008

No, I don't think you do. I think you need it to read:

(Status = 'complete' OR Status = 'closed') AND Year(ActualCompDate) >=
2007 AND Year(ActualCompDate) <= 2008

I don't know what you're doing with strManager, since you haven't
mentioned
it, so I'm going to leave it out and suggest code to build the above
clause.

Try this:

strFilter = strFilter & "(Status = 'complete' OR Status = 'closed')"
strFilter = strFilter & _
" AND Year([ActualCompDate]) >= " & Forms!frmMainMenu!txtYearLow
strFilter = strFilter & _
" AND Year([ActualCompDate]) <= " & Forms!frmMainMenu!txtYearHigh

That may not be the most efficient clause, in terms of execution by the
query engine, but it should get the job done. If you find that the
resulting filter takes too long to apply, the filter string can be
rewritten
in such a way as to avoid calling the Year() function; e.g.,

strFilter = strFilter & "(Status = 'complete' OR Status = 'closed')"
strFilter = strFilter & " AND [ActualCompDate] >= #1/1/" & _
Forms!frmMainMenu!txtYearLow & "#"
strFilter = strFilter & " AND [ActualCompDate]) < #1/1/" & _
Forms!frmMainMenu!txtYearHigh + 1 & "#"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)