sql query help

[Guest]

This is my first encounter w/ coding a sql update query.

Code:
================================
Option Compare Database
Option Explicit

Function UpdateFiscalCalendarDates()

Dim reportdate As Date
reportdate = DateAdd("d", -7, Date)
Dim strSQL As String

strSQL = "Update Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate " _
& "SET Master.[Month] = DateTranslateTable!CalendarMonth,
Master.FiscalMonth = DateTranslateTable!FiscalMonth " _
& "WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null)
AND ((Master.TranDate)>=[reportdate]));"

Debug.Print strSQL
DoCmd.RunSQL strSQL

End Function

==================================

i am prompted for the reportdate. Can someone help me with the code?

thanks.
patti

 

[Douglas J. Steele]

Queries don't know anything about VBA variables. You have to put the
variable outside of the string, you have to delimit it with # characters,
and you have to ensure that Access recognizes it correctly as a date:

strSQL = "Update Master INNER JOIN DateTranslateTable " & _
"ON Master.TranDate=DateTranslateTable.CalendarDate " & _
"SET Master.[Month] = DateTranslateTable!CalendarMonth, " & _
"Master.FiscalMonth = DateTranslateTable!FiscalMonth " & _
"WHERE Master.Month Is Null " & _
"AND Master.FiscalMonth Is Null " & _
"AND Master.TranDate>=" & Format(reportdate, "\#yyyy\-mm\-dd\#")

 

[fredg]

This is my first encounter w/ coding a sql update query.

Code:
================================
Option Compare Database
Option Explicit

Function UpdateFiscalCalendarDates()

Dim reportdate As Date
reportdate = DateAdd("d", -7, Date)
Dim strSQL As String

strSQL = "Update Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate " _
& "SET Master.[Month] = DateTranslateTable!CalendarMonth,
Master.FiscalMonth = DateTranslateTable!FiscalMonth " _
& "WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null)
AND ((Master.TranDate)>=[reportdate]));"

Debug.Print strSQL
DoCmd.RunSQL strSQL

End Function

==================================

i am prompted for the reportdate. Can someone help me with the code?

thanks.
patti

1) That's because you have enclosed reportdate] within brackets in
your Where clause. Access is looking for a FIELD named reportdate.

2) The reportdate variable must be concatenated into the SQL string.

3) As it's a date it must be enclosed within the date delimiter symbol
(#), so Access recognizes it as a Date.

Try:

& "WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null)
AND ((Master.TranDate)>= #" & reportdate & "#));"

Not knowing how you wish to group the criteria, I won't make any
suggestions about parenthesis placement, but you might not get the
results you expect if the grouping is incorrect. As written above, all
3 criteria must be true. If that is what you want, then you can
simplify the above to:

& "WHERE Master.Month Is Null AND Master.FiscalMonth Is Null
AND Master.TranDate>= #" & reportdate & "#;"

Certainly easier to understand.

 

[Guest]

Doug & fredg-

Thanks. Lesson learned.

patti