Query w/selection

[AC5FF]

I've got a query; works great as written; but I need to be able to have users choose which month to pull data for.
Right now; I pull the current month data using:

Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)

Is there a way I can rewrite this but use a user-selectable date for the month? I'm thinking it could be done w/two queries; but not sure about just one....

Thx

 

[raskew]

Copy/paste this to criteria cell of your date field.

Between DateSerial(Year(Date()),[Enter month 1-12],1) And DateSerial(Year(Date()),[Enter month 1-12]+1,0)

Bob

 

[AC5FF]

I'll give that a shot.
Is it possible; just to eliminate questions; for the user to enter "March" "Nov" etc?

Just thinking about this: Your method will work (I'm assuming, not tried yet ) However, there are 4 or 5 criteria in this report; all based off date; but I've been unable to build this without doing multiple queries. So...

If I went your direction, inserting [Enter Month 1-12] into my criteria; the user will have to enter that number 4 or 5 times... I'll need to find a way for this to only be asked once, stored as a variable somewhere and entered into the criteria that way..... At least that's my thoughts. That is why I forsaw needing to use a minimum of 2 queries....

 

[raskew]

Hi -

The user will be prompted with [Enter Month 1-12] only once. The input will be saved and used wherever that prompt appears.

Bob

 

[AC5FF]

Really? I'll definatly give this a shot this morning.
How about this (something I'm going to try, but..)
If Query1 prompts the user to enter month; and query1 pulls data from query2 that uses the same formula; will this value continue on to the next query?

If this is the case; wow.... Gonna make my job a LOT easier!

 

[pbaldy]

I think you'll get one prompt per query. I'd have the user enter their choice on a form, and have all the queries point to that. I think you're also going to want to choose a year. Relying on the current year is probably fine in mid year, but in January when you want to run reports for December, that won't work.

 

[AC5FF]

Good point; and i agree.
Reference the above "Between" statement... This might be getting a little more in-depth than I need to go; but my Quota table has a shortdate field. My thought process says to link that to that field on my form; have a user choose from that list; and somehow then incorporate this into the "Between" statement. I'm thinking... This field (QDate) as a shortdate; I should be able to do "Between Date(qdate) and Date(Qdate)+31" I think..

I need another weekend!!! LOL.. I got a lot accomplished working on this from home over this past weekend.... Another 2 days and I'd probably be able to figure all this out too. I just get distracted everytime I get into a groove here at work

But Heck; I'm getting ahead of myself! LoL.. I still need to figure out how to create a form to ENTER data into the table! LoL...

 

[pbaldy]

Well, you can simply use that instead of Date() in the original Between, and it should use that month.

I know all about getting distracted. I think I remember what a groove is, but it's been so long since I got in one I'm not sure.

 

[AC5FF]

Okay;
Seems to be semi-working... I created a new form; chose "List Box" to point to the Qmonth field from my table. Storing that value for future use.

I think I remember seeing a video on this problem a long time back; but not sure where to even start look for it again.

When I open the form I get ALL the dates stored in the QMonth field. I.E. For March there are 6 entries w/ 1 March 08, April has 9 entries w/ 1 Apr 08, etc... Isn't there a way to limit the list box values to no-duplicates?

 

[pbaldy]

SELECT DISTINCT Qmonth FROM TableName

 

[AC5FF]

Paul; took me a few to find it; but I did.. Way cool

Here is a copy of the SQL statement from my list box:

SELECT DISTINCT [Enterprise Repair Collaboration New Format].Month AS Qmonth
FROM [Enterprise Repair Collaboration New Format]
ORDER BY [Enterprise Repair Collaboration New Format].Month;

However, when I put the QMonth in either my report (I want to title the report w/the month chosen) or my query (Between Date(QMonth).....) Access pops up a window asking me for a value for QMonth.... Do I need to bracket [] or quote " " this? (Tried brackets and got the same...)

BTW: I have realized after the fact that "Month" is one of those 'reserved words'... however, all has been working as needed, so i've not gone back into my table to rename the fields..

 

[pbaldy]

The query doesn't know what/where QMonth is unless you tell it. Since it's being selected on a form try:

Between DateSerial(Year(Forms!FormName.ControlName),Month(Forms!FormName.ControlName),1) And DateSerial(Year(Forms!FormName.ControlName),Month(Forms!FormName.ControlName)+1,0)

 

[AC5FF]

Whew! After numerous trial/errors I think it's working! At least the report side

The SQL or Query that the combo box pulls up was QMonth as ......
But if I referenced Forms!ReportCard.Qmonth (or Month) it never worked. I had to reference 'combo31' ... wierd (at least sounds wierd to me! LOL).....

Thanks again for all the help! If it worked here, it should work the same when I toss this value into the "Between" dates AWSOME DEAL!

 

[AC5FF]

Paul;
After this help and the help provide on Table Setup...
Where do I vote for 2008 MVP???

THANK YOU!!!!

 

[pbaldy]

LOL. You just did, if the powers-that-be at Microsoft happen to view this thread. I'm glad to have helped out.

 

[AC5FF]

I did?? What I do?? I'll do it again! LOL

Rewriting the query to include the date(Forms!......) it doesnt like though.
Posted here:
http://www.access-programmers.co.uk/forums/showthread.php?t=146647

 

[AC5FF]

SELECT DISTINCT Qmonth FROM TableName

For this combo box, I will get just the single entries now; but this defaults to a blank entry. If a user presses the 'entry' button w/this field blank... well error/crash.. LoL
Is there a way to default it to the current month or ??

 

[pbaldy]

One way would be to make sure they've selected something before executing the rest of the code behind that button, but try this as the default value for that combo:

=[ComboName].[ItemData](0)

That should select the first item. If you sort descending, it will be the most recent date.