Question dates and uniques

U

unit

New member
Local time
Today, 10:43
Joined
Jan 21, 2014
Messages
2
I have a database with follow recordfields: check in, check out and roomnr.
i set check in and roomno in index as unique same as roomnr. and checkout so that no overbooking is possible but between the to dates i couldn't make the dates to unique in combination with the roomnumber
for exemple :
roomno. 3 check in 20/1/14 check out 22/1/14

roomno.3 check in 21/1/14 check out 25/1/14
how could i show up a error in the 2. row

with diffdate() i created a new recordfield : nights.
is it possible to use a vba code for set the unique?
any ideas how to manage? :banghead:
 
Since someone could check in on the same date as someone checks out the check in date must not be >= the check in date AND < the check out date of any other record.

What must be done is to count any records where this is the case and flag a warning if any exist.

*** Work in Progress ***

Code: 
Public Function checkForExistingBooking(byVal cebRoomNo as Long, byVal cebCheckInDate as variant)
dim varTemp as Variant

  varTemp = "(cebRoomNo = roomno)" _
  & " AND (" & chr(34) & FORMAT(cebCheckInDate, "YYYYMMDD") & chr(34) _
  & " > = FORMAT([check in], ""YYYYMMDD""))" _
  & " AND (" & chr(34) & FORMAT(cebCheckInDate, "YYYYMMDD") & chr(34) _
  & " < FORMAT([check out], ""YYYYMMDD"")"

 varTemp =nz(DCount("roomno","yourBookingTable", varTemp))

  checkForExistingBooking = (varTemp > 0)
End Function
 
Last edited:
Nanscombe, sorry to be blunt but why format dates as strings and compare them there? Dates are dates for a reason, just treat them as dates.
 
Mainly because Dates are actually stored as DateTimes and I prefer to avoid any ambiguity.


Progress so far ..

Code: 
Public Function checkForExistingBooking(ByVal cebRoomNo As Variant, ByVal cebCheckInDate As Variant, ByVal cebCheckOutDate As Variant, ByVal cebID As Long)
Dim varTemp As Variant

  If Len(cebRoomNo & vbNullString) = 0 Then Exit Function
  If Len(cebCheckInDate & vbNullString) = 0 Then Exit Function
  If Len(cebCheckOutDate & vbNullString) = 0 Then Exit Function
  
  varTemp = "(roomno = " & cebRoomNo & ")" _
  & " AND (((" & Chr(34) & Format(cebCheckInDate, "YYYYMMDD") & Chr(34) _
  & " >= FORMAT([check in], ""YYYYMMDD""))" _
  & " AND (" & Chr(34) & Format(cebCheckInDate, "YYYYMMDD") & Chr(34) _
  & " < FORMAT([check out], ""YYYYMMDD"")))" _
  & " OR (" & Chr(34) & Format(cebCheckOutDate, "YYYYMMDD") & Chr(34) _
  & " <= FORMAT([check out], ""YYYYMMDD"")))" _
  & " AND (ID <> " & cebID & ")"

 ' Debug.Print varTemp
 varTemp = Nz(DCount("roomno", "yourBookingTable", varTemp))

  checkForExistingBooking = (varTemp > 0)
  
  If checkForExistingBooking Then MsgBox "A booking already exists", vbOKOnly
End Function

This will give me a True / False answer to whether a record exists, and a popup if it does, for a particular room between check in and check out.

I'm now trying to figure out the event triggers.
 
Last edited:
Guessed as much, but if you just want to make sure you work with dates only... Use datevalue(datetimefield) or Int(datetimefield), dont butcher the field type

Same for "all" the variant type declarations, I would strongly suggest you keep to the proper types.
 
Currently trying the events as follows, I have attached the demo database I was using.

Code: 
Private Sub check_in_AfterUpdate()
Debug.Print checkForExistingBooking(Me.roomno, Me.check_in, Me.check_out, Me.ID)
End Sub

Private Sub check_out_AfterUpdate()
Debug.Print checkForExistingBooking(Me.roomno, Me.check_in, Me.check_out, Me.ID)
End Sub

Private Sub roomno_AfterUpdate()
Debug.Print checkForExistingBooking(Me.roomno, Me.check_in, Me.check_out, Me.ID)
End Sub

Just using Debug.print to output the result.

Latest version of Function.

