[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Problem with report. Data not appearing when asking for specific date range

I have a form called frmEarnings with 2 text fields txtStart and txtEnd

The user is forced to specify a date range to retrieve data from the database however I have an anomaly that I can not explain.

In the report query I have a field called sl_date and have the following for my criteria:

>=[Forms]![frmEarnings]![txtStart] And <=[Forms]![frmEarnings]![txtEnd]

If the user enters the same start date and end date, for example wants daily sales, it just gives a blank report.

Why is that? The formula above makes sense to me logically but it does not work.

Suggestions?

Thanks

PBLack
0
PBLack
Asked:
PBLack
  • 11
  • 9
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this:


Between [Forms]![frmEarnings]![txtStart] And [Forms]![frmEarnings]![txtEnd]
0
 
PBLackAuthor Commented:
Does not work if it is the same day. I'm still getting a get a blank report.

I am entering Oct-20-11 for both txtStart and txtEnd and there are two records dated Oct-20-11 in the db and the report appears blank.

This has been bugging me now for about 3 hours and I can't figure it out :(
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Ok... try this:


Between CDate([Forms]![frmEarnings]![txtStart]) And CDate([Forms]![frmEarnings]![txtEnd])

and assumed you do ... have data that matches the same day scenario.  However, does the date field you have this criteria on include Time ?

mx
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
IF ... yourDate field includes Time, then try this: (Example)


SELECT Table1.*
FROM Table1
WHERE (((Int([YourDate])) Between Int([Forms]![frmEarnings]![txtStart]) And Int([Forms]![frmEarnings]![txtEnd])));
0
 
PBLackAuthor Commented:
No time is not included.
0
 
PBLackAuthor Commented:
I tried it with CDate and still nothing. :(
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Does not ... on the same day work ?

Can you upload the db?

mx
0
 
PBLackAuthor Commented:
Nope does not work.

Sure hold on I will upload it
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Tell me exactly how to reproduce the problem ...

mx
0
 
PBLackAuthor Commented:
Here is the db. Because I signed a non-disclosure with my client, I had to remove some stuff. I hope I got it all, while keeping everything you need to see what is going on.

Thanks for the help!
problemchild.accdb
0
 
PBLackAuthor Commented:
All that you have to do is open frmEarnings and click the print button. Leave the two optional combos blank.

Look at the data in tblSlip. There is two records dated October 20th yet they fail to appear in my report unless I changed txtEnd to October 21. I don't get that.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... standby ...
0
 
PBLackAuthor Commented:
Roger!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... your two dates in the data DO have times ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So, if you use the example I posted above it works:

SELECT Table1.*
FROM Table1
WHERE (((Int([YourDate])) Between Int([Forms]![frmEarnings]![txtStart]) And Int([Forms]![frmEarnings]![txtEnd])));

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The attached is working for me for  10-20-2011 to 10-20-2011

Note ... I changed acNormal to acPreview in a few places initially.

See the Report RecordSource SQL for the change using the INT function above, which strips out just the Date part ... and does not blow up if the date is Null.

I'm off to workout ...

mx
problemchild-MX01.zip
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
If you open tblSlip ... and actually click into a sl_date cell ... you will see the Time part.  The reason you don't see it when you just open the table is because of the Long Date format ... remove that and you will see the Time in datasheet view.

mx
0
 
PBLackAuthor Commented:
Yep you are right. That works. Thanks for the help. Much appreciated!
0
 
PBLackAuthor Commented:
Above and beyond the call of duty. A great community member!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ...

thx.mx
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now