If statements not giving desired results for resources database

**NeoPa** · May 28 '14, 09:57 PM

You may want to consider working with Date/Time values rather than separate dates and times. It's possible to do it the latter way, but more (and unnecessarily so) complicated (As you've already found of course).

**BitesBoy** · May 29 '14, 03:46 PM

Thanks for taking the time to look and reply NeoPa. I am assuming you mean changing from Short Time to General Date. I have tried this and it makes no difference. If I can boil the problem into one short paragraph, it would be this. The database macro will let me reserve the same equipment multiple times within one day. It will also permit me to reserve one item over the duration of more than one day. It won't let me reserve the 2 concurrently. There a several macros, besides the reserve item, not least a book item and an an update reservation to click out and return the item on collection and return. TimeOut, DateCheckedOut, DueDate and TimeIn were in Short Time Format. I changed them all to General Date, but the result is the same. Is there a different date format I should use or due you mean I should write the code in the macro differently?

**NeoPa** · May 29 '14, 06:08 PM

The format is entirely irrelevant :-(

If you store the date and the time of the points in time you're working with then you will find it easier to work with them. Instead of saying :

Code:

Is Date1 < Date2 AND Is Time1 < Time2 Then

you can do far simpler comparisons similar to :

Code:

Is DateTime1 < DateTime2 Then

That's pseudo-code of course, but do you see what I'm trying to say?

**BitesBoy** · May 30 '14, 11:24 AM

Forgive me for being niaive here as I'm on a steep learning curve. Basically I used the find/replace aid to replace all references in the macros for Date and Time alone to DateTime. I then changed the control sources in the forms in design view to reflect the changes. When I ran the project I get the follwing error.
"Compile error

Method or data member not found"

When the debugger comes up indicates it doesn't recognise the

term Me.DateTimeIn.V isble:

Code:

Private Sub Form_Load ()
If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
    Me.DateTimeIn.Visible = True
    Me.DateTimeOut.Visible = True
End If

What am I doing wrong please?

**jimatqsi** · May 30 '14, 12:01 PM

BitesBoy,
You've tried to take a shortcut by mass replacing one thing with another. You should have gone painstakingly through the code to verify what you were doing. You changed the name of one thing in the code to some other thing but that thing does exist. You have no object on your form called DateTimeIn and DateTimeOut.

Here's what may get you to your quickest fix. Go back to your original code. You can combine a date and time into one DateTime variable simply by concatenating. Like this:

Code:

Dim dtOut as Date
dim dtIn as Date
dtOut = me.DateOut & " " & me.TimeOut
dtIn = me.DateIn & " " & me.TimeIn
if dtIn > dtOut then
 msgbox "error"
end if

You might also find the information here helpful if you really want to learn about handling dates and times. It's an old link but I think info is still all relevant:

http://support.microsoft.com/kb/210604

Jim

**NeoPa** · May 30 '14, 06:14 PM

No problem for finding it confusing BitesBoy. I suspect you were more floundering than looking for a shortcut, but nevertheless Jim's post throws some light on the point I was trying to get across.

Basically, when working with timestamps (a point in time with a date and time element) it is better to hold all the information as a single value than trying to specify it with two separate values - to whit a date and a time.

How you interact with the user is up to you. They can enter it separately or as a single value, as long as any separate entry is followed automatically by joining them together for storing.

Jim's illustration makes it fairly clear why this makes sense.

**jimatqsi** · May 30 '14, 10:17 PM

I'm sorry if I came across harshly, it was not my intent. I think we all want posters to feel eager to come back for more help and advice.

Let us know how this turned out, BitesBoy.

Jim

**BitesBoy** · May 31 '14, 01:29 PM

No offense taken Jim. I can see why someone looking on the outside in, with limited prior knowledge, could not help but think I was taking the lazier route. But if the truth be told, NeoPa was on the money when he suspected I was floundering. If I was to give your site the full background as to how I ended up working on this database, I'd probably have to give you as much info as I submitted for the code in the one module I uploaded.

The short version is my knowledge of databases and Visual Basic goes back a decade. All my dB knowledge gained then was on Access 2003 and used purely to get a qualification. Life, either by work or private projects, offered me no further incentives to continue using that knowledge. That is until a few months ago when I inherited this database from someone who left our employ, who clearly knew how to create a fully relational, macro-enabled database. And yet for the creator's knowledge and skills, you will have gathered from my query that even the creator clearly did not have the definitive knowledge required to provide full functionality to the database.

So when I did take up the challenge I found that the back-end and front-end changes in Access 2010 compared to 2003 to be like night and day. Whether I like it or not, with no one else with the knowledge to maintain it I inherited the role of maintaining the database .

For all my shortcomings, through sheer doggedness and trial and error, I have managed to master a lot of the requirements necessary to maintain it. So thank you for your, and NeoPa's patience in baring with some of my rather naive response.

My rant is nearly over. I'm impressed by the speed of responses on this site, yours and NeoPa's. It is truly heartening that there are people in the community who are prepared to offer their advice freely. I also realise that this medium is not always the best way to convey and transmit thoughts and requests, because the nature of learning computing skills, whether applications or programming, is often learned best by seeing things in action. Perhaps screen recordings of the effects people are trying to convey might enhance this.

I look forward to implementing your coding changes, but will only be able to tackle this on Monday at work, where I have access to Access2010. At home I still only have the older technology.

Thanks again,
BitesBoy

**NeoPa** · Jun 1 '14, 04:59 PM

That's all perfectly reasonable :-)

If you think it would help, I can put you in touch with someone who can help professionally with teaching and helping you directly in your project at the same time. She is a fellow Access MVP and specialises in such 'training on the job'.

Let me know if you're interested and I'll PM you her details. She's very experienced and will certainly be able to take you forward. I can't say what her rates are, but I suspect they're very reasonable - as long as you remember it's a professional service (Some people seem to believe they can get hours of professional service for the price of a pint of beer).

Check her (Crystal Long) out on :
Learn Access Playlist on YouTube

**BitesBoy** · Jun 2 '14, 04:17 PM

I tried the new date time format advised by NeoPa and detailed by Jim. Unfortunately, it did not work. At first I ran it and it threw up an object issue me at the bottom:

Code:

 Me.TimeOut.Visible = True
        Me.TimeIn.Visible = True

I changed the code for these 2 lines back and it ran, but still didn't provide the desired affects. I wondered if you might be so kind to look over the code changes I made and comment if there is anything amiss?

Code:

Option Compare Database

Private Sub Form_Current()

varDuration = DLookup("DefaultLoanDuration", "Equipment", "EquipmentID = '" & Nz(Me.EquipmentID, "") & "'")
Me.txtDuration = varDuration

End Sub

Public Function CheckValidReservationOld(Cancel As Integer)

Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer
Dim dtOut As Date
Dim dtIn As Date
Dim dtDue As Date

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) ORDER BY DateCheckedOut ASC")