Code: 
Public Function checkForExistingBooking(ByVal cebRoomNo As Variant, ByVal cebCheckInDate As Variant, ByVal cebCheckOutDate As Variant, ByVal cebID As Long)
Dim varTemp As Variant

  ' If roomno is empty exit function
  If Len(cebRoomNo & vbNullString) = 0 Then Exit Function
  
  ' If check in date is empty exit function
  If Len(cebCheckInDate & vbNullString) = 0 Then Exit Function
  
  ' If check out date is empty exit function
  If Len(cebCheckOutDate & vbNullString) = 0 Then Exit Function
  
  varTemp = ""
  varTemp = varTemp & "(roomno = " & cebRoomNo & ")" ' roomno = cebRoomNo
  
  varTemp = varTemp & " AND (((" & Chr(34) & Format(cebCheckInDate, "YYYYMMDD") & Chr(34)
  varTemp = varTemp & " >= FORMAT([check in], ""YYYYMMDD""))" ' cebCheckInDate >= check in
  
  varTemp = varTemp & " AND (" & Chr(34) & Format(cebCheckInDate, "YYYYMMDD") & Chr(34)
  varTemp = varTemp & " < FORMAT([check out], ""YYYYMMDD"")))" ' cebCheckInDate < check out date
  
  varTemp = varTemp & " OR (" & Chr(34) & Format(cebCheckOutDate, "YYYYMMDD") & Chr(34)
  varTemp = varTemp & " > FORMAT([check in], ""YYYYMMDD"")))" ' cebCheckOutDate > check in
  
  varTemp = varTemp & " AND (ID <> " & cebID & ")" ' Don't check your own record

 Debug.Print varTemp
 varTemp = Nz(DCount("roomno", "yourBookingTable", varTemp))

  checkForExistingBooking = (varTemp > 0)
  
  If checkForExistingBooking Then MsgBox "A booking already exists", vbOKOnly
End Function
 

Attachments

Last edited:
Just trying Int(dateTimeField) and Datevalue(dateTimeField).

