UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Add A Record To A Table That Checks The Date Isn't Already Between A Date Range (start And End), Access 2010    
 
   
HippyStarFish
post Mar 29 2020, 12:30 PM
Post#1



Posts: 7
Joined: 29-March 20



Hello,

I have created a database that has a start and end date.

I have a form that lets users add the start and end dates but I don't want any of the dates to overlap.

E.g. If a start date (10 Mar 2020) and and end date (17 Mar 2020) had already been created, I would need a VB check on the start form field that would check the start and end dates in the table to make sure the dates are not already there. E.g. someone else tried to create a date range of 12 March 2020 to 15 March 2020.

Any ideas, please?
Go to the top of the page
 
cheekybuddha
post Mar 29 2020, 12:41 PM
Post#2


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


Hi,

welcome2UA.gif

Before saving the new start and end date you will need to check your table for 2 things:
1. The start date does not fall within an already used period
2. The end date is less than the next highest start date

Is that correct?

d

--------------------


Regards,

David Marten
Go to the top of the page
 
HippyStarFish
post Mar 29 2020, 12:46 PM
Post#3



Posts: 7
Joined: 29-March 20



Hi,

No, sorry, I possibly didn't explain myself very well.

It's pretty much like this:

If MyFormDate is between StartDate and EndDate Then
Bad dates

MyFormDate = (Start=> And <=End)
Bad dates

Hope that makes more sense?
This post has been edited by HippyStarFish: Mar 29 2020, 12:47 PM
Go to the top of the page
 
cheekybuddha
post Mar 29 2020, 12:52 PM
Post#4


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


Who is entering MyFormDate ?

--------------------


Regards,

David Marten
Go to the top of the page
 
HippyStarFish
post Mar 29 2020, 12:56 PM
Post#5



Posts: 7
Joined: 29-March 20



The users (data entry) will be entering the form dates
Go to the top of the page
 
cheekybuddha
post Mar 29 2020, 12:59 PM
Post#6


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


OK.

Sorry, let's dial it back a bit.

What is the form name?
What are the names of the textboxes in which you/the users enter the start/end dates?
What is the name of the table that the form uses as its RecordSource?
What are the fields names in that table, and which field is the primary key?

--------------------


Regards,

David Marten
Go to the top of the page
 
HippyStarFish
post Mar 29 2020, 01:06 PM
Post#7



Posts: 7
Joined: 29-March 20





What is the form name? like.png frm_AddNewRecord
What are the names of the textboxes in which you/the users enter the start/end dates? like.png The form field are called Start and End
What is the name of the table that the form uses as its RecordSource? like.png tblSumR
What are the fields names in that table, and which field is the primary key? like.png StartDate, EndDate and the primary key is just and auto number called PriID but have joined my tables using emID because I'm importing some of the data and that field is supposed to be unique)

I think my >= <= are causing the issues? Maybe?

thanks.gif
Go to the top of the page
 
cheekybuddha
post Mar 29 2020, 01:16 PM
Post#8


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


So, the issue is when a new user enters in Start/End you need to be sure that the end date is not after another previously entered start/end period's start date?

Does it matter if the start date entered also falls within a previously entered start/end period?

--------------------


Regards,

David Marten
Go to the top of the page
 
HippyStarFish
post Mar 29 2020, 01:21 PM
Post#9



Posts: 7
Joined: 29-March 20



Hi,
Thanks for your help with this.

If the users was to try to add a date that was already covered in the table start and end date ranges, the user should get a message saying "This date is already in the table, please edit that record"

It's like a scenario where a hotel room can only be booked once at any one time -otherwise the holiday makers will be sharing a room! I hope that helps?


Go to the top of the page
 
projecttoday
post Mar 29 2020, 01:25 PM
Post#10


UtterAccess VIP
Posts: 12,399
Joined: 10-February 04
From: South Charleston, WV


So, why don't the rules David put in post #2 cover that? Explain with an example, please.

--------------------
Robert Crouser
Go to the top of the page
 
HippyStarFish
post Mar 29 2020, 01:33 PM
Post#11



Posts: 7
Joined: 29-March 20



Hi

It probably does, I've been staring at my screen all day so my algorithms are all amiss!

