For *very* simple stuff

Post #151,811

4/19/04 2:06:16 PM

...you can reference a parameter which doesn't exist and Access will prompt you for it:

SELECT * FROM Things WHERE Year = [Enter a year]

...will prompt the user for the year, which obviates the need for all that Form crap for simple queries. IIRC you can even declare the type within an explicit PARAMETERS clause.

Post #151,822

by jbrabeck

4/19/04 3:20:01 PM

Here's the SQL code.

The qParms are what I need to pass into the query.

SELECT Schedule.Level, Schedule.Job, Schedule.Schid, Schedule.System, [Trigger Jobs].StartTm, Schedule.TriggerBy, Schedule.NOEX, CommJobs.Comm INTO [Q-Day13 Schedule]
FROM [Trigger Jobs] INNER JOIN (Schedule LEFT JOIN CommJobs ON Schedule.Job = CommJobs.Job) ON [Trigger Jobs].Link = Schedule.Link
WHERE ((([Trigger Jobs].RunDays)=[qDOM]) AND (([Trigger Jobs].Months)="ALL" Or ([Trigger Jobs].Months) Like [qMonth]) AND (([Trigger Jobs].Calendar)="SP") AND (([Trigger Jobs].BusinessFunction)=[qSystem])) OR ((([Trigger Jobs].RunDays) Like [qAPDay]) AND (([Trigger Jobs].Months)="ALL" Or ([Trigger Jobs].Months) Like [qMonth]) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="M")) OR ((([Trigger Jobs].RunDays) Is Null) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="D")) OR ((([Trigger Jobs].RunDays) Not Like [qAPDay]) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="D")) OR ((([Trigger Jobs].RunDays) Like [qDOW] And ([Trigger Jobs].RunDays) Not Like [qExcept]) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="W")) OR ((([Trigger Jobs].RunDays) Like [qJDate]) AND (([Trigger Jobs].Calendar)="JU") AND (([Trigger Jobs].BusinessFunction)=[qSystem]))
ORDER BY [Trigger Jobs].StartTm, Schedule.Key;

Or the details of the Access code

RunDays	Months	Calendar	Business Function	Frequency
[qDOM]	\ufffdAll\ufffd or Like [qMonth]	\ufffdSP\ufffd	[qSystem]
Like [qAPDay]	\ufffdAll\ufffd or Like [qMonth]	\ufffdAP\ufffd or \ufffdDY\ufffd	[qSystem	\ufffdM\ufffd
Is Null		\ufffdAP\ufffd or \ufffdDY\ufffd	[qSystem]	\ufffdD\ufffd
Not Like [qAPDay]		\ufffdAP\ufffd or \ufffdDY\ufffd	[qSystem]	\ufffdD\ufffd
Like [qDOW] and Not Like [qExcept]		\ufffdAP\ufffd or \ufffdDY\ufffd	[qSystem]	\ufffdW\ufffd
like [qJDate]		\ufffdJU\ufffd	[qSystem]

Post #151,825

by ChrisR

4/19/04 3:53:17 PM

Reformatting a bit

And may not be Access compatible (i.e. alias for the table names):

SELECT \n   sch.Level, \n   sch.Job, \n   sch.Schid, \n   sch.System, \n   trg.StartTm, \n   sch.TriggerBy, \n   sch.NOEX, \n   cmj.Comm INTO [Q-Day13 Schedule]\nFROM [Trigger Jobs] trg\n   INNER JOIN (Schedule sch LEFT JOIN CommJobs cmj ON sch.Job = cmj.Job) ON trg.Link = sch.Link\nWHERE \n   (\n      (trg.BusinessFunction = [qSystem]) AND\n      (trg.RunDays = [qDOM]) AND \n      (trg.Calendar = "SP") AND\n      (trg.Months = "ALL" OR trg.Months Like [qMonth])\n   ) OR (\n      (trg.BusinessFunction = [qSystem]) AND \n      (trg.RunDays Like [qAPDay]) AND \n      (trg.Calendar IN("AP", "DY")) AND \n      (trg.Months = "ALL" OR trg.Months Like [qMonth]) AND \n      (trg.Frequency = "M")\n   ) OR (\n      (trg.BusinessFunction = [qSystem]) AND \n      (trg.RunDays Is Null) AND \n      (trg.Calendar IN("AP", "DY")) AND \n      (trg.Frequency = "D")\n   ) OR (\n      (trg.BusinessFunction = [qSystem]) AND \n      (trg.RunDays Not Like [qAPDay]) AND \n      (trg.Calendar IN("AP", "DY")) AND \n      (trg.Frequency = "D")\n   ) OR (\n      (trg.BusinessFunction = [qSystem]) AND \n      (trg.RunDays Like [qDOW] And trg.RunDays Not Like [qExcept]) AND \n      (trg.Calendar IN("AP", "DY")) AND \n      (trg.Frequency = "W")\n   ) OR (\n      (trg.BusinessFunction = [qSystem]) AND\n      (trg.RunDays Like [qJDate]) AND \n      (trg.Calendar = "JU")\n   )\nORDER BY trg.StartTm, sch.Key;

Post #151,830

by FuManChu

4/19/04 4:40:43 PM

Are all of those date parts from a single date?

That is, if you can derive qDOM, qMonth, qAPDay, qDOW, and qJDate from a single inputted date, then you're left with requesting only three params: qDate, qSystem, and qExcept.

The string-to-date coercion in Access is actually not too bad.

qDOM = Int(Format(CDate([Enter the report date]), 'd'))

etc.

It's doable. Personally, I'd move this kind of logic out of the database entirely and into a middle tier. ;)

Post #151,831

by jbrabeck

4/19/04 4:55:09 PM

qDOM = Calendar day of month 1-31
qMonth = The month
qDOW = Alpha day of week (Sun ... Sat)
qJDate = Julian day
qAP = Day of the accounting period, based on a 28 day month. No correlation to actual calendar date.

Yes, I can generate the first three variable from a single date, and maybe the julian date, but I still need to pass to the query.

Other information that will be provided in number of days to run. That means that the query would be run x times to produce x schedules.

I've been doing it manually, but my co-workers are tech writers and I don't want to risk them missing a variable and not getting the schedules correct.

All for Disaster Recovery.

FWIW, I'm learning Access on the fly. All pointer are appreciated.

I'm trying Jay's VB suggestions and getting an error "Object Required" on the Set Query1 = line

Post #151,838

by JayMehaffey

4/19/04 5:19:22 PM

Sorry

To use the example code I gave you need to do this first. It's one of those things that is so standard I skipped by it. I should also point out that I don't have a copy of Access handy, so there could be typos and other things I've overlooked.

Set db = CurrentDb()

Despite the fact that all Access code exists in the context of a database, Access does not give you a reference to the database by default. Thus the above is usually the first line of non-declare code in any significant Access VB routine.

I believe the reason for that has to do with the fact that you can create multiple database variable and access multiple databases through them. But Access really should give you access to the database the code is in through some default keyword.

Jay

Post #151,823

by andread

4/19/04 3:21:26 PM

Re: For *very* simple stuff

can even do the [enter parameter prompt] thing in QBE

A

Play I Some Music w/ Papa Andy
Saturday 8 PM - 11 PM ET
All Night Rewind 11 PM - 5 PM
Reggae, African and Caribbean Music
[link|http://wxxe.org|Tune In]

Post #151,829

by mmoffitt

4/19/04 4:38:56 PM

Or "query by form"

Welcome to IWETHEY!