Re: Programatically Change Joins

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 11/02/04

  • Next message: Erik T: "Old programmer needs help" 
    Date: Tue, 2 Nov 2004 16:32:05 -0500

    If you've got fields named "January", "Febuary", etc., then your data is NOT
    normalized. You've got data embedded in the name of the field, for one
    thing.

    Yes, you can change the SQL in a QueryDef object without any problem. You
    need to have a reference set to DAO, and then use something like:

    Dim dbCurr As DAO.Database
    Dim qdfCurr As DAO.QueryDef
    Dim strSQL As String

       Set dbCurr = CurrentDb()
       Set qdfCurr = dbCurr.QueryDefs("MyStoredQuery")
       strSQL = qdfCurr.SQL
       strSQL = Replace(strSQL, ".January", ".February")
       qdfCurr.SQL= strSQL

       Set qdfCurr = Nothing
       Set dbCurr = Nothing

    I'll echo Marsh's comment, though, that you're just asking for future
    problems! 

    --
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Sandy" <Sandy@discussions.microsoft.com> wrote in message
news:645271B8-CA63-4FE5-8F49-645D4C97E6F1@microsoft.com...
> Mr. Barton,
>
> I appreciate your effort to be helpful. However my tables are normalized
and
> I do not need assistance with structuring my database design. I simply
asked
> if there was a way to use SQL in VBA and based on some criteria have the
> joins change dynamically.
>
> This must not be possible otherwise you would have assisted me more
kindly.
>
> Regards,
>
> Ms. Skaar
>
> "Marshall Barton" wrote:
>
> > Sandy wrote:
> >
> > >Is there any way to change field joins in an sql statement or query
based on
> > >some criteria?
> > >
> > >For example:
> > >
> > >If the current month is January then I want to join TableA.Field1 to
> > >TableB.January However, if the month is February then I want to join
> > >TableA.Field1 to TableB.February, without manually changing the link to
> > >TableB.
> > >
> > >Coincidentally, I would always be referencing the previous month
Date()-31
> > >in the database which would = the field name of TableB that I want to
have
> > >TableA joined to.
> > >
> > >Your help is much appreciated on how to accomplish the above.
> >
> >
> > Sandy, you are not going to get much help with that
> > spread***-like arrangement you're struggling with.  It's
> > just plain too far from the standard relational database
> > approach for any one to be helpful.  With your existing
> > design, you are going to be continually running into these
> > complex road blocks.
> >
> > I suggest that you think about your data organization and
> > then ask for help in the tabledesign newsgroup to see if you
> > can put together a more normalized table structure.
> >
> > --
> > Marsh
> > MVP [MS Access]
> >
  • Next message: Erik T: "Old programmer needs help"
    • Quantcast