date range form values as parameters

[cfnieder]

I am a novice at Access 2007 and need some help please. I have created a date range form to use with several queries and reports. The form passing the newstartdate and newenddate values was successful in the first query. when adding the same code to subsequent queries, the query is expecting a parameter just as before. The only difference in the two queries is that one does not compare a date value inbetween the two start and end dates entered in the form and the second one does. The values in the query look as follows:
NewStartDate: format([forms]![Date Range]![StartDate],"yyyymmdd")
Newstartdate = 20100720
NewEndDate = 20100730
The above data is as expected
The query using a between on the criteria line is usr65val


Between [Newstartdate] and [NewEndDate]

The between statement acts like a parameter query in this query and not in the previous one. I am baffled as to how to correct this. Help!
Each time it expects

[NTC]

Sometimes the answer to "why"? is only found after making it work.... so to focus on making it work I would say that any query that is calling dates from a form should have this type syntax:
>Forms!FormName.[Newstartdate] AND < Forms!FormName.[NewEndDate]

If that doesn't work out of the gate. Put it in step by step, running the query as you go to see the results.

In the end you may discover 'why' 1 query works one way and the other a different way.

[Desstro]

Are you trying to query data from a table that shows you "current" info, i.e., what is and has started but not ended?

If so I imagine in your table and query you have "startdate" and "enddate".

In your query under start date set to <=Date()
and under end date set to >=Date().

So if you are scheduling work for example, this will bring up in your query only data from the table that is currently scheduled for today's system date.

If this isn't what you were going after I apologize...I'm fairly green at Access as well and had a hard time understanding your question.

[Rawb]

Can we see the actual Query itself? It seems to me that the problem almost has to be in there.

[cfnieder]

the portion of the query is in the criteria field for one date(text) value
The value is a paid date value contained in usr65val it is a text field.
The between on the criteria line is as follows:
Between [Newstartdate] and [Newenddate]
Newstartdate: format([forms]![daterange]![startdate],"yyyymmdd")
Newenddate: format([forms]![daterange]![enddate],"yyyymmdd")

The only difference in the date values for the compare is newstartdate and newenddate are left justified and the value in usr65val is right justified. looks like there are blanks in the front and back of the fields but there are not. I have checked. Does this help ?

[Rawb]

I don't see any reason why that wouldn't work (off the top of my head at least) but because you're formatting dates as text strings, you could be running into some strange behavior with the Between criteria.

Is there any reason why you're using a text string instead of actual date values? If not, I'd strongly recommend changing them to Short Date values.

You can convert any text string into a date by using the DateValue() function, like this:

Newstartdate: DateValue([forms]![daterange]![startdate])
Newenddate: DateValue([forms]![daterange]![enddate])

Between [Newstartdate] and [Newenddate]

[cfnieder]

Proprietary software that we are running access reports against. All date fields are text fields.I tried to use the short date field in the beginning but too many places and too many sub queries already existed. I am correcting about 50 reports and have run into the text fields everywhere. It was less costly to leave them as text fields until now. The client did not like entering the date field as 20100701 rather than using the calendar on the date form. They really liked that. Now that I have shown that to them I really need to make this work. Could it be the format of the date field that is the problem. I used an iif statement in the other query but it did not work with this field. I'll try converting it to a date field and see what happens. Thanks for the suggestion.

[Rawb]

Wait, just to make sure I'm understanding this. . .

The Form that the user looks at uses an actual Date field while the Query uses a Text string? Or are they both Text strings?

If they're both Text strings, then I agree that you would be better off finding out a way to make your current setup work. Which also means that I still don't have any idea as to what's happening to make it NOT work :/

If the user is entering an actual Date though, that may be your problem. If that's the case, try changing your code to something like the following:

Assuming. . .
startdate is a Short Date formatted Text Box on the Form,
enddate is a Short Date formatted Text Box on the Form,

strDateStart = CStr(Format("yyyyMMdd", [forms]![daterange]![startdate])
strDateEnd = CStr(Format("yyyyMMdd", [forms]![daterange]![enddate]))

The above code takes an actual Date value and converts it to a text string containing the four-digit year, two-digit month, and two-digit day all squished together with no delimiters. This will let you use an actual Date Field in your Form and still give the Query a format it expects (Note the capital "M"s in the Format Method. If you use lowercase, it thinks you're looking for minutes instead of months!).

[cfnieder]

The problem is solved. Several of the suggestions were used to solve the problem. Between seems to be tempermental when using a converted date none of those between statements worked. When I used the following with between it worked. I put the following in the criteria line of my query and the results are the same as when I had the original parameter query. I still don't understand why formating the value first and then using the between did not work. but Oh well....

Between format([forms]![date range]![startdate],"yyyymmdd") and format([forms]![date range]![enddate],"yyyymmdd")


Thank you all for all your help ... This was a great experience using the forum and I learned a lot. Y'all are great !!! Hope I can help some one soon.