Code: 
[U]VBA:[/U]
print int(#3/1/2014 10:00#)
01/03/2014 ' Expected 03/01/2014

print datevalue(#3/1/2014 10:00#)
01/03/2014 ' Expected 03/01/2014

Hmmm, it appears to transpose DD/MM (UK format) to MM/DD (American format) and doesn't work as expected. :banghead:

Performing the same in a query ...
Code: 
[U]Query:[/U]
check in	Int		Datevalue
03/01/2014	03/01/2014	03/01/2014
This doesn't transpose DD/MM. :)

So adding the two together to create an SQL string I get a query which appears to test "DD/MM/YYYY" (data) against "MM/DD/YYYY" (vba). :eek:

I knew there was another reason I preferred converting to strings. :o


ETA:

It's those blasted # doing it. :eek:

I've always attempted to create SQL strings by wrapping # around dates, as we are told, I put #DD/MM/YYYY# and it interprets it as #MM/DD/YYYY#.
 
Last edited:
thank you for all this replies you are awesome. It will help me to understand the system i am just start to lern about VBA
 
Dates in Access are US dates, MAJOR down part of using strings is you cannot use the index set on the column which can mean a considerable hit on performance... That is one of the reasons you keep columns in their native type

Also this is only a where, this time, but should you need weekday from your string-ed date, you need to then reconvert it again...
Keep numbers, numbers and in extension dates, date. Save yourself the headache

And might I add, keep things the same as in the same
Code: 
  varTemp = varTemp & " AND (" & Chr(34) & Format(cebCheckInDate, "YYYYMMDD") & Chr(34)
  varTemp = varTemp & " < FORMAT([check out], ""YYYYMMDD"")))"
Either use the double quote "" or use Chr(34)

And ofcourse you know line continuations ( & _ ) are faster than the way you are appending to strings
Plus, it is less typing and less text
 
Just been reading Allen Browne's article International Dates in Access

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.
Click to expand...

Code: 
strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & [B]Format(Me.StartDate, "mm\/dd\/yyyy")[/B] & "#;"

That explains a lot, to create a SQL string with a date using #s it seems you've always got to convert it to American format first. :banghead:


I'll have a go at rewriting my function using this idea a bit later.
 
Last edited:
namliam said:
And might I add, keep things the same as in the same
Code: 
  varTemp = varTemp & " AND (" & Chr(34) & Format(cebCheckInDate, "YYYYMMDD") & Chr(34)
  varTemp = varTemp & " < FORMAT([check out], ""YYYYMMDD"")))"
Either use the double quote "" or use Chr(34)
Click to expand...

I used chr(34) in this instance to make it clearer to read than using triple quotes (""")

namliam said:
And ofcourse you know line continuations ( & _ ) are faster than the way you are appending to strings
Plus, it is less typing and less text
Click to expand...

I used concatenation, in this case, because you can't mix line continuation and comments.

Of course, not adding comments also saves typing but it doesn't help people to understand what is going on with the code.
 
Last edited:
I have attached a new version of the demo database.

Now I better understand what's going on with the dates in VBA, courtesy of Allen Browne, and have had a chance to reconsider the logic here is an improved version of the function.

Code: 
Public Function checkForExistingBooking(ByVal cebRoomNo As Variant, ByVal cebCheckInDate As Variant, ByVal cebCheckOutDate As Variant, ByVal cebID As Long)
Dim varTemp As Variant

  ' If roomno is empty exit function
  If Len(cebRoomNo & vbNullString) = 0 Then Exit Function
  
  ' If check in date is empty exit function
  If Len(cebCheckInDate & vbNullString) = 0 Then Exit Function
  
  ' If check out date is empty exit function
  If Len(cebCheckOutDate & vbNullString) = 0 Then Exit Function
  
  ' A more efficient version of the string concatenation
  varTemp = varTemp & "(roomno = " & cebRoomNo & ")" _
  & " AND" _
  & " (ID <> " & cebID & ")" _
  & " AND (" _
  & "(#" & Format(cebCheckInDate, "mm\/dd\/yyyy") _
  & "# < [check out])" _
  & " AND " _
  & "(#" & Format(cebCheckOutDate, "mm\/dd\/yyyy") _
  & "# > [check in])" _
  & ")"
 
'  Debug.Print varTemp
  varTemp = Nz(DCount("roomno", "yourBookingTable", varTemp))

  checkForExistingBooking = (varTemp > 0)
  
  If checkForExistingBooking Then MsgBox "A booking already exists", vbOKOnly
End Function

I can't include all of the comments in there so I have made a second version with more comments.

Code: 
Public Function checkForExistingBooking_WithComments(ByVal cebRoomNo As Variant, ByVal cebCheckInDate As Variant, ByVal cebCheckOutDate As Variant, ByVal cebID As Long)
Dim varTemp As Variant

  ' If roomno is empty exit function
  If Len(cebRoomNo & vbNullString) = 0 Then Exit Function
  
  ' If check in date is empty exit function
  If Len(cebCheckInDate & vbNullString) = 0 Then Exit Function
  
  ' If check out date is empty exit function
  If Len(cebCheckOutDate & vbNullString) = 0 Then Exit Function
  
  varTemp = ""
  varTemp = varTemp & "(roomno = " & cebRoomNo & ")" ' roomno = cebRoomNo
  
  varTemp = varTemp & " AND"
  
  varTemp = varTemp & " (ID <> " & cebID & ")" ' Don't check your own record

  varTemp = varTemp & " AND ("
  
  varTemp = varTemp & "(#" & Format(cebCheckInDate, "mm\/dd\/yyyy")
  varTemp = varTemp & "# < [check out])" ' cebCheckInDate <= check out
  
  varTemp = varTemp & " AND "
  
  varTemp = varTemp & "(#" & Format(cebCheckOutDate, "mm\/dd\/yyyy")
  varTemp = varTemp & "# > [check in])" ' cebCheckOutDate > check in
  
  varTemp = varTemp & ")"
 
  Debug.Print varTemp
  varTemp = Nz(DCount("roomno", "yourBookingTable", varTemp))

  checkForExistingBooking_WithComments = (varTemp > 0)
  
  If checkForExistingBooking_WithComments Then MsgBox "A booking already exists", vbOKOnly
End Function
 

Attachments

nanscombe said:
Of course, not adding comments also saves typing but it doesn't help people to understand what is going on with the code.
Click to expand...

Way way to much code around without any comments :/
 
Where people are relatively new to coding I tend to prefer writing a less efficient code with comments so they can understand what's happening.
 
nanscombe said:
Mainly because Dates are actually stored as DateTimes and I prefer to avoid any ambiguity.
Click to expand...

To be correct, Dates are not stored as Date/Time they are stored as numbers. They are displayed as Date Time.
 
You must log in or register to reply here.

Similar threads

R
Unique values
Replies
15
Views
794
Pat Hartman
P
I
Create and training record database
Replies
8
Views
803
Pat Hartman
P
E
Emmanuel Katto Dubai : Help Needed with Generating Unique Customer Codes in Access
Replies
8
Views
655
Pat Hartman
P
E
Emmanuel Katto : Best Way to Format Ascending Lot Numbers Based on Ingredient and Date
Replies
6
Views
423
Mark_
M
N
Add RowNumber Problem
Replies
11
Views
833
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom