Problem with Date Criteria 1

[kentwoodjean]

When it rains it poors.........and I have a problem with another database. I have never had this happen before and not sure how to fix it.

Have about 4 tables, where most of the information feeds into the major table. I have 3 date fields on my main table that are not repeated in the other tables (Recvd Date, Start Date, End Date). The input masks and formats are set for short dates. I have made different queries based on my main table (sums & counts are included in a few of them) and I have made the reports. It I try to put a criteria prompt of Between [Begin Date] and {End Date}, I get only 1 of the 2 prompts and it is not reading my dates. It does not seem to matter if I have all 3 date fields on 1 report/query or just one.

Any thoughts?

 

[ZmrAbdulla]

Hi,


<B>It I try to put a criteria prompt of Between [Begin Date] and {End Date},<B>

Are you using square brackets or curly brackets?

ZamrAbdulla

 

[kentwoodjean]

Yes, I also use the criteria prompt of &quot;Between&quot; and the square brackets. This just is a mystery to me.

 

[scriverb]

kentwoodjean: I think what ZmrAbdulla was referring to was that it looks like you have mistyped your criteria statement and used square brackets [ ] for the Begin Date and then you used INCORRECTLY curly brackets { } for the End Date. This is why you are only getting prompted for the first date.
YOUR POST:
Between [Begin Date] and {End Date}

Should be:
Between [Begin Date] and [End Date]

This should clear this up for you.

Bob Scriver

 

[kentwoodjean]

How very stupid of me. I cannot belive how many times I retyped the criteria on multiple reports and kept typing it without the square brackets on the &quot;end date&quot;. Not only that, I looked at it over and over again and continued to miss it. I must have been very tired. thanks so much for helping me open my eyes.

Correct typing makes everything right!

 

[scriverb]

Been there, done that. Enough said, Right???

Bob Scriver

 

[smithia6]

Hi I have just been reading over this artical and I have a problem related to this.

I have a macro that calles a query to print a day report, I have it set where it justs askes for a date, and it will print a report for this date.

In the query I have it set from the table in the date field [Enter Date] and it works fine, so I have now changed it to [Begin Date] and [End Date]it does ask me for the 2 dates and then prints a plank page.

What I am trying to set up is to print reports between 2 dates or a 5 working day report using the 1st and only date of week commencing and let it print a report from that date +5

Hope anyone can help me.

Regards

Ian

 

[scriverb]

The criteria for a date range should be:
Between [Begin Date] and [End Date]
or
>=[Begin Date] and <=[End Date]

If you wanted to use just the first date of the week and have the user only enter the Monday date you could use this:

Between [Begin Date] and ([Begin Date]+ 4)

I hope this helps you to understand the syntax for this type of criteria statement.

Bob Scriver

 

[smithia6]

Thanks Bob you have been very helpful.

I have it running now using : -
Between [Begin Date] And ([End Date])

I did try using Between [Begin Date] and ([Begin Date]+ 4)and got an error babbling on about it was maybe to complicated expression and to try simplifying it.

I am placing this string in my query table just under the date field, this is right ....isn't it?

Regards

Ian

 

[scriverb]

Sorry about the incorrect syntax before. This won will work with just the Monday of the week entry:

Between [Begin Date] And DateAdd(&quot;d&quot;,1,[Begin Date])

Let me know if I can be of more assistance.

Bob Scriver

 

[smithia6]

Magic!! I have it running sweet now, thanks Bob your a star.

Ian

 

[scriverb]

Glad to be of assistance. The only thing that you have to be care of is the user entering a valid Monday date. There are other ways of checking for that also. If you want to pursue them let me know.

Bob Scriver

 

[smithia6]

Can that be done????

 

[scriverb]

To do this we would have to create a small form that would popup and prompt for a date. Depending on how the user is using this report we could prompt for the most likely date that they would select. You would have to think about this but as an explample on any day during the week would they normally selecting the Monday of that week? If so we can prompt for that date as the default.

You see each report and prompt situation has its own set of rules and expected inputs. Sometimes a report is run only for previous time periods. Some are run for periods in the future. Just what this is run for will have to be left up to you to set the rules. But, after that we can figure out the best way to insure that the right dates are input by the user.

Let me know what the rules are for this report.

Bob Scriver

 

[scriverb]

As an example if the report was being run today and the user always runs the report for the weekdays of the current week then the following could be used in the query:
Between DateAdd(&quot;d&quot;,((DatePart(&quot;w&quot;,Date())-2)*-1),Date()) and DateAdd(&quot;d&quot;, 6 - DatePart(&quot;w&quot;, Date()), Date())

Waiting for your response to your needs.

Bob Scriver