brainstorm best way to select month of data

[cowboy]

I have a form f_Month that displays records from a query q_Month
When or before the form is opened I want to be able to select a month and only see the records from that month

Possible ways I have thought about doing this:
1. Call a form that has a combo box with possible months that will then call f_Month and set the filter for f_Month, but then I also have dsum() that uses the query, how would I filter the dsum?
2. Have the q_Month require a parameter to be entered and filter the query
3. Not sure how to do this but use a PROCEDURE at the beginning of the query and ask for a month and filter by that parameter

What is the cleanest way to do this, or do you have any other ideas?



thanks for the brainstorm

[DaveT]

I use a parameter query with criteria set as between first of month and last of month. Using functions put in a standard module, the query criteria (on date field) looks something like this:

Between FirstOfMonth([zDate]) And LastOfMonth([zDate])

zDate is the paramter and the query asks for this (or you can feed zDate from form, global variable, etc.) zDate can be any date in the target month.

Beyond this, I have use a custom calendar form, but that's another story.

Public Function FirstOfMonth(Optional dteDate As Date) As Date
' This function calculates the first day of a month, given a date.
' If no date is passed in, the function uses the current date.

If CLng(dteDate) = 0 Then
dteDate = Date
End If

' Find the first day of this month.
FirstOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)
End Function

Public Function LastOfMonth(Optional dteDate As Date) As Date
' This function calculates the last day of a month, given a date.
' If no date is passed in, the function uses the current date.

If CLng(dteDate) = 0 Then
dteDate = Date
End If

' Find the first day of the next month, then subtract one day.
LastOfMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - 1
End Function

[cowboy]

Ok so that worked well, currently from my form I have a

DoCmd.OpenQuery ("q_Month")

and then it asks me for zDate, I would like to be able to pass it the value of zDate when I call the query, I cant seem to find the syntax anywhere for that. Do you know?

Thanks for the help, the functions work great

[cowboy]

I think I figured out something that will work....

Function getRent()

Dim dsb As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs("q_Month_Totals")
  qdf.Parameters("zDate") = Forms!f_Year.[v_Month]

  Set rst = qdf.OpenRecordset()
    
  getRent = rst![SumOfqvRent]
End Function

Then on my form I run it in continuous view and I have a textbox on that form that the control source is set to =getRent() so it will call it for each record.
What do you think? I like it so far I will let you know if I come across any hiccups.

[DaveT]

Looks good (but functions in continous forms can hit performance). Personally I like to use globals, so I would do somethng like:

'put these in a standard module

Public gDteDate1 As Date
Public gDteDate2 As Date

'add these in standard module:

Public Function GetStartDate() As Date
On Error Resume Next
GetStartDate = gDteDate1
End Function

Public Function GetEndDate() As Date
On Error Resume Next
GetEndDate = gDteDate2
End Function

My query criteria then looks like:

Between GetStartDate() And GetEndDate()

where I have set gDteDate1 and gDteDate2 before opening the query.

If you are summing something across months (such as rent) then you can set up an aggregate query that returns the months you want (using Where on the Total line against a date field with the display box unchecked) and then make it all show in chrono order by doing something like:

Field in query:

YYMM: Format([MyTableDateField], "yyyy") & Format([MyTableDateField], "mm")

Group By
Ascending

Anyway, the idea would be to get everything you want per line in one query that becomes the recordsource for your form/report.

But there's always more than one way to do things and sometimes it's just personal preference. If your solution works, then that's the way to go.

[cowboy]

I know you get paid to work with access databases so dont spend more than 5 or 10 minutes looking at this, I know your time is valuable.

I know how to do straight up program everything I need to work but I am trying to figure out new ways to do things and try to take advantage of access the best as possible.

I attached 2 pictures to show very quickly everything that is going on. As you can see from the second picture I am using a continuous form and calling a function to fill in the information, do you have a better idea on how to do that?

I also want those blank buttons on the left to display the v_Month from its record source ex: Jan, 2010. The function method doesnt seem to work and writing a function that just does btnMonth.caption = ... didnt work either so I am not sure how to do that without going into hard code. Upon clicking the button I will display the months transactions which I can do easy.

Last, my action queries do a search on the memo field and if it says mcdonalds its category is set to food, I am still brain storming how I want to set the Unassigned category. The way I am leaning towards was on the form that shows all the months transactions it has a column for each category and have a toggle button for each option on the continuous form.

Any suggestions would be appreciated but dont spend but a few minutes looking at this, thanks much.

[DaveT]

Remember that a continous form is really x copies of one form. So if you put a command button on the form, it will show the same caption, x times.

Conditional formatting is another issue since it does allow you to change things, row-by-row based on some condition in that row. But that's another story.

Say you want a command button on each row and you want to show a value like a date where the command button is located. Put a text box on the form with the date from your form's recordsource. For the text box set Enabled = No, Locked = Yes

Drop a command button on top of the text box and set it to Transparent = Yes. While you're there (command button is highlighted in design view), click on Arrange, Bring to Front (to make sure command button is over/above the text box).

Now the user sees the date and when they click on it you get the On Click event for the command button.

This is about the same as not using a command button at alll, but using the OnClcik event of the text box to begin with. To skip the command button entirely, set the properties of the text box to Enabled = Yes Locked = Yes (User can click on the text box but cannot change the value).

If the text box is based on a date field, then you can format the text box to show whatever you want using the format property of the text box such as mmm yyyy.

As far as the continuous form with a function as the recordsource for several text boxes, this can work but can be slow.

To get the same effect, I sometimes use a temp table (say one row per day) that has all the fields I want on one row (essentially a de-nornalized view) and then process the table multiple times (VBA/DAO) until it has everything I need. That's some coding work, but it is a useful technique.

It looks like what you're doing can be done with crosstab (pivot table) queries, but that's a chapter of its own.

You can also use a bunch of individual queries to sum values (qryRent, qryUtilities, ...) and then tie them all together with left joins. That can work as long as you have a defined set of categories.

Making decisions based on the text in a memo field can be done, but this doesn't always work like we hope for (over time there have been some issues with Access and memo field text searches).

Anyway, you can use InStr in queries such as:

x: InStr([MemoField], "McDonald's")

If x > 0, then McDonald's was found, so you could use >0 in the criteria row of the query to see only McDonald's records.

You are much better off setting up a category table where category is a text field (primay key). Your data would consist of known categories (no misspelling issues, etc) and it becomes easier to manipulate the data.

That's not a complete answer, but just some suggestions.

[cowboy]

Thanks for the ideas, I like the transparent button idea.