Date Range

[D B Lawson]

I want to print worksheets where the WDate field falls between the date range specified in a dialog form. I've tried these different versions of code on the OnClick event of a print button and can't get any of them to work:

DoCmd.OpenReport stDocName, acViewPreview, "[WDate]" & " Between #" & Me!DateFrom & "# AND #" & Me!DateTo & "#"

DoCmd.OpenReport stDocName, acViewPreview, "[WDate] & Between #" & Format(Me.DateFrom, "dd/mm/yy") & "# And #" & Format(Me.DateTo, "dd/mm/yy") & "#"

And

DoCmd.OpenReport stDocName, acPreview, , "[WDate] Between #" & Me![DateFrom] & "# And #" & Me![DateTo] & "#"

They are all compiling OK but I either get all the worksheets or one with Error#

There are reasons why I don't want to use criteria in a query to create the filter.

Any ideas?

Thanks

 

[Carol]

I tried your 1st code on a print button and it worked correctly giving me the correct report and reporting dates. Could it be that you are not asking for the field names of your unbound text boxes correctly.

I am assuming that you have 3 unbound text boxes on your dialog form:
1 - WDate (This would be your worksheetID)
2 - DateFrom
3 - DateTo

 

[D B Lawson]

Hi Carol

WDate is the text field in my report and DateFrom and DateTo are the unbound field on my dialog form and I want to print any worksheets where the WDate text field falls between the DateFrom and DateTo range specified by the user. The first piece of code

DoCmd.OpenReport stDocName, acViewPreview, "[WDate]" & " Between #" & Me!DateFrom & "# AND #" & Me!DateTo & "#"

is opening the worksheet OK but was printing dates outwith the range specified.

Any suggestions?

Thanks

Dawn

 

[Rich]

Curious as to why you can't use a parameter query for the record source?

 

[D B Lawson]

Hi Rich

The reason I don't want to use a parameter query is that I am giving the user 4 options on how to batch print the worksheet, by Job No, by date range by operative or by operative and Job No. That would mean I would need four different queries and either specify the record source when opening the report or have four different reports. I just thought using a case select and code on the print button would be neater.

[This message has been edited by D B Lawson (edited 05-30-2001).]

[This message has been edited by D B Lawson (edited 05-30-2001).]

 

[Talismanic]

D B Lawson,

I have had to do the same thing in one of my applications and here is a cut and paste from the command button. Hopefully you can make it work for you!

Dim bProcOk As Boolean
bProcOk = True

' The following IF Then series checks for null values in
' the stated txtbox. If a null is found the error message
' is displayed and the focus is sent back to form control

If IsNull(Me.txtBeginDate) Then

MsgBox "You must provide a beginning date!", vbExclamation, "Error"
txtBeginDate.SetFocus
bProcOk = False

Else

If IsNull(Me.txtEndDate) Then
MsgBox "You must provide a ending date!", vbExclamation, "Error"
txtEndDate.SetFocus
bProcOk = False

End If
End If

If bProcOk Then ' If the above IF Thens are satisfied
' the following command is carried out

DoCmd.OpenReport "EmpContactLog", acPreview, , _
"[date]>=#" & Me![txtBeginDate] & "# And [date]<= #" & _
Me![txtEndDate] & "#"
' Opens the report based on the begin and end date

End If

 

[D B Lawson]

I had seen your code on another posting and had tried that to with no luck. I think I better give the code another go because it looks like I'm not far off. I'll let you know....

 

[D B Lawson]

Thought I would give you the end to this saga. The reason I was having problems was because I am in the UK and SQL statements don't support regional settings other than US. A couple of articles to read on the subject:
http://support.microsoft.com/support/kb /articles/Q210/0/69.ASP?LN=EN-US&SD=gn&FR=0&qry=Q210069&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

And
http://support.microsoft.com/support/kb /articles/Q208/5/96.ASP?LN=EN-US&SD=gn&FR=0&qry=Q208596&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

Read them the other way around.

I created a new module with the following:

Function MakeUSDate(x As Variant)

If Not IsDate(x) Then Exit Function
MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"

End Function


And used the following code on my print button:

Dim F, T As Variant

Dim stDocName As String

stDocName = "rptWorksheetFromDialog"

Select Case Me.Frame0

Case 1

F = MakeUSDate([DateFrom])
T = MakeUSDate([DateTo])

DoCmd.OpenReport stDocName, acViewPreview, , "[WDate]" & " Between " & F & " AND " & T & ""

Case 2

etc

With big thanks, hugs and kisses to Jack Cowley who sorted this for me. Don't know what I would do without him!

Dawn




[This message has been edited by D B Lawson (edited 06-01-2001).]