By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

refering to variable in a form.

P: 12
I have a form that the user enters a financial quarter and year to produce a report. the report is based on a query that uses the quarter and year. seems simple enough. The financial quarter and year are drop down boxes. When the run query button is pushed the form concantinates the strings QueryStartDate and QueryEndDate. How do I refer to these variables in my query expression?

Public Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String
Dim QueryEndDate As String
Dim QueryStartDate As String

If Me.Quarter = 1 Then
QueryStartDate = "01/01/" & Me.Year
QueryEndDate = "04/01/" & Me.Year
ElseIf Me.Quarter = 2 Then
QueryStartDate = "04/01/" & Me.Year
QueryEndDate = "07/01/" & Me.Year
ElseIf Me.Quarter = 3 Then
QueryStartDate = "07/01/" & Me.Year
QueryEndDate = "09/01/" & Me.Year
ElseIf Me.Quarter = 4 Then
QueryStartDate = "09/01/" & Me.Year
QueryEndDate = "12/31/" & Me.Year
End If
stDocName = "QuarterlyTestReport"
DoCmd.OpenReport stDocName, acPreview

Thanks in advance
PS if there is a simpler way to do this I would like to know.
Feb 10 '08 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You don't reference them in your query expression. You set them as criteria in the OpenReport function.
Expand|Select|Wrap|Line Numbers
  1. Public Sub Preview_Report_Click()
  2. On Error GoTo Err_Preview_Report_Click
  3.  
  4.     Dim stDocName As String
  5. Dim stLinkCriteria As String
  6.     Dim QueryEndDate As String
  7.     Dim QueryStartDate As String
  8.  
  9.     If Me.Quarter = 1 Then
  10.         QueryStartDate = "01/01/" & Me.Year
  11.         QueryEndDate = "04/01/" & Me.Year
  12.     ElseIf Me.Quarter = 2 Then
  13.         QueryStartDate = "04/01/" & Me.Year
  14.         QueryEndDate = "07/01/" & Me.Year
  15.     ElseIf Me.Quarter = 3 Then
  16.         QueryStartDate = "07/01/" & Me.Year
  17.         QueryEndDate = "09/01/" & Me.Year
  18.     ElseIf Me.Quarter = 4 Then
  19.         QueryStartDate = "09/01/" & Me.Year
  20.         QueryEndDate = "12/31/" & Me.Year
  21.     End If
  22.     stDocName = "QuarterlyTestReport"
  23.     stLinkCriteria = "[DateField] BETWEEN #" & QueryStartDate & "# AND #" & QueryEndDate & "#"
  24.  
  25.     DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  26.  
Feb 10 '08 #2

P: 12
Thanks that worked perfectly
Feb 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.