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

  • Thread starter Thread starter 2vao
  • Start date Start date
2

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.
 
strDoc = "next3days"
strCriteria = "[SDate] BETWEEN #" & Dateadd("d", -1, Date()) & "#"_
& " AND #" DateAdd("d", 3, Date()) & "#"
docmd.openreport strDoc, acViewPreview,,strCriteria
 
2vao said:
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
Click to expand...


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

strLink1= " [SDate]>=" & Format(Me.SDate1, "\#yyyy-m-d\#)
 
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.

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

----
HTH
Dale



2vao said:
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.
Click to expand...
Click to expand...
 
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

2vao said:
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.

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

----
HTH
Dale



2vao said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thank you both for helping me with my date issue, I think Dale's
recommendation could work as well with formats. Thank you Marsh.


Marshall Barton said:
2vao said:
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
Click to expand...


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

strLink1= " [SDate]>=" & Format(Me.SDate1, "\#yyyy-m-d\#)
Click to expand...
 
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.


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

strLink1= " [SDate]>=" & Format(Me.SDate1, "\#yyyy-m-d\#)
Click to expand...
2vao said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top