sql query help

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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\#")
 
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
Click to expand...

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.
 
Back
Top