Calculate date time with access Results

Thanks, Anna, for your prompt response. Sorry if I appear dumb, but this is my first VBA day although I have two year’s experience in designing in Access 2000 the conventional way (tables, queries etc). I tried the code below incorporating your suggestion and it locked on the very first line. I want to get values (integers) from fields M1-M12 in Table “tB” and display them in TextBoxes “Jan – Dec”on a form. What is wrong with my code, please?

Unfortunately, all the books I’ve seen do not make it very clear which are reserved commands and which are just names. Also, do you need to Dim a value if the value type has already been declared in a table?

I need to bill monthly on within X period and within Y budget. Later I will do calculations on them with quite a bit of logic and itineration (carrying balances forward) and lots of date manipulation. THEN I need to get data OFF the Module and onto other tables or queries maybe.

By the way, the reason why I am plunged suddenly into VBA is that every time I try to do a lot of itineration combined with logic statements in a query I get the error “Query is too complex” (Error #3360) I’ve posted questions about this but never got a response. I’ve given up trying to use queries for this section of the database.

I wrote:-

Option Compare Database
Private Sub Form_Load()

Dim CurrentDb.TableDefs("tb").Fields("M1").Value as integer ‘January Input
Dim CurrentDb.TableDefs("tb").Fields("M2").Value as integer ‘February Input
Repeat until Field M12 incl.

For fc = 1 To 12
M(fc)= TextBox M(fc)

Next M(lc)

End Sub

i'm relatively advanced Access user but this problem has got me stumped....i've been given a table that's basically a log of operating rooms with fields designating the date, the room number, "patient in room" time and "patient out of room" time. basically, i want to calculate the turnaround time which is the "patient in room" time" minus the "patient out of room" time from the previous record (if records are sorted in sequential order).

any thoughts?

It's a Bed and Breakfast. There are two Access Tables in the query. No
spreadsheet. The Reservations table has RoomNumbers (some of which repeat because different customers rent the room during different time periods), CustomerNumbers, Check-inDate and Check-out Dates. The Rooms table has RoomNumber, FloorNumber, PricePerNight fields. The calculated field that I have developed to figure the money generated by each room is Total[Rooms].[PricePerNight])*([Reservation].[Check-outDate]-[Reservations].[Check-inDate]).

This makes my result look like:

Room Number Total
3 $960.00
7 $540.00
3 $480.00
1 $300.00
4 $280.00
4 $280.00
3 $240.00
2 $225.00
2 $150.00
4 $140.00
1 $ 75.00
6 $ 60.00

How do I use a Summation function to aggregate the amount of money brought in for each room, so that the result looks like

Room Number Total
3 $1,680.00
4 $700.00
7 $540.00
2 $375.00
1 $375.00
6 $60.00

Any help would be appreciated.

Thank you

Very new to Access and I have what I guess is a really, really simple problem but I just can't seem to see what I am doing wrong. I have broken my Database down to the most simplistic form to see if it helps but it doesn't. My Table now contains two fields: Date (Date/Time format) and Days (number format). I have put these into a query and calculated a third field: Latest Retest Date: [Date]+[Day]. I entered test data of 01/01/08, 01/02/08 and 01/03/08 with days set to 7 in each case. This works fine. However I then put a Paramater Query in Criteria line under Latest Retest Field of Between [First Date] And [Last Date]. If I run the query with First date 09/02/08 and last date 09/03/08 I get no results despite the third record showing a Latest Retest Date of 08/03/2008. However, if I run with a first date in advance of all three dates, 01/01/2008 for example and a last date beyond all three dates of 31/12/2008 for example, it shows all three records as expected. I guess it has to be a formatting error but I have spent hours looking at FAQs and MS Tutorials. It may simply be a case of not seeing the wood for the trees but it is SO frustrating I would appreciate any advice.

Thanks a lot


Hi (newbie here),

First time I've posted even through I have lurked for awhile. I downloaded Jon K "Query By Form Access 2000" which is what I really need, I have been unable to edit it so that I can do a search on a date using the Operators: < = etc on the sample form. Instead of calculated an "Age", I just want to search a Shipped Date using the operator.

I'm not totally clueless but close (used to do some programming in Basic about 100 years ago.)

Any way how can I change the iif statement in the query from the current:

