SQL problem in Access 2000 2

[theboy]

How do I use a variable in a SQL  'WHERE' statement, which I can retrieve and use at a latter date.

 

[kathryn]

If I understand your question, you want to reuse the parameter that you use in a SQL statement.&nbsp;&nbsp;Where does this SQL statement occur and how are you sending the parameter into it?<br><br>Are you using a native Access query or a stored procedure in SQL?<br><br>Hope I can be of some help.<br><br>Kathryn

 

[theboy]

I am trying to use code to retrieve data from a table called &quot;tblinformation&quot; by using parameters that request the start date of the yearly quarter, and then the end date from the user.&nbsp;&nbsp;These dates are not fixed so require input each time.&nbsp;&nbsp;This data is then used to populate a query table called &quot;tblqdata&quot;.&nbsp;&nbsp;<br><br>By using a number of different sql statements I then retrieve the required specific data for each of the areas specified on the form.<br><br>My problem is that I require to retrieve the values of the initial parameters for use elsewhere on my form so that the user does not have to re-enter them.<br><br>I hope that you can help

 

[kathryn]

This is totally doable.<br><br>On your form, create two textboxs, call them txtStart and txtEnd.&nbsp;&nbsp;Label them something like &quot;Please enter start date&quot; and &quot;Please enter end date&quot;<br><br>In your query, the criteria can refer to this value on the form, as long as YOU DON'T CLOSE THE FORM!&nbsp;&nbsp;Sorry for the caps, but I always make that mistake.&nbsp;&nbsp;When you run the report, (from a button Click event??) make sure that you hide the form, by setting its visible property to false<br><br>Anyway, the criteria for the StartDate Field will look something like Forms!YourFormName!txtStart.&nbsp;&nbsp;Similar for criteria for end date.<br><br>I am not sure what you mean by the different areas on the form.&nbsp;&nbsp;Are these &quot;areas&quot; actually another parameter to the SQL, like the various departments or managers?<br><br>Hope this helps.<br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[jimmythegeek]

If I understand you right, you could do something like this:<br><br>In a Module, create three public variables<br><br>Public gdatStart as Date<br>Public gdatEnd as Date<br>Public gfCancel as Boolean<br><br>You could use an Input box, or a simple date parameters form to retrieve these dates. A date parameter form is better, as you can use formats and input masks to force the user to enter the correct dates.<br><br>So create a small pop-up form with 2 text boxes (txtStart, txtEnd), and 2 butttons (OK, Cancel).<br>Save it as frmDates<br><br>Be sure to format the text boxes and use a date input mask.<br><br>==========<br><br>Put the following on the OK button:<br><br>Private Sub cmdOK_Click()<br>&nbsp;&nbsp;&nbsp;If Not IsNull(Me.txtStart) And Not IsNull(Me.txtEnd) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;gdatStart = Me.txtStart<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;gdatEnd = Me.txtEnd<br>&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Msgbox &quot;You must enter both dates to continue&quot;<br>&nbsp;&nbsp;&nbsp;End If<br>End Sub<br><br>And the following on the Cancel Button:<br><br>Private Sub cmdCancel_Click()<br>&nbsp;&nbsp;&nbsp;gfCancel = True<br>&nbsp;&nbsp;&nbsp;Docmd.Close<br>End Sub<br><br>==========<br>&nbsp;&nbsp;&nbsp;<br>Next, On whatever form you are using to prompt, put the following on a button:<br><br>Private Sub Button_Click()<br>&nbsp;&nbsp;&nbsp;Dim strSQL as String<br><br>&nbsp;&nbsp;&nbsp;DoCmd.OpenForm &quot;FormDates&quot;, , , , , acDialog<br>&nbsp;&nbsp;&nbsp;If Not gfCancel Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strSQL = &quot;SELECT ? FROM ? WHERE(((DateField) Between #&quot; & gdatStart & &quot;# AND #&quot; & gdatEnd & &quot;#));&quot;<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Do whatever else you are going to do.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' You can now retrieve the gdatStart, and gdateEnd from anywhere in your application now.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;gfCancel = False<br>&nbsp;&nbsp;&nbsp;End If<br>End Sub<br><br>==========<br><br>Hope this is what you are talking about doing.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[theboy]

Cheers for the tips above.<br><br>New problem.<br><br>Is there any way I can stop the DCount command using the US date system.&nbsp;&nbsp;<br><br>I am using the DCount command in conjunction with the code mentioned above to produce the total number of records in a field between the two dates supplied in 'gdatstart' and 'gdatend'.&nbsp;&nbsp;The problem is that Access reverts the date to the US system, therefore giving the wrong answer.