Modules & VBA :: Month And Week Criteria In A Module
Sep 23, 2013
I am trying to return a value in an expression (call it FundedPeriod): CurrentWeek, CurrentMonth, PreviousMonth, based on a date value in field [funded_date].
Here are the criteria I am using:
Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())
Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())
Based on example, I expanded upon that and came up with this:
Code:
Function FundedPeriod(FD)
' returns CurrentWeek or CurrentMonth or PreviousMonth or None based on FundedDate (FD) criteria
Dim ret As Boolean
ret=CurrentWeek
[Code] ....
Am I in the ballpark w/ this in order to return:
CurrentWeek or CurrentMonth or PreviousMonth
so in the query expression I think I would type FundedPeriodName: FundedPeriod([funded_date])
I have found multiple ways of calculating the week of a month for a given date. Now, I want to reverse it, i.e. given a month and week and day of week calculate the date.
Note that in week 1 and last week, there will often be days with no value.
I need a formula that will return the nth full week of the month that specified date falls in.
Example: 03/31/2006 would be "5" - the fifth full week of March 20/13/2006 would be "2 - the second full week of February
I've tried using a formula that subtracts the week number for the 1st of the month from the week number the date falls into, but it's just not working as I expect it to.
I would like to ask for help on my current problem.
I need to input a date on a text box and another text box say txtweeknumber should determine the week number the date falls on the current month.
for example:
May 1, 2005 should fall on week 1 and txtboxweeknumber should say Week 1 May 9, 2005 should fall on week 2 and txtboxweeknumber should say Week 2 May 17, 2005 should fall on week 3 and txtboxweeknumber should say Week 3 May 25, 2005 should fall on week 4 and txtboxweeknumber should say Week 4 May 30, 2005 should fall on week 5 and txtboxweeknumber should say Week 5
Same goes for all months. Is there a function to determine which week number of the current month the input date falls?
Help!! I have 2 fields in a table one for a year and one for a week number that data is being collected against. I need to change these to a month and year for the report. Is there a way to do this??
I'm planning to create a form that would show total number of sales transaction per week: If today's month is February, i would like users to see records like this: (Week should start on a Monday and ends on Saturday)
Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.
Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.
I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made. Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.
I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:
It somhow need to be check what the date is today and then caclulate back.
I have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"
I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.
Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:
This month we did 250 units Last month we did 300 units so we did 15% less "Yes my math is not exact"
They don't need to see last months data but i need a way to query the previous record to compare the data
This is the criteria code i use in the query that "Form B" is based on.
Code: [Forms]![Form A]![Report_Start_Date]
I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.
How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.
Want to use criteria in a date field to query data a week at a time. Open the query and have it ask for week starting MM/DD. Date input will normally use the Monday of each week and expect data back for Monday through Sunday
I'm a little stuck on three of my nine queries. Bascially, three of them display records for a given day, three for a given week and three for a given month. The criteria for days and months was pretty easy; I just got:
[Enter Date] for a single date DatePart("m", [Date]) = [Month] for a given month
However, I'm really stumped on how to work out the criteria needed for working out records for a given week. All I managed to work out was:
<=[Week Ending] And >"Date[Week Ending]-7"
This works when there is no test data, but not when there is. I know it's something to do with the -7 bit, but I can't work out what.
I have a database that lists jobs that need doing, in the jobs table there is a Deadline field, this is a Date Time field that specifies when a job must be finished by.
So I'm trying to make a query, using my limited knowledge, that displays all jobs that need finishing between todays date and the rest of the week.
I'm wondering how other members here make decisions whether they want to place codes behind form or use a standard module instead.
I understand there is a performance penalty when you add another module (and use it), but am not sure whether one big fat module would be faster than several smaller modules with identical coding.
Furthermore, I know that some members use a hidden form to deal with startup and shutdown processing. Sometimes the processing has nothing to do with forms and would make more sense in a standard module, but since the form is already loaded, does it makes more sense to use the module behind the form than calling a function in a separate standard module to execute the needed code?
I have a small system that consists of a back end data base and 2 front ends. having 2 front ends was a historic reason but they cover 2 distinct processes in the system.
I now have the need for using the same module(s) in both front ends. Each has a copy which is fine if nothing changes but when it does I have to apply the changes made (or copy over in its entirety) from one front end to the other.
Although this is no big deal I can forsee it slipping through the net.
Can 2 front ends access the SAME module and if so how?
I have this code below that is working however the calculation are updating on my form late.
Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.
The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.
Control
Code: Call GeraAuditCalc DoCmd.RefreshRecord
Function
Code: Function GeraAuditCalc() Dim fHrs As Double Dim Ttl As Double Dim Ttl1 As Double Dim Ttl2 As Double
I am trying to pass a boolean variable to a class module
Code: Set rps.ViewS = View
the code in the module that this in theory is calling reads as
Code:
Private ViewC As Boolean
Public Property Set ViewS(ByRef ViewA As Boolean) Set ViewC = ViewA End Property Public Property Get ViewS() As Boolean Set ViewS = ViewC End Property
However I am getting the error message
Quote:Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.
This module is giving me the "undefined function" error message when I try to run my query. I don't know why, but I have checked that there are no references with "missing" and there are not. I also added the word "Public" to the function becasue that was advised by another forum user. I thought it worked perfectly the first time I ran this query, but now it is not working and I do not recall making any changes. I have called the module basFunctions:
Option Compare Database '************************************************* ********* 'Declarations section of the module '************************************************* ********* Option Explicit '================================================= ========= ' The DateAddW() function provides a workday substitute ' for DateAdd("w", number, date). This function performs ' error checking and ignores fractional Interval values. '================================================= ========= Public Function DateAddW(ByVal TheDate, ByVal Interval) Dim Weeks As Long, OddDays As Long, Temp As String
I have a back end database that a number of people are working with using an Excel add-in that imports and edits records saved in the backend. Occasionally, when a user will update a record at first it saves to the backend but then an hour or two later, the changes made disappear. We have validated that after the user saves the changes, those changes are present in the backend of the database, but for some reason those changes disappear after a period of time.
Can't find any way to declare variables in the sort of module that Access uses by default.Having changed the default, I now get 'Option Compare Database' as the first line (previously nothing was there). Adding anything at all in that section, or changing it to 'Option Explicit' has the consistent effect that all the procedure names in the module are not recognised and nothing works.
Variables declared in procedures work only in the procedure even if declared as Public. Can't get a Static variable to work in more than one procedure. However many variables I declare, there are never any in the Declarations list at the top of the Code Window.
I am trying to create a customizable report that would allow the users to choose fields. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.
The error reads: Run-time error 2465: Application-defined or object-defined error
Could it be something as simple as an incorrect reference? I have checked multiple times, but I am stumped.
Code: Option Compare Database Option Explicit Sub MakeReport()
[Code].....
This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.
