[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Count records between two dates in Access in a report

Posted on 2009-04-02
15
Medium Priority
?
619 Views
Last Modified: 2013-11-28
Hi Experts,

I want to make a report in Access that counts the number of records in a specific table between two different dates. Date is a field in the table and it is in the date - format (i.e the calendar opens up when you click in the field). I am not very familiar to report making so probably I will need a detailed step by step list. The report should be accessed from a switchboard by clicking on a command button. Also, when the end - user is asked to define between which dates I would like the calendar - functionality to pop - up as in the table.

All help are highly appreciated.

Best regards,

Stayer
0
Comment
Question by:Stayer
  • 10
  • 5
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24050265
Detailed step by step?

Depends on how good you are in Access, this step-by-step could be 50 lines long.
Do you really need every single step listed?

How far have you gotten on this syetem yourself, so far?

I can give you the basic steps and a sample....

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24050560
Stayer,

Try this.

It is a simple report based on a simple query.
The only tricky thing is the form, and the code
Test it to see if it functions in the way you need.

If so, then please give it your best shot to replicate this in your database.

Post back here if you have any problems.

Please remember that you request requires some effort on your part as well.
Sound fair?

;-)

JeffCoachman

Access-EEQ24288336-CountDatesBet.mdb
0
 

Author Comment

by:Stayer
ID: 24058898
Hi boaq2000, and thank you for your reply.

When I try to open your attachement I get the warning message:

"Unrecognized database format......", please see screenshot.

How may I solve this?

rgds,

Stayer
screenshot.bmp
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24065377
Sorry, I had the DB open when I posted it.

(If you notice the file size said "0 Bytes"_

Here it is again.
Access-EEQ-24288336CountRecordsB.mdb
0
 

Author Comment

by:Stayer
ID: 24068552
Hi again,

now I can open the database :) however, when I input dates in the form I get an error message. I have attached a screenshot with the error message. Also, when I input dates in the form, the calendar functionality does not pop up (I have to input dates "manually"). I also needs this.

Please provide me further suggestions :)

Best regards,

Stayer
screenshot.bmp
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24069073
Is xx.xx.xxxx a standard date format?
If so, then for which country?

This will have to be converted.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24071000
Which chrachters are the months and which are the days?
0
 

Author Comment

by:Stayer
ID: 24076022
Hi.

The format is Scandinavian and is written this way: /day/month/year. For instance 01.03.2009 is 1st of march 2009. However, I would like the calendar to pop up when the cursoer highlights the start and end fields in the form (this way, is should not matter how you type it since you select in a calendar). It seems to me that the dates in the table have been converted to the scandinavian way (if the dates of march is the object to display).

Best regards,

Stayer
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24076264
OK,

working on new sample.

But if you enter a date in the US format:
mm/dd/yyyy
between 3/1/2009 and 3/31/2009 (March 2009)

You will see the results in the report.

let me know if this is what you want first.

;-)

JeffCoachman
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 24076330
Again creating a Pop-up calendar will require additional coding.

Please pear in mind that this system that you are requesting *requires* you to be familiar with VBA coding.

So a "Step-by-step" example may end up being an entire "Course" in basic VBA coding, depending on exactly how much help you need.
I can provide a working sample, and the instructions for it's use, but getting this work in your database will be ultimately up to you.

;-)

Let me know

JeffCoachman
0
 

Author Comment

by:Stayer
ID: 24076439
Hi,

when I wrote it in US format in the form it worked perfectly. However, I just need the sub total to be displayed and not list the entire record set. Is there a easy way to just view the total number of records?
If you could provide a working sample it would be great :) and I will give it my best shot.

Best regards,

Stayer
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24077330
No problem.

BTW, Access 2007 will dispaly a Pop-up calendar for textboxes automatically if it is bound to a table.
So I created a Date Range Table just for this.
It it only stores one set of dates at a time, and clears the table when the form is opened.

New enhanced sample.
;-)

JeffCoachman
Access-EEQ-24288336CountRecordsB.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24077367
I cannot change my regional settings to match your date format.

You will have to make whatever changes are needed to make this work in your date forrmat.

Let me know if you need help.
0
 

Author Closing Comment

by:Stayer
ID: 31565764
This is perfect!

Thanks once again for your help, boaq2000.

rgds,

Stayer
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24077781
;-)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question