Custom Query using VBA

[Xiazer]

Hi I am new to this forum and kind of new to access. I am a previous excel VBA semi-guru so im not translating well to access. Here is my problem, I am trying to write a program that does an automatic query when a new record is pulled.
I hope I don't sound foolish as I write this but here goes nothing:

I am writing a DB that allows the users to "predict" Box office results and what I want to do is user's will input their top 5 predictions and that will write to a table, then in another screen I will input the weekend actuals, what I want to do, be it on auto load or a button click, I want to do a query that will "pull" the dates from the predictions table that are the same as the current weekend date and automatically query all the predictions in a temporary prediction query for that specific weekend.

Prediction Table Columns:
User, Date, #1 Prediction, #1 High Gross, #1 low gross...

Weekend Results Table columns
Date, Number 1, Number 1 Gross...

The Prediction Table will hold predictions for all users for all weekends so I just need it to create a query for each Date record in the Weekend Results.

If I didn't clarify, or just wrote this poorly let me know I can clarify as necessary. Thanks in advance.

Q.

 

[raskew]

Hi -

This criteria will return dates from the previous weekend (Saturday and Sunday) based on weeks starting on Sunday (1)

Between weekday(date - weekday(date, 1), 1) AND weekday(Date()- weekday(date, 1) + 1, 1)

Is this what you were after?

Bob

 

[Xiazer]

This is what I have right now, I would like to return the top 3 predictions based on an algorithm I wrote that will return "accuracy" of each user's predictions, I would like to do it automatically whenever you load a different record of Weekend Results, ( the page that displays what the top five movies were over the weekend and their gross) Right now I have this tied to a button that will calculate the results, I would like the top three added to another table that will hold results for each week, 1 record = 1 weekend.

Here is what that button includes now, included commented test code.

Private Sub cmdCalcRes_Click()
'On Error Resume Next
    Dim ResultsCalc As Database
    Dim GetResultsQ As QueryDef
 
    Set ResultsCalc = CurrentDb
    Set GetResultsQ = ResultsCalc.CreateQueryDef("ResultQuery", "SELECT 'Weekend Ending' FROM Predictions")
 
    GetResultsQ.Parameters(1) = Forms!WeekendResults!tstDate
 
    Between Weekday(Date - Weekday(Date, 1), 1) And Weekday(Date - Weekday(Date, 1) + 1, 1)
 
    'GetResultsQ.Parameters("Date") = Forms!WeekendResults!tstDate
 
    'ResultsCalc.ResultsQuery.Delete
    'Close all objects
    GetResultsQ.Close
 
    'Set GetResultsQ = Nothing    
End Sub

Right now I have each week ending Sunday, so the record date will for this weekend is 25 Jan 09.

Thanks again!

Q.