Dynamic Querying with VBA

[MonTpython]

I am working on a database that will check different projects for date
conflicts and will be linking to quite a few excel spreadsheets as data
sources for the individual project tables. The problem I am looking at is
trying to automate the process as much as possible through VBA.

I would like to know if there is a way to use VBA to set query criteria.
The reason for this is even though each table will have different projects,
the criteria will be identical for each of them and I would like to avoid
having to go in to manualy add in the criteria for each new table as it is
relatively lengthy.

The criteria I need to replicate is as follows
IIf(IsNull([Forms]![frmProjectCentral]![txtProjEnd]),[EagleStart],IIf(([Forms]![frmProjectCentral]![chkEagle]<0),IIf(([EagleStart]>[Forms]![frmProjectCentral]![txtProjEnd]),[EagleStart]),[EagleStart])) Or Is Null

The point of this criteria is to determine if the text box txtProjEnd on the
control form is null (if so give everything) if not see if the check box
associated with the project chkEagle is checked and if so apply the final
test of whether the new project date ends prior to the existing project's
start date.

Any help would be greatly appreciated
Thanks!

 

[Tom van Stiphout]

On Tue, 22 Apr 2008 16:39:01 -0700, MonTpython

Stop right there! "Each table will have different projects"??? That
sounds a lot like you are working with an Excel state of mind and not
a Relational Database state of mind. Rather there should be a single
Projects table. If not, tar and feathers are in your immediate future.
Seriously, if you don't know how to use the strengths of a relational
database, you're in for a world of hurt and it would be much better to
hire that expertise. "Microsoft Solution Provider" in your yellow
pages may be a good place to start.

-Tom.

 

[MonTpython]

It's not that every project will be housed in its own table, most, if not all
will be appended into a single master project table, at most there will be
2-3 tables being queried but a total of dozens of fields each needing to fit
the date conflict criteria. The multiple sources are coming from other people
with their own finished product that I must work with as-is.

Basically I need to test every existing project start/end dates to ensure
that the user defined dates of a new project do not overlap on the front end
or back end. I need to apply the same basic criteria to each field but don't
know how to do that short of manually entering the criteria into each field.

On Tue, 22 Apr 2008 16:39:01 -0700, MonTpython

Stop right there! "Each table will have different projects"??? That
sounds a lot like you are working with an Excel state of mind and not
a Relational Database state of mind. Rather there should be a single
Projects table. If not, tar and feathers are in your immediate future.
Seriously, if you don't know how to use the strengths of a relational
database, you're in for a world of hurt and it would be much better to
hire that expertise. "Microsoft Solution Provider" in your yellow
pages may be a good place to start.

-Tom.

I am working on a database that will check different projects for date
conflicts and will be linking to quite a few excel spreadsheets as data
sources for the individual project tables. The problem I am looking at is
trying to automate the process as much as possible through VBA.

I would like to know if there is a way to use VBA to set query criteria.
The reason for this is even though each table will have different projects,
the criteria will be identical for each of them and I would like to avoid
having to go in to manualy add in the criteria for each new table as it is
relatively lengthy.

The criteria I need to replicate is as follows:
IIf(IsNull([Forms]![frmProjectCentral]![txtProjEnd]),[EagleStart],IIf(([Forms]![frmProjectCentral]![chkEagle]<0),IIf(([EagleStart]>[Forms]![frmProjectCentral]![txtProjEnd]),[EagleStart]),[EagleStart])) Or Is Null

The point of this criteria is to determine if the text box txtProjEnd on the
control form is null (if so give everything) if not see if the check box
associated with the project chkEagle is checked and if so apply the final
test of whether the new project date ends prior to the existing project's
start date.

Any help would be greatly appreciated
Thanks!