Wherecondition problem

[OIOIO]

Hello,

I am trying to filter a report based on two user inputed dates, but can't seem to figure it out. I've played around with quotation marks, and # but can't seem to figure the syntax out. Anyone know what I'm missing here?

Me.OrderBy = "Date Submitted"
Me.OrderByOn = True


Dim Date1 As Date
Dim Date2 As Date

Date1 = UserInput
Date2 = UserInput

DoCmd.ApplyFilter WhereCondition:="[Date Submitted] > Date1 and < Date2"

 

[dinoc]

The format should be like this:

DLookup("FieldName" , "TableName" , "Criteria= #date#")

 

[pr2-eugin]

Almost dinco

It should be,

DoCmd.ApplyFilter WhereCondition:="[Date Submitted] > " & Format(Date1, "\#mm\/dd\/yyyy\#") & _
                             " AND [Date Submitted] < " & Format(Date2, "\#mm\/dd\/yyyy\#")

 

[OIOIO]

Sorry I'm pretty new to Access and don't understand your reply. Do I use this code in place of the ApplyFilter? If so how do I set the criteria to be after Date1 but before Date2?

 

[dinoc]

Sorry I meant the end of the DLookup is how to format dates.
But try something like this (I havent checkted it though):

WhereCondition:="[Date Submitted] <= #" & Date1  & "# AND ([Date Submitted] >= #" & Date2 & "#")

 

[dinoc]

Sorry forgot to ask, where are you getting your dates from?
e.g. from a form control?

 

[OIOIO]

I'd like to get the dates from the user when the report is generated. You code works, but the messagebox asking for date1 and date2 no longer appears.

 

[OIOIO]

pr2-eugin your code works but has the same problem, the message box asking the user for dates no longer appears. Do I need to do more than just set Date1 and 2 to UserInput?

 

[pr2-eugin]

the message box asking the user for dates no longer appears.

You mean?

Me.OrderBy = "Date Submitted"
Me.OrderByOn = True


Dim Date1 As Date
Dim Date2 As Date

Date1 = [COLOR=Red][B]InputBox[/B][/COLOR]("Enter the Start Date : ")
Date2 = [B][COLOR=Red]InputBox[/COLOR][/B]("Enter the End Date : ")

DoCmd.ApplyFilter WhereCondition:="[Date Submitted] > " & Format(Date1, "\#mm\/dd\/yyyy\#") & _
                             " AND [Date Submitted] < " & Format(Date2, "\#mm\/dd\/yyyy\#")

 

[dinoc]

I'd like to get the dates from the user when the report is generated. You code works, but the messagebox asking for date1 and date2 no longer appears.

Do you get the input boxes when you first try it and then no longer works or you just dont get them at all?

Sorry pr2-eugin didnt see your post, I was too busy look at my quotes and hash marks haha

 

[OIOIO]

Yes this works. Thank you!

 

[pr2-eugin]

You're welcome !