dtOut = Me.DateCheckedOut & " " & Me.TimeOut
dtIn = Me.DateCheckedIn & " " & Me.TimeIn
dtDue = Me.DueDate & " " & Me.TimeIn

If dtIn < dtOut Then
    MsgBox "Date reserved to cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Warning"
    Cancel = True
    Exit Function
End If

If dtDue < dtOut Then
    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Warning"
    Cancel = True
    Exit Function
End If

If rs.RecordCount > 0 And rs.RecordCount <> -1 Then
    MsgBox "Reservations or loans exist", vbOKOnly
    'Item already has one or more reservations
    'Need to check if the new reservation clashes with any existing reservations or bookings
    rs.MoveFirst
    Do While Not rs.EOF
    
        If dtOut >= rs!dtOut And dtDue <= rs!Due Then
            MsgBox "Dates clash with an existing booking/reservation", vbOKOnly, "Reservation Exists"
            Cancel = True
            dtOut.SetFocus
            Exit Function
        'If (Me.DateCheckedOut >= rs!DateCheckedOut And Me.DueDate <= rs!DueDate) Then
            'MsgBox "Dates clash with an existing booking/reservation", vbOKOnly, "Reservation Exists"
            'Cancel = True
            'Me.DateCheckedOut.SetFocus
            'Exit Function
        Else
            'Continue to save record and close window
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.Close
        End If
    Loop
