Append query: from start to end week

[zfind]

Hi all, been a while since I asked for help but it's that time again!

I working on an append query. The query is creating a record in tblRecords for each combination of Partner, Week and Genre. So if we had Partners A, B and C who use Genres 1 and 2 and we have 52 weeks, that would result in 312 new records. This is all well and good, but each Partner has a defined start week and year and an end week and year. I want the query to only append records on and between those dates. So if Partner A would start on week 27, 2008 and end on week 52, 2008, I would need this to be taken into account.

How could I achieve this? I know I can get parameters from a form (which is where this query is laucnhed from), use global variables and such but I don't know how to actually implement this. Maybe there is an easy way?

Each partner record has the week and year data stored within it.

Thanks!

 

[neileg]

If you have the start then all you need to do is add 0 to 51 weeks to the date. You haven't explained how you are creating the 52 records, though.

I can't help feeling this is poor design. It's not normally a good idea to create empty records, though there are exceptions.

 

[namliam]

I can't help feeling this is poor design. It's not normally a good idea to create empty records, though there are exceptions.

Just want to echo this from Neil

Now for part of your question...

You can refer to forms or global variables by substituting them into SQL, which works more or less like so:
strSQL = ""
strSQL = strSQL & "Select ..."
For number values:
strSQL = strSQL & "where thisandthatfield = " & YourGlobal
strSQL = strSQL & "and anotherfield = " & Forms("FormName").Control("ControlName")
For text values:
strSQL = strSQL & "where thisandthatfield = '" & YourGlobal & "'"
For date values:
strSQL = strSQL & "where thisandthatfield = #" & YourGlobal & "#"

I hope that helps you along.

Good luck

 

[zfind]

Thanks guys, as far as bad design goes, tell my boss that. As much as I'd like to do it my way, I can't

The records need to be created all at once and empty so that revenue forecasting data can be imported into them - so not really empty.

The above is just what you helped me with before namliam, thanks. How can I use this in an append query to create records only between value x and value y (or cbobox x and cbobox y)?

 

[namliam]

Well go into the designer, create your query there.
Then go to SQL view and copy over the SQL to your code and addapt it.

To refer to forms, see my previous post... Use: Forms("FormName").Control("ControlName")

As for your boss, I think I must have said that before too... We are the developers NOT these 2 bit managers who think they own the world!

 

[zfind]

Tell me about it, he's got his head in the clouds and wants to implement every bloody feature under the sun regardless of how inefficient, time-consuming or useless it is.

Thanks.