I'm trying to create a query that accepts date range criteria from text boxes in a form, and I'm getting puzzling results. I've been stuck for days; time to ask for some help!

In the query the criteria go in an expression that calculates "DueDate". The expression is based on date fields (ShipmentDate from tblShipments, or OrderDate from tblOrders) and number fields (CommDueDays and POPayTerms) to determine when commission is due. Here's the portion of the SQL that calculates DueDate:

IIf([OrderTypeID]=1 Or [OrderTypeID]=5,IIf([Vendors_1].[PayOnID]=1,[ShipmentDate]+[POPayTerms]+7+[Vendo
rs_1].[CommDueDays],[ShipmentDate]+[Vendors_1].[CommDueDays]),[OrderDate]+30) AS DueDate

DueDate is returning the expected (correct) values, so it seems like everything is fine, as long as the query has no criteria in the DueDate field. I also get the desired results if I enter a literal date range in the DueDate criteria like this:
Between #3/1/12# and #3/31/12#

But I run into a problem when I try to enter date range criteria based on input from form controls like this:
Between [Forms]![frmReportCriteria].[txtDate1] and [Forms]![frmReportCriteria].[txtDate2]

When I do this, the returned records have a DueDate with the correct date and month, but they are not limited to the year that was entered. In other words, if I type 3/1/12 in the first text box on the form, and 3/31/12 in the second text box, the record results are limited to March, but for any year.

I've tried changing the criteria to dummy parameter prompts like "Between [SomeDate1] and [SomeDate2]" and typing in the date range at the prompts, and I still get records from any year.

There must be something basic I don't know about the results of an expression to calculate a date, but I can't find it. It makes me think the DueDate value is a string rather than a number. (I tried formatting DueDate properties as "General" and it doesn't work - they are displayed in the short date format no matter what.)

I thought that maybe I couldn't put criteria on a calculated expression, so I tried saving the query with no criteria, and then created another query based on the frst one with criteria in the DueDate field, but I get the same results.

I'm lost and I'd love some help if anyone has a suggestion.

Hi,

What version of Access are you using? Knowing the Access version may help us determine a more appropriate response to your question.

How about if you explicitly format the criteria as a date? For example:

Between "#" & Forms!... & "# And #" & Forms!... & "#"

What do you get then?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Much obliged for the reply DBGuy. Sorry, I should have indicated that I am using Access 2007.

Thanks for the suggestion. I just tried the following with quotes around all the hash tags:

Between "#" & [DateOne] & "#" And "#" & [DateTwo] & "#"

At the prompts I entered 3/1/12 and 3/31/12, but it returned no records. (There should be a couple of hundred.) Then I noticed you had left off the quotes between the hash tags and the word And, so I tried this:

Between "#" & [DateOne] & "# And #" & [DateTwo] & "#"

I got an error saying I was missing the "And". No luck either way.

Is there any way you can post a zip copy of your db with test data?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Try with:

Between DateValue([Forms]![frmReportCriteria].[txtDate1]) And DateValue([Forms]![frmReportCriteria].[txtDate2])

/gustav

Hmmm... Not sure how I could manage that. It's our company DB, lots of confidential data, and it's split between front end client and back end DB.

The behavior is surprising to me. In general, shouldn't an expression in a query based on calculations on a date, returning a date value, be filterable based on criteria?

That's a good idea, Gustav. But, I think it would have to be applied to the calculated column instead of the criteria. For example:

IIf([OrderTypeID]=1 Or [OrderTypeID]=5,IIf([Vendors_1].[PayOnID]=1,DateValue([ShipmentDate])+[POPayTerms]+7+[Vendo
rs_1].[CommDueDays],DateValue([ShipmentDate])+[Vendors_1].[CommDueDays]),DateValue([OrderDate])+30) AS DueDate

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Looks like you found the secret sauce. It's working Well done and thank you.

To clarify, (I misread the order of the replies in the thread) Gustav's suggestion did the trick. I will try TheDBGuy's suggestion too, but it was Gustav's answer that I tried first and it worked. Stand by, I'll check out TheDBGuy's suggestion too.

I was not successful by applying the DateValue function in the expression itself, and using my original criteria, as suggested by TheDBGuy. But when I used my original expression and the DateValue function in the criteria I got the correct results. Thanks to you both for your suggestions. I am very grateful to finally have this working.

Hi,


Thanks for the clarification; although, it sounds odd to me. But I'm sure Gustav can explain why it works because he's really good with Dates, while I'm still learning.

Good luck with your project.

A textbox contains, well, text. So when you refer to it in SQL, you retrieve just the text and not a string representation of a date; for Access, this should be like #yyyy/mm/dd#.

DateValue converts as to the local settings a string that can be read as a date. It returns a value of data type Date which can be compared directly with a field of the same data type.

/gustav

Hi Gustav,


Thanks for the explanation. Didn't realize that DateValue() could be used for that purpose. Why did you prefer to use it over CDate()? Also, why didn't the earlier suggestion of using #s work for the OP?

Thanks again,
DBG

DateValue strips a time part, CDate doesn't.

The string should have read:

" ... Between #" & [DateOne] & "# And #" & [DateTwo] & "# ... "

and that would only work for correctly formatted dates of either m/d/yyyy or yyyy/mm/dd formats. Thus this method fails outside the US.

/gustav

Hi Gustav,

Thanks for the follow up! (IMG:style_emoticons/default/thumbup.gif)