Need parameter assistance AGAIN!

[fhs]

Something doesn't work anymore! Now, I've input correct data exactly as it will be entered daily, the report query isn't getting anything! If Bob is around, would he please look at this. Many thanks in advance.

 

[boblarson]

I did this and it seemed to do the trick (I should have had that in there like that to begin with):

 

[fhs]

Again, I appreciate your insight about this query and I wanted to ask you for an explanation of the two "Or" statements. But more importantly, when I run the report with the parameters, I am unable to get all the data that I should relative to the table of data. Would you mind taking a look again. Thanks...

 

[Brianwarnock]

As this is 2007 I cant look at it but if it works the same as previous releases why not use a Form for the Start and End dates to save the multiple entry requests.

Brian

 

[fhs]

Well, I'm a little rusty with that. Do you mind walking me through the form creation.

 

[boblarson]

Again, I appreciate your insight about this query and I wanted to ask you for an explanation of the two "Or" statements. But more importantly, when I run the report with the parameters, I am unable to get all the data that I should relative to the table of data. Would you mind taking a look again. Thanks...

Basically the query is wanting to look for the date range in the start date area and also the end date range. But if one of them is null then it won't work for that one so putting the same input on an or line for each of them individually is what I did.

So, what do you mean that you don't get all of the data relative to the table of data?

works the same as previous releases why not use a Form for the Start and End dates to save the multiple entry requests?

Brian - there are only two requests that come up - one for the start date and one for the end date. Using the same exact parameter inputs in each of them means that Access will not do it more than once for each of the parameters.

FHS - I'll take a look again at the uploaded sample.

 

[boblarson]

FHS - I'll take a look again at the uploaded sample.

I just looked and it is returning the correct values that I can see. Perhaps you had better explain what you EXPECT to see when you enter certain values in.

 

[fhs]

If I were to input in the Start Date parameter "12/6/2010" and "12/15/2010" in the End Date parameter, I should get all of the dates that exist now in the table. That isn't happening now. In the future when I input a two-week range of Start and End dates, I should get all of the ClockIn and ClockOut records for that two-week period. Run the report to see what I mean. What should the query do to get that data? Thanks.

 

[boblarson]

Okay, I see what is happening. The date of 1/1/1900 is interfering with it. So, I made a change to the criteria. Just watch out. The Start Date criteria is slightly different than the end date criteria.

Here's your copy back to look at.

 

[boblarson]

Never mind - that did NOT work. I'll keep looking.

 

[boblarson]

I have not been able to come up with a solution. What I originally had SEEMED like a good idea at the time, but it just isn't working. The key is that you need to keep from having nulls in your fields and then everything should work great.

 

[fhs]

Well, I sure appreciate you being so diligent about this puzzle. Initially, the requirement was that the employees only needed to click one field (ClockIn if they came in early or ClockOut is they stayed late). Anyway, if you do stumble upon an idea about a solution, please let me know. Thanks for all your help. By the way, is this the only way to get in touch with you?

 

[boblarson]

Well, I sure appreciate you being so diligent about this puzzle. Initially, the requirement was that the employees only needed to click one field (ClockIn if they came in early or ClockOut is they stayed late). Anyway, if you do stumble upon an idea about a solution, please let me know.

I just thought of a way. It would be to take the other field as the date value for both sides if one side is null. I'll try to put it into the sample.

Thanks for all your help. By the way, is this the only way to get in touch with you?

Yes, that's about it. For anything other than my own tools, samples, and code snippets, I don't answer email. So, since I'm here most days, this is the best.

 

[boblarson]

Oh, I think I finally did do it. The key was making sure the parameter prompt value was explicitly converted to a date.

See the attached sample.

 

[fhs]

It appears that this solution will work. I'll keep you informed. Again, thanks a lot.

 

[vbaInet]

I'm not sure if an explicit cast to type Date is needed. What is the objective? For it to pull all records when either one of the parameters is Null? I don't get it

 

[boblarson]

I'm not sure if an explicit cast to type Date is needed.

Yes, it is in this case. Not sure why but it was not working when I left CDate out of the equation. It wasn't pulling the right records. If you go into the sample and then - run the query and put in

12/6/2010
12/15/2010

for the parameters it will return 3 records when it should have a lot more. But if you wrap the [Enter Start Date] and [Enter End Date] parameter prompts in CDATE it returns the right records.
[/quote]
What is the objective? For it to pull all records when either one of the parameters is Null? I don't get it[/quote]

to pull records from EITHER the start date or end date where either one fits into the date range.

 

[vbaInet]

Not sure why but it was not working when I left CDate out of the equation.

That had to do with the DateValue() indirect cast. Once removed, works fine without CDate().

to pull records from EITHER the start date or end date where either one fits into the date range.

It seems the OP isn't trapping for Null cases, i.e. where the Start Date could be Null for example.

 

[boblarson]

It seems the OP isn't trapping for Null cases, i.e. where the Start Date could be Null for example.

Hence why the check for null in the creation of the datevalue field (using datevalue which removes the time element) and if it is null, it uses the OTHER field to handle the nulls.

 

[boblarson]

That had to do with the DateValue() indirect cast. Once removed, works fine without CDate().

So if you removed the DateValue function, then what did you do to turn it to a DATE ONLY field so that you could not use the time elements?