Help with date procedure

[smitty27629]

From the switch board, the user clicks on the Create Monthly/Quarterly report. This opens a form where they enter the begining month, ending month, begining quarter, and ending quarter. After they type that in they click continue which initializes a macro that runs several queries based on the entered dates then opens a form displaying the results.

What I would like to do is, after the user clicks Monthly/Quarterly report, they would choose a month from a combo box, and type a year. From that form, four variables would get populated (beginMo, endMo, beginQtr, endQtr). Then those variables would get passed to the macro for further processing.

I think I have figured out how to get those variables populated using a switch case, but I'm not sure how to pass them to the macro. Or maybe, I'm barking up the wrong tree. Is there a better or easier way to do this? If I need to provide more info, please let me know. I can also provide the relevant forms and macro from my app if need be. Thank you for any help you can provide.

 

[DES]

Lots of picking and choosing going on there Smitty.

What i usually do is to create a box for the start date and for the end date - Actually I usually use a Date/Time Picker to get these dates.

From there I create a query uses those to values to filter out anything not within those dates.

Then every report I need to make I just make sure that the original date filtering query is part of the query for the report so that it only shows values for the specified period.

Now as in most cases this means very little to you so if you would like a sample of this process just drop me a line so I know where to send it.

technoweenie@sympatico.ca

DES

 

[smitty27629]

Des,

I'm not sure I understand what you mean by a lot of picking and choosing. Currently, the user is having to type in 4 dates. For example they want to see the Monthly and Quarterly report for January. When they click on the button to create the report, they are presented with a form that has 4 textboxes in it. This is where they would enter (01/01/05, 01/31/05, 01/01/05, 03/31/05). What I would like to do is present them with a combo box that has a list of months, and one textbox that they would type in the year.

From that I would populate, var beginMo (01/01/05), var endMo (01/31/05), var beginQtr (01/01/05), and var endQtr (03/31/05). Then I would like to pass those variables to the macro that runs the relevant queries. All I'm trying to do is make it easier on the user, so they don't have to try to figure how many days in that month or what are the beginning and ending dates for 2nd Qtr, etc...

Maybe I'm not understanding your post, sleep deprevation will do that to you . Thanks.

 

[Mile-O]

What other forum have I seen this question on?

 

[RV]

Rebuild your query so it uses the selected month plus the year you typed in as criteria.
Use the DatePart function within the WHERE clause(s) for your query/queries.

RV

 

[smitty27629]

SJ McAbney,

Where was this posted before? I may be able to get info from it.

RV,

I changed one of my queries to use the DatePart in the criteria field of my query but I'm not getting any results. No errors, no data, nothing.

In the table that I'm querying, the [shipdate] field is 01/17/04. The form that the user sees is a combo box with January, February, MArch, etc... in the field list. and a text box where they would enter 04. In my query, the Field: is [ladingtab]![shipdate] and my criteria is DatePart("m",[Forms]![getDates]![Month]) and DatePart("yyyy",[Forms]![getDates]![Year]).

Am I missing something?

After some more tweaking, Now I'm getting a Data Type Mismatch.

 

[smitty27629]

Does anyone have any info at all? Please. Thanks.

 

[RV]

Seems that you can't use DatePart in this case.
Switch to the Format function.
Use it on your shipdate column, NOT on your form fields (as you did as for the DatePart function):

Format([ladingtab]![shipdate],"mmmm") = [Forms]![getDates]![Month])
AND Format([ladingtab]![shipdate],"yy") = [Forms]![getDates]![Year])

RV

 

[smitty27629]

RV,

Your a lifesaver Mate. I owe you a pint or two.

I took the quarterly queries out of my macro to test the monthly queries with the info you gave me and it works great. One other question, How do I do the same thing for the quarterly queries?

Again, the combo box will have January, February, etc... and I need to find the matching quarter in my [ladingtab]![shipdate] field which will hold a date like this: 01/17/04. Thanks again.

 

[smitty27629]

Does anyone have any suggestions for the quarter format in the query in post #9? I'm not very good with VBA obviously, so I don't know the available attributes for the DatePart and Format functions. Maybe I need to do something different in order to get the Quarterly query to work. I just don't know. Any info would be appreciated. Thanks.

 

[raskew]

This query, meant to be tested against Northwind's Orders table, will return just those records occuring in the specified mm/yyyy:

PARAMETERS [enter mm/yyyy] Text;
SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate) Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1));

Modify it to incorporate your table and field names. When prompted enter something like: 01/2005

Should return all records from Jan 05.

HTH - Bob