IIf([Forms]![Main Form]![cboOperator] Is Null Or [Forms]![Main Form]![txtAge] Is Null,True,IIf([DateOfbirth] Is Null,Null,Eval(DateDiff("yyyy",[DateOfBirth],Date())+(Format(Date(),"mmdd")

Hello all,

My apologies in advance for such a long post, but I really want to make clear exactly what my problem is, I'm sure that there is a relatively simply solution to this...

We have an incredibly old SOP system which contains data regarding orders that are required for the running of our Access DBMS. To use the data in Access we download batches of data into CSV files and then twice daily append/update these records to a table in the Access file for use with the queries in there. This all works fine and has done for some months now. However, we have now got to the point where we need to analyse some of the data in monetary terms. The problem is that monetary values in the SOP data are stored as a currency format and the actual currency is stored in a separate field of the same table, which simply stores a string denoting the currency; there are three of these: GBP, EUR and USD.

The data that is to be reported needs to be summed and displayed in GBP, and so I am looking to convert the values to GBP in the Access table that stores the SOP data, so when the reports are run it is just a matter of summing the values, rather than slowing the queries down by performing the calculation in them every time that the report is run. Reports will always be run at least a week after the previous month has ended and so my plan is to create a table which stores the conversion rates, which would then be updated manually as soon as the new rates are available (usually around the second working day of the month). This table would contain the start and end date of the month and the conversion rate for EUR and USD.

Each record in the Access table has an invoice date associated with it and so I could then run an update query (in with the existing queries that are run twice daily) that for each record finds the appropriate date range and calculates the currency value to be GBP (by looking up the currency and then finding the conversion rate for that month). The result can then be stored in a new field (which defaults to zero), and this new value can be summed on the reports.

My question is this: what is the best way to structure this update query? I'm not sure how to go about it. It almost needs to iterate through each record in the Access table and for each record loop through the date ranges in the currency table until an exit criteria of >=Start Date AND

Hello All,

I have posted regarding issues on this project before but I think I am down to my final issue.

I am having problems with a calculation for a DVD rental system I am creating for my Friend.

I am currently creating the Rental form. When a customer returns a dvd I want to be able to search for their customer number (I plan to do this through generating and showing a query with date due back=0)

I will display a list of all customers with unreturned DVDs so that the user can check if the DVD is actually due. If it is,

I want the user to then be able to enter the relevant customerID into a text box and for the user to click a return DVD button.

On this button I want the duration to be calculated and saved in the Duration field- is the following correct?

Rentals.Duration= Datediff(“dd”,[rentals.Date Rented], [rentals.Date Due Back]

I also do not know where I should place the code, on the button when an update occurs?

Also I want the system to automatically work out the price for the rental including late fees. In this system I have chosen to have a standard rental duration of 3 days, charged at £1.50 a day and if the time is over that to charge each additional day at £2.50. I have come up with this code, again I am unsure if this would work;

Let N= rentals.Duration
Let P = rentals.Price

If N>= 4


P= (3*1.50) + [(N-3)*2.50)]


P= (N*1.5)

End if

I want this value to be displayed in a text box on the form and also saved in the rental table.

I have set up all other aspects of the system, Adding/Deleting records for DVDs and Customers etc.

I need help in finalising the actual code that I can put into Access 2003 and where exactly to put it.

I thank you in advance for your help


I am new to this and hope everything makes since.
I am working in access 03 and summing all jobs by quarter. The problem I am having is that some job sums need to be added to a different goalyear and the totals need to show in the 1st quarter of the next goalyear. For example, the sales manger has reached there sales goal for the 2008 year. So any sales that are still in 08 need to be added to the 1st quarter or 2009. It is known what sales are to be posted in 09 by a drop down called goalyear.
I have attached screen print to try a show what I am trying to say.


you can see in the screen print that 09 has qtr 3 and 4 with amounts. This is happening because the GoalYear has been selected as 09 and the dates the sales where entered were in the 3rd and 4th qtrs of 08.

my thinking is that i need the sales amount to be added to the 1st quarter of the assigned GoalYear.

Thanks for your time and assistance.

Hi everyone!

I am working on a Facilities Maintenance database, which allows users to create maintenance requests. The Facilities department views the logged maintenance requests, and creates a work order. This is 90% finished, but I have come to a problem that I can't find an answer to.

I need to prioritize the "Maintenance Request" list for the facilities department. When maintenance is requested, the following priority system is used.

Priority 1 = Urgent (Production has stopped - Over-runs any other priority level)
Priority 2 = High (Slows down production)
Priority 3 = Low (minor impact)

I have this working on a simple "ascending" scale query, but Management has asked for the "date raised" to be considered (allows for jobs that have been sitting in the queue for a longer time to have a higher priority). Good old bosses!!! 2 seconds to ask for a job that I have spent hours trying to fix!

I have this strategy that I want to implement (I have no idea how). I want to add a second field with a numerical value (priority score), which is possibly calculated by a query.
Priority 1 = 1000 (priority score)
Priority 2 = 20
Priority 3 = 10

I also want the date difference between now and the "request raised" date to be added into the equation. Eg, if the request was raised 5 days ago, then 5 is added to the priority score.

I can easily do this on excell, but unfortunately, Access won't accept the equation. Can someone please help me do this?

Hi all,

I've been struggling with this for a week before finally deciding I need help - big time!

I need to run a query to create a charges table, using the following to calcualte a current charge amount:


So in English this would work out something like this:

If the start date is less than the invoice date AND the EndDate is less than the BilledUpToDate, THEN Units * UnitCost, Pro-rated from:

(Either the StartDate or BilledUpToDate, whichever is most recent) to either the EndDate or the InvoiceDate (default if EndDate is NULL) whichever is first in time).

Anyone have any ideas as to how I'd even begin to go about achieving this?

I could and have done this in VBA for excel but getting frustrated without much knowledge of SQL / VBA in access for this task. It's really the method rather than the calculation I need help with. I've attached some data for testing examples with correct results.

Hi all

Been away from Access for a number of years so bear with me

I have been tasked with creating a simple Access T&A system. The company use time clocks whereby the data is polled automatically to a SQL database.

I can connect to the tables no problem and link to the data etc.

Each employee is to clock in or out depending upon whether it lunch time etc. so it might go something like this

Clock in first thing in the morning
Clock out for lunch
Clock back in after lunch
Clock hout for home time

Technically there should be a mimimum of four entries in the table for each employee for each day. (Obviously we know some people forget to clock in or out but thats not the issue at the moment.)

I have used the splitfield function to split the date and time field into their own fields using a query. Ive not added these to the main table yet but will want to do this 'on the fly'

My problem is I can't get my head round the time calculations between the four entries. These could also go over midnight just to add fuel to the fire.

Here is the sample data for one employee, obviously there will be more employees and this will need to be done over differing dates etc.

Any ideas would be great

Hi All

I am busy with a gynaecologist booking system for mums to be.

I need to have the following.
The date that they first visit.
How far along they are and every time when they come for an appointment I would need to know how many weeks they are at.
And lastly the due date or expected due date.

I googled for this information but just cant seem to find the right information.
Oh and I need to create it in 2007. My desktop however has 2010 on so hopefully it shouldnt be a problem when opening it or I can just view save it in the correct version

Many thanks

Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !

I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.

I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.

Suggestions please !


Private Sub cmdDate_Click()
Dim FirstDate As Date ' Declare variables.
Dim Number As Integer
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim lnginterimdate As Long
Dim intdays As Integer

Dim Msg
FirstDate = InputBox("Enter a date - dd/mm/yy")
Number = 20

dtinterimday = FirstDate
intdays = Number
Do Until intcount = Abs(intdays)

If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1

lnginterimdate = dtinterimday

If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then

intcount = intcount + 1
End If
End If


Msg = "New date: " & dtinterimday
MsgBox Msg
End Sub

hi, i downloaded a calendar sample from this forum..

the actual module that was provided was to add the chosen date to a field.. which is fine..

i have tried using this calendar on my main menu.. i just want it to display todays date.. thats all.. the problem is.. as time passes, the main menu calendar starts to display multiple dates.. like this..

to resolve this problem.. i copied the calendar form and re-pasted it with a different name..

so the calendar was originally called frmCalendar.. and i copied that form and called it 'newfrmcalendar', in the form design i tried to lock the form and its elements so it wouldnt change..

i then copied the calendar module and called it a different name.. basically trying to separte the 2.. it didnt work..

below is the calendar module..

	Option Compare Database   'Use database order for string comparisons
Option Explicit

Const CALENDAR_FORM = "frmCalendar"

Type udDateType
    wYear As Integer
    wMonth As Integer
    wDay As Integer
End Type

Private Function isFormLoaded(strFormName As String)
    isFormLoaded = SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, strFormName)
End Function

Function PopupCalendar(ctl As Control) As Variant
    ' This is the public entry point.
    ' If the passed in date is Null (as it will be if someone just
    ' opens the Calendar form raw), start on the current day.
    ' Otherwise, start with the date that is passed in.
    Dim frmCal As Form
    Dim varStartDate As Variant

    varStartDate = IIf(IsNull(ctl.Value), Date, ctl.Value)
    DoCmd.OpenForm CALENDAR_FORM, , , , , A_DIALOG, varStartDate

    ' You won't get here until the form is closed or hidden.
    ' If the form is still loaded, then get the final chosen date
    ' from the form.  If it isn't, return Null.
    If isFormLoaded(CALENDAR_FORM) Then
        Set frmCal = Forms(CALENDAR_FORM)
        ctl.Value = Format(DateSerial(frmCal!Year, frmCal!Month, frmCal!Day), "dd/mmm/yyyy")
        DoCmd.close A_FORM, CALENDAR_FORM
        Set frmCal = Nothing
    End If
End Function

below is the form code for the calendar.. how can i edit either of these so they will just display todays date.. i will then rename and use them..

form code

	Option Compare Database
Option Explicit

' Set the first displayed day of the week.  In the
' US, this is Sunday (1).  In other countries,
' use the appropriate number (1 == Sunday, 7 == Saturday).
'(c)2000, Maurice St-Cyr, Micro Systems Consultants, Inc.
' Ottawa, Ontario, Canada

Const FIRST_DAY = 1

' Color to show weekend days.

Const D_SUN = "Su"
Const D_MON = "Mo"
Const D_TUE = "Tu"
Const D_WED = "We"
Const D_THU = "Th"
Const D_FRI = "Fr"
Const D_SAT = "Sa"

Dim astrDays(1 To 7) As String

' The date passed in from the caller (possibly null)
Dim dtStartDate As udDateType

Dim intStartDOW As Integer

' Store away today's date.
Dim intYearToday As Integer
Dim intMonthToday As Integer
Dim intDayToday As Integer

Dim aintMonthLen(1 To 12) As Integer
Dim strSelected As String

' Constants used to control movement on the form.
' These constants match the interval values
' needed by DateAdd().
Const CHANGE_DAY = "d"
Const CHANGE_MONTH = "m"
Const CHANGE_YEAR = "yyyy"
Const CHANGE_WEEK = "ww"


' Constant month values.
Const M_JAN = 1
Const M_FEB = 2
Const M_MAR = 3
Const M_APR = 4
Const M_MAY = 5
Const M_JUN = 6
Const M_JUL = 7
Const M_AUG = 8
Const M_SEP = 9
Const M_OCT = 10
Const M_NOV = 11
Const M_DEC = 12

' Key Codes
Const KEY_CANCEL = &H3
Const KEY_MBUTTON = &H4    ' NOT contiguous with L & RBUTTON
Const KEY_BACK = &H8
Const KEY_TAB = &H9
Const KEY_SHIFT = &H10
Const KEY_CONTROL = &H11
Const KEY_MENU = &H12
Const KEY_PAUSE = &H13
Const KEY_CAPITAL = &H14
Const KEY_SPACE = &H20
Const KEY_PRIOR = &H21
Const KEY_NEXT = &H22
Const KEY_END = &H23
Const KEY_HOME = &H24
Const KEY_LEFT = &H25
Const KEY_UP = &H26
Const KEY_RIGHT = &H27
Const KEY_DOWN = &H28
Const KEY_SELECT = &H29
Const KEY_PRINT = &H2A
Const KEY_HELP = &H2F

' KEY_A thru KEY_Z are the same as their ASCII equivalents: 'A' thru 'Z'
' KEY_0 thru KEY_9 are the same as their ASCII equivalents: '0' thru '9'

Const KEY_NUMPAD0 = &H60
Const KEY_NUMPAD1 = &H61
Const KEY_NUMPAD2 = &H62
Const KEY_NUMPAD3 = &H63
Const KEY_NUMPAD4 = &H64
Const KEY_NUMPAD5 = &H65
Const KEY_NUMPAD6 = &H66
Const KEY_NUMPAD7 = &H67
Const KEY_NUMPAD8 = &H68
Const KEY_NUMPAD9 = &H69
Const KEY_ADD = &H6B
Const KEY_F1 = &H70
Const KEY_F2 = &H71
Const KEY_F3 = &H72
Const KEY_F4 = &H73
Const KEY_F5 = &H74
Const KEY_F6 = &H75
Const KEY_F7 = &H76
Const KEY_F8 = &H77
Const KEY_F9 = &H78
Const KEY_F10 = &H79
Const KEY_F11 = &H7A
Const KEY_F12 = &H7B
Const KEY_F13 = &H7C
Const KEY_F14 = &H7D
Const KEY_F15 = &H7E
Const KEY_F16 = &H7F

Const KEY_NUMLOCK = &H90

' Shift parameter masks
Const SHIFT_MASK = 1
Const CTRL_MASK = 2
Const ALT_MASK = 4

Private Function Base7(wValue As Integer)
    ' Convert a number, up to 48 decimal, into base 7.
    Base7 = (wValue  7) & (wValue Mod 7)
End Function

Private Sub ChangeDate(strMoveUnit As String, intDirection As Integer)
    ' Called from OnPush property of the next/previous month/year buttons.
    Dim intMonth As Integer
    Dim intYear As Integer
    Dim intDay As Integer
    Dim varDate As Variant
    Dim varOldDate As Variant
    Dim intInc As Integer
    Dim rstrInterval As String

    On Error GoTo ChangeDateError

    ' Get the current values from the form.
    intYear = Me!Year
    intMonth = Me!Month
    intDay = Me!Day

    intInc = IIf(intDirection = MOVE_FORWARD, 1, -1)
    varOldDate = DateSerial(intYear, intMonth, intDay)
    varDate = DateAdd(strMoveUnit, intInc, varOldDate)

    If (intDirection = MOVE_BACKWARD And varDate > varOldDate) Then
        ' This should only happen when you go backward from
        ' 1/1/100 to 12/31/1999.  Just a quirk of Access' date
        ' handling!
        Exit Sub
    End If

    intMonth = DatePart("m", varDate)
    intYear = DatePart("yyyy", varDate)
    Me!Day = DatePart("d", varDate)

    ' If the month and year haven't changed, then just
    ' move to the selected day.  It's a lot faster.
    If Me!Month = intMonth And Me!Year = intYear Then
        HandleIndent "lbl" & Day2Button((Me!Day), intStartDOW)
        ' Set the values on the form and then display the new calendar.
        Me!Month = intMonth
        Me!txtMonth = GetMonthName(intMonth)
        Me!Year = intYear
    End If

    Exit Sub

    Resume ChangeDateExit
End Sub

Private Sub cmdCancel_Click()
End Sub

Private Sub CmdNextMonth_Click()
End Sub

Private Sub CmdNextMonth_KeyDown(KeyCode As Integer, Shift As Integer)
    HandleKeys KeyCode, Shift
End Sub

Private Sub CmdNextYear_Click()
End Sub

Private Sub CmdNextYear_KeyDown(KeyCode As Integer, Shift As Integer)
    HandleKeys KeyCode, Shift
End Sub

Private Sub cmdOK_Click()
    ' Just hide the calendar form.  This makes it possible for the caller
    ' to get at the date that was chosen.
    Dim var As Variant
    var = SelectDate(strSelected)
End Sub

Private Sub CmdPreviousMonth_Click()
End Sub

Private Sub CmdPreviousMonth_KeyDown(KeyCode As Integer, Shift As Integer)
    HandleKeys KeyCode, Shift
End Sub

Private Sub CmdPreviousYear_Click()
End Sub

Private Sub CmdPreviousYear_KeyDown(KeyCode As Integer, Shift As Integer)
    HandleKeys KeyCode, Shift
End Sub

Private Function Day2Button(wDay As Integer, intStartDay As Integer)
    Day2Button = Base7(wDay + intStartDay - 2 + 7) + 1
End Function

Private Function DaysInMonth(varMonthNumber As Variant) As Integer
    ' Get the number of days in the passed-in month.
    ' If the month isn't February, we know its length.
    If varMonthNumber  M_FEB Then
        DaysInMonth = aintMonthLen(varMonthNumber)
        ' Since Access knows the leap year stuff, let's let IT do the work here!
        ' Get the last day of the month of February for the currently displayed year.
        DaysInMonth = DatePart("d", DateSerial(Me!Year, M_MAR, 1) - 1)
    End If
End Function

Private Sub DisplayCal()
    ' Actually display the calendar.
    Static wInHere As Integer

    ' Let's make sure we don't end up in here recursively!
    If wInHere Then Exit Sub
    wInHere = True

    ' Figure out the starting day of week for the given month.
    intStartDOW = FirstDOM((Me!Month), (Me!Year))

    ' Finally, really display the calendar.
    ShowDate intStartDOW
    wInHere = False
End Sub

Private Sub FillInStartValues()

    Dim varStartDate As Variant

    If Not IsDate(Me.OpenArgs) Then
        varStartDate = Date
        varStartDate = CVDate(Me.OpenArgs)
    End If
    If IsNull(varStartDate) Or IsEmpty(varStartDate) Then
        varStartDate = Date
    End If

    ' Store away the start date values (varStartDate is global).
    Me!Month = DatePart("m", varStartDate)
    Me!Year = DatePart("yyyy", varStartDate)
    Me!Day = DatePart("d", varStartDate)
    Me!txtMonth = GetMonthName((Me!Month))

End Sub

Private Function FirstDOM(intMonth As Integer, intYear As Integer) As Integer
    ' Calculate the first day of the month in question.
    FirstDOM = DatePart("w", DateSerial(intYear, intMonth, 1), FIRST_DAY)
End Function

Private Sub FixDaysInMonth(intStartDay As Integer)
    ' Turn on and off buttons in the currently displayed month.
    Dim intRow As Integer
    Dim intCol As Integer
    Dim intNumDays As Integer
    Dim intCount As Integer
    Dim strTemp As String

    intNumDays = DaysInMonth(Me!Month)
    ' If the chosen date is past the last day in this month,
    ' then just select the last day of this month.
    If Me!Day > intNumDays Then
        Me!Day = intNumDays
    End If

    intCount = 0
    For intRow = 1 To 6
        For intCol = 1 To 7
            If (intRow = 1) And (intCol < intStartDay) Then
                Me("lbl1" & intCol).Visible = False
                intCount = intCount + 1
                strTemp = "lbl" & intRow & intCol
                If intCount  0 Then
        If strSelected  strNewSelect Then
            Me(strSelected).SpecialEffect = 0
        End If
    End If
    strSelected = strNewSelect
    Me(strSelected).SpecialEffect = 2
    Me!Day = Me(strSelected).Caption

End Sub

Private Sub HandleKeys(KeyCode As Integer, Shift As Integer)

    ' Key Mappings:
    ' Leftarrow = Previous Day
    ' Shift-Leftarrow = Previous Year
    ' Rightarrow = Next Day
    ' Shift-Rightarrow = Next Year
    ' Uparrow = Previous week
    ' Shift-Uparrow = Previous Month
    ' Dnarrow = Next Week
    ' Shift-Dnarrow = Next Month
    ' PgUp = Previous Month
    ' Shift-PgUp = Previous Year
    ' PgDn = Next Month
    ' Shift-PgDn = Next Year
    ' Home = Move to Today
    ' Shift-Home = Move to today in selected year.

    Dim ShiftDown As Integer

    ShiftDown = ((Shift And SHIFT_MASK) > 0)

    Select Case KeyCode
        Case KEY_ESCAPE
        Case KEY_RETURN
            Me.Visible = False
        Case KEY_HOME
            If ShiftDown Then
                ' Use the selected year.
                MoveToToday False
                ' Use the actual current year.
                MoveToToday True
            End If
        Case KEY_PRIOR
            If ShiftDown Then
                ChangeDate CHANGE_YEAR, MOVE_BACKWARD
                ChangeDate CHANGE_MONTH, MOVE_BACKWARD
            End If
        Case KEY_NEXT
            If ShiftDown Then
                ChangeDate CHANGE_YEAR, MOVE_FORWARD
                ChangeDate CHANGE_MONTH, MOVE_FORWARD
            End If
        Case KEY_RIGHT
            If ShiftDown Then
                ' Move to next year
                ChangeDate CHANGE_YEAR, MOVE_FORWARD
                ChangeDate CHANGE_DAY, MOVE_FORWARD
            End If
        Case KEY_LEFT
            If ShiftDown Then
                ' Move to previous year
                ChangeDate CHANGE_YEAR, MOVE_BACKWARD
                ChangeDate CHANGE_DAY, MOVE_BACKWARD
            End If
        Case KEY_UP
            If ShiftDown Then
                ' Move to previous month
                ChangeDate CHANGE_MONTH, MOVE_BACKWARD
                ChangeDate CHANGE_WEEK, MOVE_BACKWARD
            End If
        Case KEY_DOWN
            If ShiftDown Then
                ' Move to next month
                ChangeDate CHANGE_MONTH, MOVE_FORWARD
                ChangeDate CHANGE_WEEK, MOVE_FORWARD
            End If
    End Select
    ' Tell Access to disregard the key press.
    KeyCode = 0
End Sub

Private Function HandleSelected(strName As String)
    HandleIndent strName

End Function

Private Sub MoveToToday(fUseCurrentYear As Integer)
    ' Month and year get filled in from the form.

    ' Go to the stored current date.
    Me!Month = intMonthToday
    Me!txtMonth = GetMonthName((Me!Month))
    Me!Day = intDayToday
    If fUseCurrentYear Then
        Me!Year = intYearToday
    End If

    ' Actually display the calendar.
End Sub

Private Function SelectDate(strName As String)
    HandleIndent strName
    Me.Visible = False
End Function

Private Sub ShowDate(intStartDay As Integer)
    Dim newSelected As String

    ' Fix up the visible day buttons.
    FixDaysInMonth intStartDay

    ' Set the right button as depressed when the month is displayed.
    newSelected = "lbl" & Day2Button((Me!Day), intStartDay)
    HandleIndent newSelected
End Sub

im building a database that will take customer orders, but whatever i do i take one step forward and 12 steps back!!!!!!!!!!

ive got a transaction form with transaction ID, date and time, then a subform that contains Item ID, description, quantity, price and line total.

Ive been trying queries, control source etc but to no avail

All i want is to be able to work out the cost of each line then get a total! I either get a #name?, #error?, or not even that far as i get frustrated with it and have to walk away before i smash my laptop

Can anyone help? im at my wits end


I'm a new user here and new Access user, but have used this wonderful site as a resource.

My question concerns the structure of my database. I'm doing a simple db which collects data on clients, specifically visit and consult times. I have a main form with basic client info. I then have two sub-forms, linked to the master form by client number, with specific information on visits and consults. (a) The first subform concerns consult times (b) The second one contains a second set of times.

Before I go forward, I'm wondering if structurally, there is any reason to have two sub-forms rather than one.

I am also performing calculations in the sub-forms. In subform (b), they work fine (I used a string).

In subform (a), however, I need a piece of information from subform (b) to do the calculation. I don't know how to get that piece of information (a general date and time) over to subform (a). If I try to put in the control source equation =ElapsedTimeString([ConsultRequested],[OrdersWritten]) I get an error. The reason is because [ConsultRequested] is from subform (b).

If I try to put in instead something like [Conslt_Tblsubform]![ConsultRequested] it also doesn't work. (I'm sure that last bit is totally wrong.)

So, should I just have one sub-form, which would probably solve the problem?

Also, this is an unbound calculation. I did also try to write a SetValue macro which would save this to a table. However, if I need to run reports or make charts with averages, isn't it better to just do the calculations then and there, rather than save them to the table?

I realize that my questions may be a bit unclear and I apologize. This is a first for me.

Thanks in advance and please let me know if I need to clarify.

This has probably been asked a million times before but i can`t find a reply that suits my problem!!

I need to add a text box to my form with a formula that calculates the days between 2 separate date fields within my form (excluding weekends). I know Access doesn`t recognise NetWorkDays as it does in Excel.

I have created a table with a list of weekend dates and bank holiday dates that i want to minus from my formula but have no idea how to do this!

Any ideas gratefully received. I am not advanced enough to use 'code' so an idiot guide also gratefully recieved if this is the suggested route


I’m new to Access and this forum. I just started using Access about a year ago and really enjoy working in it. But I’m not whiz kid at it and any help is much appreciated.

My friend took over my old job and asked me to help him with room scheduling. Previously they were using Excel for room scheduling but that takes up too much of his time. I’ve put together a database but here are the two issues I come across:

1. How do I prevent a double booking? (Besides the obvious of not entering the same info )

I have a form which allows the user to book rooms. On this form are the following fields:

Count: (Autonumber)
ProgramCode: Text box
DivisionCode: Text Box (drop down box)
Event: Text Box (drop down box)
Room: Text Box (drop down box)
StartDate: Date/Time
EndDate: Date/Time
StartTime: Date/Time
EndTime: Date/Time
NumofSessions: Number (this is a calculated field - for show only)
Semester: Text Box (drop down box)
Year: Text Box (drop down box)

The form adds this information to the Schedule table.

2. How do I have the form check to see if events overlap? If it does overlap, I want the proposed booking to be rejected with a customized message.



I don’t know if this is possible with access. Users wanted an automated re-calculation of the sum of total interest based on the past transaction by a particular transaction date of a client; Of course, there will be an increased interest as a form of penalty. (See attached database sample below.)

The problem starts here:
Since the user’s client wanted to change his loan scheme from Self Liquidating to a basic receivable (debit - credit scheme). Users had applied an interest penalty of additional (1.07%) to his original (3.93%) interest rate by manually re-computing individual transactions.

Now the users requested if the program can somehow auto-recalculate the sum of the additional interest for them and then just file it as an unpaid due to ease the pain of manually getting the additional sum of interest. Is this possible at all?

In the example below: Since this is a Self-Liquidating account, there will be a fixed interest to be paid as follows:

*Original Interest Rate: 3.93%
Fix Interest due: $333.33

1st record of transaction 1558 balance = $10,000.00
Interest due = $333.33 (Amount)*(Rate)
2nd record of transaction 1558 balance = $9,666.66
Interest due = $333.33 (Amount)*(Rate)
3rd record of transaction 1558 balance = $8,999.99
Interest due = $333.33 (Amount)/*(Rate)

Total Interest Paid: $999.99
Total Principal Paid: $2,181.00
Total Remaining Balance: $8,666.66 (Amount) / (Months to Pay) * (Month)

After re-calculation and transforming this account to a basic receivable scheme:
Interest will now be based on balance of each record for every transaction so say:

*Additional (New) interest rate: 1.07%
Balance from 1st record of transaction 1558 = $10,000.00
Additional interest due = $105(Balance * 1.07%)
Balance from 2nd record of transaction 1558 = $9,666.66
Additional interest due = $103 (Balance * 1.07%)
Balance from 3rd record of transaction 1558 = $8,999.99
Additional Interest due = $96.30 (Balance * 1.07%)

Total New Interest due to be paid: $304.30

***This new total sum of interest will be filed as an additional unpaid due of that client. And of course, client has to pay the remaining balance and monthly due to finally close the original account.

=/ As of the moment i can't upload an attachment yet, I have tried for a couple of times already and it's not responsive at all. No gauge bar running, no failure warning or any kind of response at all whatsoever. It just sat there and I've waited like forever. Sorry by the way. I'll attached it as soon as possible. I hope some can understand what I've written above, I'm willing to try anything. . .

I have an Employee information form with a time off sub form. The sub form has the fields Date, Reason (vacation, Sick, Personal, or Other), and notes. I need a "vacation days left" field in the main form that calculates the total days off for each category and subtracts it from the allowed for that category.
Basically I need to translate this literal verbal equation into a workable Access function: "Count all entries where reason equals vacation (or sick, etc.) and subtract from total allowed". That seems simple enough, but I know very little about built in functions for calculations, but there is also a kicker. I also have a "vacation reset date" field that I need to automatically change to one year later once the previous date has passed. Then I need to filter the sub form to only display records after that date and count only those records (with an option to view all records if necessary) How would I go about doing this, preferably without writing VBA code, which I know even less about?

I will be doing a lot more, that I'm sure I will need help on, with this form once this task is figured out, so if anyone is interested in helping me personally please let me know.

Not finding an answer? Try a Google search.