Is it possible??

  • Thread starter Thread starter rblivewire
  • Start date Start date
R

rblivewire

Is there a way to set up a query so that when a user inputs a date, it
can show the user all the results from the past 0-1 month, 1-3 months,
3-6 months, 6-12 months, and 12+ months? Or even ask the user if they
want the query to show 0-1 month, 1-3 months, etc.??
 
rblivewire said:
Is there a way to set up a query so that when a user inputs a date, it
can show the user all the results from the past 0-1 month, 1-3 months,
3-6 months, 6-12 months, and 12+ months? Or even ask the user if they
want the query to show 0-1 month, 1-3 months, etc.??
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

PARAMETERS [What date?] Date, [How many months?] Byte;

SELECT
Switch(
DateDiff("m",date_column,[What date?]) BETWEEN 0 AND 1,"0-1 Month",
DateDiff("m",date_column,[What date?]) BETWEEN 2 AND 3,"2-3 Months",
DateDiff("m",date_column,[What date?]) BETWEEN 4 AND 6,"4-6 Months",
DateDiff("m",date_column,[What date?]) BETWEEN 7 AND 12,"7-12 Months",
DateDiff("m",date_column,[What date?]) > 12, "12+ Months"
) As Period,

SUM(sales) As TotSales,

<other columns>

FROM table_name

WHERE date_column BETWEEN DateAdd("m",-[How many months?],[What date?])
AND [What date?]

GROUP BY
Switch(
DateDiff("m",date_column,[What date?]) BETWEEN 0 AND 1,"0-1 Month",
DateDiff("m",date_column,[What date?]) BETWEEN 2 AND 3,"2-3 Months",
DateDiff("m",date_column,[What date?]) BETWEEN 4 AND 6,"4-6 Months",
DateDiff("m",date_column,[What date?]) BETWEEN 7 AND 12,"7-12 Months",
DateDiff("m",date_column,[What date?]) > 12, "12+ Months")

, < other non-aggregate columns >


See the VBA Help for more info on the Switch() and DateDiff() and
DateAdd() functions.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAODY4echKqOuFEgEQJlzQCgu94o6tR6q02Gdhs/anNx258ikSwAn02C
LWJ6BsK+tmbtrX/GHOnZla39
=FN1B
-----END PGP SIGNATURE-----
 
It would be much simpler to ask the user for a RangeStarttDate and a
RangeEndDate. You could then use the same syntax for all your conditions. It
would look like:
Between RangeStartDate And RangeEndDate

In fact, if you could do it like this:
Between [ Enter Range Start Date] And [Enter Range End Date]
Access would then prompt the user to enter both dates.
 
"PC Datasheet" <[email protected]> schreef in bericht
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1125 users have come to me from the newsgroups requesting help '1125 users ????
(e-mail address removed)
Click to expand...

--
To Steve:
Over 415 users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R
 
Thanks MG.... should all of this go in the SQL view, including the
"parameters" and the "grouped by" ... Also, when a user inputs a date,
it will ask them how many months, then when they put in 1-3 months it
will give them the results?
 
rblivewire said:
Thanks MG.... should all of this go in the SQL view, including the
"parameters" and the "grouped by" ... Also, when a user inputs a date,
it will ask them how many months, then when they put in 1-3 months it
will give them the results?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, from the PARAMETERS line down to the info in the GROUP BY line. My
post was just an example so be sure to put in the names of your tables
and columns in place of the ones I used and remove anything that you
don't need. See the Access Help articles on SQL ("SQL Reference" in the
Table of Contents) for more info on SQL.

The user should put in one number for [How many months?]. IOW, if they
want to see 3 months back put in 3. If they want to see 12 months back,
put in 12, etc.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRASoWoechKqOuFEgEQJ9VACfQ4kFXaxHAHoNlEGK2qcMuRQ3VmEAoKUi
Wu2ISE9mqjt5OJlQJIDNk/7n
=bDwV
-----END PGP SIGNATURE-----
 
One more question.... If I wanted the user to only input the month and
year and keep the day constant at the first... how could I put this
into the solution up top. Basically I want to replace the "what date"
with the something like dateserial([specify year], [specify month],
1)... could I put this into your sloution?
 
rblivewire said:
One more question.... If I wanted the user to only input the month and
year and keep the day constant at the first... how could I put this
into the solution up top. Basically I want to replace the "what date"
with the something like dateserial([specify year], [specify month],
1)... could I put this into your sloution?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'd have to have 2 parameters: one for the year and one for the
month, as you indicated:

PARAMETERS [Year?] Integer, [Month?] Byte;

and use DateSerial() as you've indicated. Just substitute it whereever
I had the [What Date?] parameter.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAS294echKqOuFEgEQJKGwCfcnJadJO98pCuSUPNlAWzi9UJUc4An31H
oT0W7m4Z6+d609FcsQ6Inx3S
=jg9u
-----END PGP SIGNATURE-----
 
It seems like every time I put in the dateserial(), it comes back with
the error, wrong number of arguments. I try to put in
DateDiff("m",date_column,dateserial[specify year], [specify month],1)
BETWEEN 0 AND 1,"0-1 Month", and for all the rest going down to 12+ and
it gives me worng number of arguments. I even tried it with just
dateserial() and still gives me the same response. Any suggestions?
 
rblivewire said:
It seems like every time I put in the dateserial(), it comes back with
the error, wrong number of arguments. I try to put in
DateDiff("m",date_column,dateserial[specify year], [specify month],1)
BETWEEN 0 AND 1,"0-1 Month", and for all the rest going down to 12+ and
it gives me worng number of arguments. I even tried it with just
dateserial() and still gives me the same response. Any suggestions?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Correct syntax is:

DateDiff("m",date_column, dateserial([specify year], [specify month],1))

You didn't put in the beginning and ending parentheses of the
DateSerial() function.

If you're still having problems post the SQL for the query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAUj2IechKqOuFEgEQIVkQCdHTFStRlIWUTqjyrdprLkRfOZ8bEAn0pi
NslVUw89tRgraQKYSmIVEG17
=NLuZ
-----END PGP SIGNATURE-----
 
Back
Top