Re: Access Update Query

From: KelMon (KelMon_at_discussions.microsoft.com)
Date: 03/21/05 

Date: Mon, 21 Mar 2005 04:25:04 -0800

That worked. Thank you.

"MGFoster" wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> So maybe Access doesn't "like" an INNER JOIN on an UPDATE, especially w/
> a BETWEEN in the ON clause. Try this:
>
> UPDATE GL_ALL AS GL, Fiscal_Yr_Table AS FY
> SET GL.Period = FY.Period,
> GL.Week = FY.Week
> WHERE GL.Import_Date BETWEEN FY.Start_Date AND FY.End_Date
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQjJq8oechKqOuFEgEQLXCwCfTOJozPhM8FD3vR4R/iTeR/Un7gQAnj7G
> mMJcTVzqWPqEzMOzMfsQE3u5
> =BOHP
> -----END PGP SIGNATURE-----
>
>
> KelMon wrote:
> > I copied it straight from your response.
> >
> > "MGFoster" wrote:
> >
> >
> >>You didn't copy the "AND FY.End_Date" part of the line.
> >>
> >>--
> >>MGFoster:::mgf00 <at> earthlink <decimal-point> net
> >>Oakland, CA (USA)
> >>
> >>KelMon wrote:
> >>
> >>>I'm getting an error of "Between operator without And in query expresion
> >>>'GL.Import_Date BETWEEN FY.Start_Date'." ?
> >>>
> >>>"MGFoster" wrote:
> >>>
> >>>
> >>>
> >>>>KelMon wrote:
> >>>>
> >>>>
> >>>>>I need to convert this SQL statement to a statement that Access 2003 will
> >>>>>run. Any suggestions?
> >>>>>
> >>>>>I have two tables. GL_ALL contains all imported records with blank Period
> >>>>>and Week but the Import date is poppulated with a calendar date.
> >>>>>
> >>>>>Fiscal_Yr_Table contains the calendar for the fiscal year. It has the
> >>>>>period, week, start date and end date for each period and week
> >>>>>
> >>>>>I want to update the GL_ALL table with the proper Period and Week from the
> >>>>>Fiscal_Yr_Table based on the Import date in the GL_ALL table.
> >>>>>
> >>>>>UPDATE GL_ALL
> >>>>>SET GL_ALL.Period = Fiscal_Yr_Table.Period, GL_ALL.Week=Fiscal_Yr_Table
> >>>>
> >>>>>FROM Fiscal_Yr_Table,GL_All
> >>>>
> >>>>>WHERE (GL_ALL.Import_Date>=Fiscal_Yr_Table.Start_Date) AND
> >>>>>(GL_ALL.Import_Date<=Fiscal_Yr_Table.End_Date);
> >>>>>
> >>>>>This works in SQL server but not in Access
> >>>>
> >>>>-----BEGIN PGP SIGNED MESSAGE-----
> >>>>Hash: SHA1
> >>>>
> >>>>Perhaps:
> >>>>
> >>>>UPDATE GL_ALL AS GL INNER JOIN Fiscal_Yr_Table AS FY
> >>>> ON GL.Import_Date BETWEEN FY.Start_Date AND FY.End_Date
> >>>>SET GL.Period = FY.Period,
> >>>> GL.Week = FY.Week
> >>>>
> >>>>--
> >>>>MGFoster:::mgf00 <at> earthlink <decimal-point> net
> >>>>Oakland, CA (USA)
> >>>>
> >>>>-----BEGIN PGP SIGNATURE-----
> >>>>Version: PGP for Personal Privacy 5.0
> >>>>Charset: noconv
> >>>>
> >>>>iQA/AwUBQjIHMIechKqOuFEgEQLoCgCeLUmlGDvqxidczSXU/MRTu/oiTDUAoKk5
> >>>>eoKwF8oenNnxinHTBQOKByON
> >>>>=PyOi
> >>>>-----END PGP SIGNATURE-----
> >>>>
> >>
>

Relevant Pages