Access Variables and SQL

Yea, your probably right but...  I'm really not that familar with VBA.  Thats what I really like about Access anyway - you don't have to code.  

I guess the question is:  How do I make an SQL select statement use a global variable declared in VBA?

Don't have to code?  I enjoy watching people use vanilla Access, they go "hey! I wanted it to make a new record and it just updated my existing record"  Record #1 winds up always getting modified undesirably is my favorite vanilla Access feature hehehe.  Worst bug (feature) ever in Access is the inability to search on record being entered and distinguish between users desire to find/update/edit or add-new.

I'm not entirely sure on VBA, but if it holds true to VB's methods, then placing the code in Report_Open may be causing the problem. Try moving the code to Report_Activate.

As I recall from my MCSD classes, a form (or in this case report) opens in the following order
OPEN
ACTIVATE
PAINT

And data objects on or in the form get initialized in OPEN, so they aren't available or won't respond to commands until Activate.

I may be a little off on the explanation here, but I think the solution is sound. Give it a try.

I've placed a demo code here for you to download at
http://web.nova1.net/rgcs/quickSQL.htm
please grab it, unzip it and evaluate its usefulness to what you are doing and let me know if you feel it answers your question.

Bear in mind the SQL in this style of use is being inserted directly into the FORM data source property and as such is very limited.  You would do better to use an ACTUAL query as the source and insert the Query into there and just reference the query name as the source.  

This method uses an actual form which could be the form you use to query the user for which date or date range etc they wish.  Notice if you preview the report it asks for a Month (enter a Low number for the demo data is all under month 7) and it will display the report for only months over the month number entered.  Then close the report and previwe it again and it will use the same data without prompting again.  This use of a form control as a Global variable is an old trick which still works well in access.  It's not pretty but works pretty effectively.  You can use a form just for this and hide the form it is all stored on easily enough.  In fact I use the forms hidden yet or not property to determine if you've been prompted for the month or not.

It's not Report Open versus Activate.  It is not allowing him to use the Global Public variable in the query because it may be too complex to stuff that into a form data source property directly.  He can either use the demo code I've provided or use an actual saved QUERY name to reference a full real query instead of putting it into the form data property directly.

kkirt1,

I'm not sure you can accomplish this without a little code try this:

Create a new module (Database Window, Under the Module tab)and enter the following code:

'****BEGIN CODE****
Public gdteEndDate As Date
Public gintYear As integer

Public Function GetEndDate() As DAte
    GetEndDate= gdteEndDate
End Function

Public Function getYear () as Interger
    GetYear = gintYear
End Fnction

'****END CODE****

Next do the follwoing:
As dclary stated, you might not be able to do this from your Report/Open event--if it does not work, put the code behind a button on a form. Put the following code in report or a form:


'****BEGIN CODE****

gdteEndDate = InputBox("Please enter period", "User Input Required")
gintYear = InputBox("Please enter year", "User Input Required")

'Open your report here if it dosn't work from Report Open Event

'****END CODE****

Then lastly, in  your query, in the Criteria row under the appropriate fields type the following (don't forget the parenthesis):

GetEndDate()
GetYear()

respectivly.

There are pros/cons to using this approach, but it is, IMO, the easiest way to accomplish this. If you need more details or a better explanation, let me know..

Gus

Sorry Rayford, I just keyed in on his opening line saying it was all in the onopen event. I agree that it's a relatively simple thing to build the query he needs and then run the report off that query. I suspect that should be the winning answer.

The zip downloaded from the above address has two query report formats one uses SQL in the source property of the report and the other uses the actual Query name instead.

Think I'm done with this for now unless you provide feedback as to how useful it may have been.
Let me know if you want more.

Thanks Rayford - it looks like that will work.  I never though about using a form for user input. I've been swamped lately.  As soon as I get a chance to test it I'll let you know so you can pick up your points.  Thanks again.

Works like a charm!  The points are yours for the taking.  I do have one more question though.  I would like to clear the variables after I close the report.  Any ideas?

Thanks!