how to write >=Date()-1 And <Date()+3 in VBA

[2vao]

Hi,

I want to open a report using a form but I need to write the above criteria
in VBA. This is what I tried

strDoc = " next3days "
strLink1= " [SDate]>=" & Me.SDate1 ' SDate1=Date-1
strLink2= " [SDate]<=" & Me.SDate2 ' SDate2 = Date+3
DoCmd.openReport strDoc, acViewPreview, ,strLink1 & " AND " & strLink2
Please excuse my crazy attempt.
Many thanks.

 

[Dale Fye]

strDoc = "next3days"
strCriteria = "[SDate] BETWEEN #" & Dateadd("d", -1, Date()) & "#"_
& " AND #" DateAdd("d", 3, Date()) & "#"
docmd.openreport strDoc, acViewPreview,,strCriteria

 

[Marshall Barton]

I want to open a report using a form but I need to write the above criteria
in VBA. This is what I tried

strDoc = " next3days "
strLink1= " [SDate]>=" & Me.SDate1 ' SDate1=Date-1
strLink2= " [SDate]<=" & Me.SDate2 ' SDate2 = Date+3
DoCmd.openReport strDoc, acViewPreview, ,strLink1 & " AND " & strLink2

You need to delimit and format the date values to a
standard:

strLink1= " [SDate]>=" & Format(Me.SDate1, "\#yyyy-m-d\#)

 

[2vao]

Thank you for your quick response, I literally copied your code ( added '&'
after " AND#" ), I did not get an error msg but the report came blank.
something is still not ok.

strDoc = "next3days"
strCriteria = "[SDate] BETWEEN #" & Dateadd("d", -1, Date()) & "#"_
& " AND #" DateAdd("d", 3, Date()) & "#"
docmd.openreport strDoc, acViewPreview,,strCriteria

----
HTH
Dale

Hi,

I want to open a report using a form but I need to write the above criteria
in VBA. This is what I tried

strDoc = " next3days "
strLink1= " [SDate]>=" & Me.SDate1 ' SDate1=Date-1
strLink2= " [SDate]<=" & Me.SDate2 ' SDate2 = Date+3
DoCmd.openReport strDoc, acViewPreview, ,strLink1 & " AND " & strLink2
Please excuse my crazy attempt.
Many thanks.

 

[Dale Fye]

Are you sure you actually have data for the date span 4/19/09-4/23/09?

Open the query that the report is based on. Then sort on the [SDate] field
and look for records in the appropriate range. It may be that the original
query is setup incorrectly, resulting in not actually having records that
match the criteria.

Dale

Thank you for your quick response, I literally copied your code ( added
'&'
after " AND#" ), I did not get an error msg but the report came blank.
something is still not ok.

strDoc = "next3days"
strCriteria = "[SDate] BETWEEN #" & Dateadd("d", -1, Date()) & "#"_
& " AND #" DateAdd("d", 3, Date()) & "#"
docmd.openreport strDoc, acViewPreview,,strCriteria

----
HTH
Dale

Hi,

I want to open a report using a form but I need to write the above
criteria
in VBA. This is what I tried

strDoc = " next3days "
strLink1= " [SDate]>=" & Me.SDate1 ' SDate1=Date-1
strLink2= " [SDate]<=" & Me.SDate2 ' SDate2 = Date+3
DoCmd.openReport strDoc, acViewPreview, ,strLink1 & " AND " & strLink2
Please excuse my crazy attempt.
Many thanks.

 

[2vao]

Thank you both for helping me with my date issue, I think Dale's
recommendation could work as well with formats. Thank you Marsh.

I want to open a report using a form but I need to write the above criteria
in VBA. This is what I tried

strDoc = " next3days "
strLink1= " [SDate]>=" & Me.SDate1 ' SDate1=Date-1
strLink2= " [SDate]<=" & Me.SDate2 ' SDate2 = Date+3
DoCmd.openReport strDoc, acViewPreview, ,strLink1 & " AND " & strLink2

You need to delimit and format the date values to a
standard:

strLink1= " [SDate]>=" & Format(Me.SDate1, "\#yyyy-m-d\#)

 

[Marshall Barton]

Sometimes it might work, but it might not work on all
machines. If you do not explicitly specify the format, then
Access will use the Windows regional settings for dates.
Those settings can easily be set to a format that Access can
not interpret correctly. Even if you set your machine up so
it works for you, other users may set their machines so it
won't work.
--
Marsh
MVP [MS Access]

Thank you both for helping me with my date issue, I think Dale's
recommendation could work as well with formats. Thank you Marsh.

You need to delimit and format the date values to a
standard:

strLink1= " [SDate]>=" & Format(Me.SDate1, "\#yyyy-m-d\#)

I want to open a report using a form but I need to write the above criteria
in VBA. This is what I tried

strDoc = " next3days "
strLink1= " [SDate]>=" & Me.SDate1 ' SDate1=Date-1
strLink2= " [SDate]<=" & Me.SDate2 ' SDate2 = Date+3
DoCmd.openReport strDoc, acViewPreview, ,strLink1 & " AND " & strLink2