Help Needed with converting query to VBA

C

Craig M. Bobchin

Hi all,

I'm trying to set the rowsource in a combobox in VBA. I've broken the
SQL into 3 sections to better debug any issues. I'm having problems with
the Where clause below:

strCriteria = "WHERE (((tblWeekEnding.WeekEnding) " & ">" & DateAdd
("m",-1,Date() & ")) AND ((tblTimeTrack.ResourceID)=" & intResourceId &
"))"

Can anyone help me sort out why Access/VBA doesn't like the line?
intResourceID is a defined global variable.

Thanks

Craig
 
D

Dirk Goldgar

Craig M. Bobchin said:
Hi all,

I'm trying to set the rowsource in a combobox in VBA. I've broken the
SQL into 3 sections to better debug any issues. I'm having problems
with the Where clause below:

strCriteria = "WHERE (((tblWeekEnding.WeekEnding) " & ">" & DateAdd
("m",-1,Date() & ")) AND ((tblTimeTrack.ResourceID)=" & intResourceId
& "))"

Can anyone help me sort out why Access/VBA doesn't like the line?
intResourceID is a defined global variable.
Click to expand...

One thing that's missing is the date delimiters (#) around the date
value. It's also a good idea to format all date literals so that they
are either completely unambiguous, or else in the US "mm/dd/yyyy"
format. You might try this:

strCriteria = _
"WHERE ((tblWeekEnding.WeekEnding > " & _
Format(DateAdd("m",-1,Date(), "\#mm/dd/yyyy\#") & _
") AND (tblTimeTrack.ResourceID = " & _
intResourceId & "))"
 
J

John Spencer

Well, I use Debug.Print to see what is constructed. Once I have a valid
phrase I drop the debug.print strCriteria statement.

Assumption:
WeekEnding is a DATE field.

strCriteria = "WHERE tblWeekEnding.WeekEnding > #"
& DateAdd("m",-1,Date() & "# AND tblTimeTrack.ResourceID=" & intResourceId

Debug.Print StrCriteria 'Return value in the immediate window to check for
validity.
 
S

Saabster

Hi John,

Thanks but that did not work either. the VBA window still shows it in
red.
 
D

Dirk Goldgar

Saabster said:
Dirk,

Thanks, but that was a no go. VBA window still shows the line in red.
Click to expand...

Whoops, missing a closing parenthesis on the DateAdd function. This
compiles for me:

strCriteria = _
"WHERE ((tblWeekEnding.WeekEnding > " & _
Format(DateAdd("m", -1, Date), "\#mm/dd/yyyy\#") & _
") AND (tblTimeTrack.ResourceID = " & _
intResourceId & _
"))"
 
Top