Chart Object

  • Thread starter Thread starter Nexus
  • Start date Start date
N

Nexus

I have a chart object on my report and I want to only
retrieve data with a certain date range. Instead of
adding the date parameter into the query where this chart
is reading from, I have a calender macro which I would
like to use instead. This is where the user is able to
select the date range. Any methods to do this?? I've
tried the codes below but doesn't seem to work.

Dim strDate As String

strDate = "[PurchasedDate] Between" & "#" & [Forms]!
[frmPurchasesTable(pop)]![txtStartDate] & "#" & "And"
& "#" & [Forms]![frmPurchasesTable(pop)]![txtEndDate]
& "#"
If Not IsNull(Me!txtStartDate) And Not IsNull(Me!
txtEndDate) Then
DoCmd.Close
DoCmd.OpenReport "rptPurchasesTable", acViewPreview, ,
strDate

Thanks!
 
You would need to place spaces on either side of your "And" and after
Between. I would use the following code:
strDate = "1 = 1"
If Not IsNull([Forms]![frmPurchasesTable(pop)]![txtStartDate]) Then
strDate = strDate & " AND [PurchasedDate] >= #" & _
[Forms]![frmPurchasesTable(pop)]![txtStartDate] & "# "
End If
If Not IsNull([Forms]![frmPurchasesTable(pop)]![txtEndDate]) Then
strDate = strDate & " And [PurchasedDate]<= #" & _
[Forms]![frmPurchasesTable(pop)]![txtEndDate] & "#"
End If
 
I have tried the codes below and have not been successful
with it. Using the codes as provided, gives me the
following error:

"Can't find the form "frmPurchasesTable(pop)" referred to
in a macro expression or visual basic code".
 
The name of the form was copied from your first post. It's your mdb not mine
so I trust your earlier posting. Is the form open? Is the code in the form's
module?

--
Duane Hookom
MS Access MVP


I have tried the codes below and have not been successful
with it. Using the codes as provided, gives me the
following error:

"Can't find the form "frmPurchasesTable(pop)" referred to
in a macro expression or visual basic code".

-----Original Message-----
You would need to place spaces on either side of your "And" and after
Between. I would use the following code:
strDate = "1 = 1"
If Not IsNull([Forms]![frmPurchasesTable(pop)]! [txtStartDate]) Then
strDate = strDate & " AND [PurchasedDate] >= #" & _
[Forms]![frmPurchasesTable(pop)]![txtStartDate] & "# "
End If
If Not IsNull([Forms]![frmPurchasesTable(pop)]! [txtEndDate]) Then
strDate = strDate & " And [PurchasedDate]<= #" & _
[Forms]![frmPurchasesTable(pop)]![txtEndDate] & "#"
End If
Click to expand...
Click to expand...
 
Back
Top