MS Access - creating a query for monthly data

P

pips1520

Hope somebody can help me!!

I am trying to create a query that displays all submissions o
applications for the past month and am finding entering the righ
criteria very difficult indeed!!

I want to be able to run the query, and it will show me the data fo
the past month.

Thankyou
 
G

Gary Walter

Hi pips,

SELECT * FROM yourtable
WHERE
yourdatefield >= DateSerial(Year(Date),Month(Date)-1,1)
AND
yourdatefield < DateSerial(Year(Date),Month(Date),1)

good luck,

gary
 
P

pips1520

Thankyou for the suggestion, but unfortunately no luck!!

Apparently I have entered an invalid syntax expression. Any othe
suggestions? (Please!!!)

Pip
 
G

Gary Walter

Thankyou for the suggestion, but unfortunately no luck!!

Apparently I have entered an invalid syntax expression. Any other
suggestions? (Please!!!)

Pips
Click to expand...
Hi Pips,

In query design grid, under your date field
in the Criteria row, type in (this is all one line, but may word wrap)
 
P

pips1520

Hello gary,

Again, no luck im afraid. Possibly there is a problem with th
database itself? I know very very little about these thing
(hence.......im here).

thanks for your help anyway!

pip
 
P

pips1520

I dont think my problem lies with the criteria, as the query will ru
but no data appears. And i have absolutely no idea why!

If anyone can help, it would be much appriciated :0)

pips152
 
G

Gary Walter

Again, no luck im afraid. Possibly there is a problem with the
database itself? I know very very little about these things
(hence.......im here).
Click to expand...
Hi Pips,

Do you know how to tell if
the data type for your date field
is type "Date/Time"

Open the table and design view
and in the second column next
to your date field, does it show

Date/Time

??????

If it does, then please post the
SQL you have so far.

If it shows

Text

then the preceding advice won't work
and why are you storing Date/Time
as Text?

good luck,

gary
 
P

pips1520

It does indeed show Date/Time, i made sure of that any way. As for th
SQL:

SELECT Applications.Surname, Applications.Forename
Applications.[Policy Type], Applications.Company, Applications.Adviser
Applications.[Commission Due], Applications.[ETA Commission]
Applications.[Submission Date]
FROM Applications;

I think this is what you asked for! (Did the whole "SQL View" thing
then copied/pasted).

many thanks!

Pip
 
P

pips1520

By the way, as you can see Im not exactly familiar with all this or goo
at it, so please bear with me if what i say or ask seems rather obviou
or stupid. :0)

pip
 
J

JR

Assuming your dates are stored as dates, and that you want the preceding
month (30 days) from the current date you can try:

SELECT a.ApplicationDate
FROM tblApplications a
WHERE (((a.ApplicationDate)>DateAdd("m",-1,Date())))


JR
 
P

pips1520

Thanks for that advice.

Now when i run the query it asks for a parameter value - the submissio
date, and even if i enter one the query comes up blank. I want my quer
to run so that it automatically shows the last 30 days without an
parameters etc.
thanks again JR. you too gary!

if anyone else has any suggestions i would very muck like to hear the
- it sounded quite straightforward and now it simply infuriates me!!

pip
 
J

JR

My connection is a bit slow tonight so I didn't see your SQL before I posted
my last.

SELECT Applications.Surname, Applications.Forename,
Applications.[Policy Type], Applications.Company, Applications.Adviser,
Applications.[Commission Due], Applications.[ETA Commission],
Applications.[Submission Date]
FROM Applications
WHERE (((Applications.[Submission Date])>=DateAdd("m",-1,Date())));

Here you go. This time using your SQL.

JR
 
P

pips1520

ok, thanks very much for the help but did all that and yet when i run i
my query still comes up blank.

??????

completely at a loss at what to do. Any suggestions?............

pip
 
J

JR

Sorry, no. Is it possible that you don't have any records meeting the
criterion within the month preceding Date()?

I have tested the SQL and it works for me.

JR
 
P

pips1520

There is something odd going on for sure, as the records meet th
criterion ( there are over 50 submissions for the past 30 days, s
surely these should appear?) and the query will run, just not bring u
any data. It does however bring up the records without any criteri
entered, so I know it still has the function to do so!!

Thanks for the advice & help, albeit in vain!!

If there are any more suggestions, i would be much obliged to hea
them.

pips152
 
G

Gary Walter

SELECT Applications.Surname, Applications.Forename,
Applications.[Policy Type], Applications.Company, Applications.Adviser,
Applications.[Commission Due], Applications.[ETA Commission],
Applications.[Submission Date]
FROM Applications
WHERE
Applications.[Submission Date] >= DateSerial(Year(Date),Month(Date)-1,1)
AND
Applications.[Submission Date] < DateSerial(Year(Date),Month(Date),1);
 
P

PC Datasheet

Create a query based on your submissions table. You need to set the criteria
for year and month to get the previous month. If the current month is
February to December, the year of the previousmonth is the same as the year
of the current month. However, if the current month is January, the year of
the previous month is not the current yesr but the previous year. Put the
following expression in the first blank field of your query:
PreviousSubmissionYear:Year([SubmissionDate]) and put the following criteria
in that field:
IIF(Month(Date()) = 1, Year(Date())-1,Year(Date()))
Be sure to get the parantheses right!!!
Put the following expression in the second blank field of your query:
PreviousSubmissionMonth:Month([SubmissionDate]) and put the following
criteria in that field:
IIF(Month(Date()) = 1, 12, Month(Date())-1)
Be sure to get the parantheses right!!!
 
P

pips1520

Again, no luck. We are calling in the experts now!!

Thanks for all the help, i will be sure to use this site in time o
need!!

Pips152
 
G

Gary Walter

Only thing I can think of is
I forgot "()" after Date functions:

SELECT Applications.Surname, Applications.Forename,
Applications.[Policy Type], Applications.Company, Applications.Adviser,
Applications.[Commission Due], Applications.[ETA Commission],
Applications.[Submission Date]
FROM Applications
WHERE
Applications.[Submission Date] >= DateSerial(Year(Date()),Month(Date())-1,1)
AND
Applications.[Submission Date] < DateSerial(Year(Date()),Month(Date()),1);

In Immediate Window,
(type simultaneously <ALT> <F11>)
what do you get if you type

?DateSerial(Year(Date()),Month(Date())-1,1)

(then hit <ENTER. key)

then type

?DateSerial(Year(Date()),Month(Date()),1)

(then hit <ENTER. key)

This is what I get:

?DateSerial(Year(Date()),Month(Date())-1,1)
5/1/2005
?DateSerial(Year(Date()),Month(Date()),1)
6/1/2005

the first gives me the first day of last month

the second gives me the first day of current month

So the above query is same as

SELECT Applications.Surname, Applications.Forename,
Applications.[Policy Type], Applications.Company, Applications.Adviser,
Applications.[Commission Due], Applications.[ETA Commission],
Applications.[Submission Date]
FROM Applications
WHERE
Applications.[Submission Date] >= #5/1/2005#
AND
Applications.[Submission Date] < #6/1/2005#;

Which (I believe is what you wanted) should
give you all records whose Submission Date
(if it is a Date/Time data type)
occurs in the month of May 2005.

good luck,

gary
 
Top