Help Needed with converting query to VBA

[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

 

[Dirk Goldgar]

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.

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 & "))"

 

[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.

 

[Saabster]

Hi John,

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

 

[Saabster]

Dirk,

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

 

[Dirk Goldgar]

Dirk,

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

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 & _
"))"