Else
    'No reservations exist
    'Continue to save record and close window
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
End If

End Function


'Public Function CheckValidReservation(Cancel As Integer)
'
'Dim db As DAO.Database, rs As DAO.Recordset
'Dim intnewrec As Integer
'
'Set db = CurrentDb()
'
'If Me.DueDate < Me.DateCheckedOut Then
'    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Date Error"
'    Cancel = True
'    Exit Function
'End If
'
'Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) AND (DateCheckedOut>=Date()) ORDER BY DateCheckedOut ASC")
'
'StartDate = Me.DateCheckedOut
'EndDate = Me.DueDate
'
'test = False
'
'If rs.RecordCount = 0 Then
'    'Currently no existing bookings or reservations
'    'Ok to save record and close
'    DoCmd.RunCommand acCmdSaveRecord
'    DoCmd.Close
'    Exit Function
'End If
'
'rs.MoveFirst
'
'Do Until rs.EOF
'
'If StartDate = rs!DateCheckedOut Or EndDate = rs!DueDate Then
'    test = True
'ElseIf StartDate >= rs!DateCheckedOut And StartDate <= rs!DueDate Then
'    test = True
'ElseIf EndDate <= rs!DueDate And EndDate >= rs!DateCheckedOut Then
'    test = True
'End If
'
'If test Then
'    rs.MoveLast
'    rs.MoveNext
'Else
'    rs.MoveNext
'End If
'
'Loop
'
'rs.Close
'
'If test = False Then
'    'Dates selected are ok
'    'Continue to save record and close window
'    DoCmd.RunCommand acCmdSaveRecord
'    MsgBox "Reservation Saved", vbOKOnly + vbInformation, "Reservation Saved"
'    DoCmd.Close
'Else
'    'Display error message to user
'    MsgBox "The dates you have selected clash with an existing booking/reservation. Please choose different dates.", vbOKOnly + vbExclamation, "Reservation Clash"
'    'Cancel
'    Cancel = True
'    Me.DateCheckedOut.SetFocus
'
'    Set rs = Nothing
'    Set db = Nothing
'
'    Exit Function
'End If
'
'Set rs = Nothing
'Set db = Nothing
'
'End Function

Public Function CheckValidReservation(Cancel As Integer)

Dim db As DAO.Database, rs As DAO.Recordset
Dim intnewrec As Integer

Set db = CurrentDb()

If dtDue < dtOut Then
    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Date Error"
    Cancel = True
    Exit Function
End If

If dtDue = dtOut And Format(dtIn, "Short Time") < Format(dtOut, "Short Time") Then
    MsgBox "Due Date cannot be before the Reservation From date", vbOKOnly + vbExclamation, "Time Error"
    Cancel = True
    Exit Function
End If


Set rs = db.OpenRecordset("SELECT EquipmentID, DateCheckedOut, DueDate, TimeOut, TimeIn, IsReservation FROM Loan WHERE EquipmentID='" & Me.EquipmentID & "' AND (IsReservation=True OR DateCheckedIn Is Null) AND (DateCheckedOut>=Date()) ORDER BY DateCheckedOut ASC")

StartDate = dtOut
EndDate = dtDue
TimeOut = Format(dtOut, "Short Time")
TimeIn = Format(dtIn, "Short Time")

test = False

If rs.RecordCount = 0 Then
    'Currently no existing bookings or reservations
    'Ok to save record and close
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
    Exit Function
End If

rs.MoveFirst

Do Until rs.EOF