The start dates have to be filled by the users but the end date doesn't because we don't always know the end date. So I based my code on the start date like this:

If IsNull(EndDate) Then
If StartDate >= Me.Start Then
MsgBox " Start date is already covered in the table "
End If
Else
If Me.Start >= StartDate AND Me.Start <= EndDate Then
MsgBox "Dates have already been entered into the table"
Else msgBox " stuck.. "
End If
End If

When I test in the form, I just see " stuck.." no matter what I add on the Start field

**I haven't yet included the emID because I'm testing with only one record
This post has been edited by HippyStarFish: Mar 29 2020, 01:36 PM
Go to the top of the page
 
cheekybuddha
post Mar 29 2020, 03:15 PM
Post#12


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


>> The start dates have to be filled by the users but the end date doesn't because we don't always know the end date. <<

Ah, that is a useful bit of information!

In the code you just posted where are StartDate and EndDate declared/ coming from?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 29 2020, 04:05 PM
Post#13


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


Hi,

You should use the BeforeUpdate event of each control to validate the inputs:
CODE
Private Sub Start_BeforeUpdate(Cancel As Integer)

  Dim strWhere As String

  strWhere = "emID = " & Me.emID & " AND StartDate <= " & Format(Me.Start, "\#yyyy\-mm\-dd\#") & " AND EndDate >= " & Format(Me.Start, "\#yyyy\-mm\-dd\#")
  If DCount("*", "tblSumR", strWhere) > 0 Then
    Cancel = True
    MsgBox "This date falls between an existing period for this emID", vbOK + vbInformation, "Bad Date"
    Me.Undo
  End If

End Sub

Private Sub End_BeforeUpdate(Cancel As Integer)

  Dim strWhere As String

  strWhere = "emID = " & Me.emID & " AND StartDate <= " & Format(Me.End, "\#yyyy\-mm\-dd\#") & " AND EndDate >= " & Format(Me.Start, "\#yyyy\-mm\-dd\#")
  If DCount("*", "tblSumR", strWhere) > 0 Then
    Cancel = True
    MsgBox "This end date falls after the next period starts for this emID", vbOK + vbInformation, "Bad Date"
    Me.Undo
  End If

End Sub


For the second date (End) see this thread to understand the logic.

It does require that you should enter a start date before entering and end date, otherwise the code might bomb. If that's likely to be an issue then let us know.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
HippyStarFish
post Mar 29 2020, 04:14 PM
Post#14



Posts: 7
Joined: 29-March 20



Hi,

I have solved this (thank you for the chat as sounding out really helps!!). Are you able to see if I have missed anything?


Private Sub Start_AfterUpdate()
'set date variables for start and end dates for the srtable
Dim StartDate As Date
Dim EndDate As Date

'assign data to the variables
StDate = (DLookup("[StartDate]", "[srtable]", "([StartDate]) And ([emId] = " & Me.comboemid & ")"))
EnDate = (DLookup("[EndDate]", "[srtable]", "([EndDate]) And ([emId] = " & Me.comboemid & ")"))

'check if a record is already open
If Not IsNull(DLookup("[emId]", "[srtable]", "IsNull([EndDate]) And ([emId] = " & Me.comboemid & ")")) Then
MsgBox "End date empty"

'if the end date is not empty check the date in the form is not in the table already
Else
If Start >= StDate And Start <= EnDate Then
MsgBox " record open with start date " & StDate & " to make any changes, please edit the record"
Me.Undo
DoCmd.Close acForm, "AddNewRecord", acSave
End If

End If
End Sub


Sorry cheekybuddha I didn't see your code. I will have a look now... Thank you!!!!!!!
This post has been edited by HippyStarFish: Mar 29 2020, 04:16 PM
Go to the top of the page
 
zocker
post Mar 30 2020, 01:51 PM
Post#15


Utterly Eccentric and Moderator
Posts: 4,119
Joined: 4-March 00
From: Bristol / Ipswich / Spain/Gloucester


This example may help, as well as what's booked, it can show you what's available for any date range.
HERE


Z

--------------------
I think they're for 1 am. Rene Descartes' Mum
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th July 2020 - 03:26 PM