If StartDate = rs!dtOut And EndDate = rs!dtDue Then
    If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
        'Perform additional time tests for netbook sets
        If IsNull(dtIn) Or IsNull(dtOut) Then
            MsgBox "Please enter time values", vbOKOnly + vbExclamation, "Enter Times"

            Cancel = True
            dtOut.SetFocus
            rs.Close
            Set rs = Nothing
            Set db = Nothing
            Exit Function
        Else
            If TimeOut = rs!dtOut Or TimeIn = rs!dtIn Then
                test = True
            ElseIf TimeOut >= rs!dtOut And TimeOut <= rs!dtIn Then
                test = True
            ElseIf TimeIn <= rs!dtIn And TimeIn >= rs!dtOut Then
                test = True
            End If
        End If
    Else
        test = True
    End If
ElseIf StartDate = rs!dtOut Or EndDate = rs!dtDue Then
    test = True
ElseIf StartDate >= rs!dtOut And StartDate <= rs!dtDue Then
    test = True
ElseIf EndDate <= rs!dtDue And EndDate >= rs!dtOut Then
    test = True
ElseIf StartDate <= rs!dtOut And EndDate >= rs!dtOut Then
    test = True
End If

If test Then
    rs.MoveLast
    rs.MoveNext
Else
    rs.MoveNext
End If

Loop

rs.Close

If test = False Then
    'Dates selected are ok
    'Continue to save record and close window
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Reservation Saved", vbOKOnly + vbInformation, "Reservation Saved"
    DoCmd.Close
Else
    'Display error message to user
    MsgBox "The dates you have selected clash with an existing booking/reservation. Please choose different dates.", vbOKOnly + vbExclamation, "Reservation Clash"
    'Cancel
    Cancel = True
    dtOut.SetFocus
    
    Set rs = Nothing
    Set db = Nothing
    
    Exit Function
End If

Set rs = Nothing
Set db = Nothing

End Function

Private Sub Form_Load()

If Me.EquipmentID = "MLAP-SET01" Or Me.EquipmentID = "MLAP-SET02" Or Me.EquipmentID = "MTAB-SET01" Or Me.EquipmentID = "MTAB-SET02" Or Me.EquipmentID = "MTAB-SET03" Or Me.EquipmentID = "MTAB-676c" Or Me.EquipmentID = "MTAB-677c" Or Me.EquipmentID = "MTAB-678c" Or Me.EquipmentID = "MTAB-679c" Or Me.EquipmentID = "MTAB-680c" Or Me.EquipmentID = "MTAB-681c" Or Me.EquipmentID = "MTAB-682c" Or Me.EquipmentID = "MTAB-683c" Or Me.EquipmentID = "MTAB-684c" Or Me.EquipmentID = "MTAB-685c" Then
        Me.TimeOut.Visible = True
        Me.TimeIn.Visible = True

NeoPa, thanks for offering as a contact Crystal Long for possible on the job training. I like the sound of this, provided she knows how to offer advice on the Vba side and that she doesn't charge Bill Gates rates.

I will have a look at her play list tonight.

**jimatqsi** · Jun 2 '14, 08:27 PM

Bitesboy, what is the problem? What is the error and how are you arriving at it? You click a button to make that happen?

I see that CheckValidReser vationOld looks pretty good. But CheckValidReser vation makes references to dtIn and dtOut without giving them any value. They are not the same variables as in CheckValidReser vationOld because the "scope" of those variables is the subroutine they are defined in. So if the problem is occurring in CheckValidReser vation you should start by copying your dtIn= and dtOut= code from CheckValidReser vationOld.

Be clear about your current problem each time you post. We cannot know what you have done and what new result you are getting.

Jim

**NeoPa** · Jun 2 '14, 10:09 PM

For help with code you need to be a lot clearer with what the problem is and generally need a fairly small amount of code. A code review of 250+ lines is not something many will get themselves into in their spare/volunteer time. If anyone does then they'd be looking for much clearer indications of what is what.

I say this, not to chasten or chastise, but to give you a better understanding of what you can reasonably expect help with, and what is likely to put people off from helping you. If you can identify where the problem is then people are generally prepared to spend some time helping you pinpoint it.

As for the Crystal thing - I'll PM you on that separately.

If statements not giving desired results for resources database

If statements not giving desired results for resources database

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment