Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > Microsoft Access Development
Reload this Page

Availability

Discuss Availability in the Microsoft Access Development forum on Dev Articles.
Availability Microsoft Access Development forum to discuss problems and solutions with this popular DBMS. Use Access to build and modify database tables, or full-featured applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old December 13th, 2004, 08:38 AM
ItalianLodging ItalianLodging is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Location: Italy
Posts: 9 ItalianLodging User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 36 sec
Reputation Power: 0
Unhappy Availability
Hello Everyone,
I need some help to start off with this !!!
I am trying to figure out how to create a sub that checks into Date Fields.
Let's say I have two tables: tblApt (where I store the name of the properties) and tblAvail (the availability table)
for the tblApt I only have IDApt and txtName
and for tblAvail only IDAvail, dtArrival, dtDeparture and IDApt (to relate it to the tblApt) to make sure that each apartment
has its own availability. How do I check in the availability table if a certain date has been already taken?

Thanks for your time.
Reply With Quote
  #2  
Old December 13th, 2004, 09:23 AM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
  
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 5
Hi ItalianLodging

Try this to see if it will get what you are looking for:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dteRequest As Date
Dim strSQL As String
Dim i As Integer

dteRequest = RequestedDate
strSQL = "Select [dtArrival], [dtDeparture] FROM tblAvail "
strSQL = strSQL & "WHERE [dtArrival] >= #" & dteRequest & "#"
strSQL = strSQL & " OR [dtDeparture] <= #" & dteRequest & "#"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveLast
i = rs.RecordCount

If i > 0 Then
Msgbox "Room is Taken"
Else
Msgbox "Room is Available"
End If

This should give you some ideas on how to rewrite the code into your application.

lwells
Reply With Quote
  #3  
Old December 14th, 2004, 10:52 AM
ItalianLodging ItalianLodging is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Location: Italy
Posts: 9 ItalianLodging User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 36 sec
Reputation Power: 0
Further Help
Hello Dear Iwells,



first thank for your prompt help. Unfortunately I am too much a newbie and with this approach



I am getting a little disoriented. I have tried to get out as much as I could from your script but



I am not getting where I want (My fault I am sure). I beg you for a bit of extra help. What I want to achieve is to create the Sub that checks into the date fields already stored in my table. I was thinking to something like



Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord = True Then

Dim dtRequest As Date, strMessage As String

strMessage = " Date Already Entered "

if dtRequest = "#" & dteRequest & "#" = "#" & dteRequest & "#" then

MsgBox strMessage, vbInformation

Cancel = True

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo

Exit Sub

End If

Me.RecordsetClone.MoveFirst

Me.RecordsetClone.FindFirst dtRequest

Do Until Me.RecordsetClone.NoMatch




Me.RecordsetClone.FindNext strWhere

Loop

End If

End Sub





I have tried to work with your script but I couldn’t make it controlling the data field. It gives me the msgBox with room taken even if I leave the date blank and move onto the next record.



I have attached the DB in zip file I hope you can look at it.



Thanks L.



Quote:
Originally Posted by lwells
Hi ItalianLodging

Try this to see if it will get what you are looking for:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dteRequest As Date
Dim strSQL As String
Dim i As Integer

dteRequest = RequestedDate
strSQL = "Select [dtArrival], [dtDeparture] FROM tblAvail "
strSQL = strSQL & "WHERE [dtArrival] >= #" & dteRequest & "#"
strSQL = strSQL & " OR [dtDeparture] <= #" & dteRequest & "#"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveLast
i = rs.RecordCount

If i > 0 Then
Msgbox "Room is Taken"
Else
Msgbox "Room is Available"
End If

This should give you some ideas on how to rewrite the code into your application.

lwells
Attached Files
File Type: zip Try_Avail.zip (28.4 KB, 299 views)
Reply With Quote
  #4  
Old December 15th, 2004, 12:02 AM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
  
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 5
Hi ItalianLodging

See if this is closer to what you were looking for.


Cheers
lwells
Attached Files
File Type: zip Try_Avail.zip (19.5 KB, 265 views)
Reply With Quote
  #5  
Old December 17th, 2004, 06:36 AM
ItalianLodging ItalianLodging is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Location: Italy
Posts: 9 ItalianLodging User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 36 sec
Reputation Power: 0
Red face Still Struggling
Hello Iwells,



the function works perfectly but there is one thing that I was trying to solve on my own but I am still getting lost so



here I am begging for extra help.







If I insert a reservation starting 10/10/2005 end ending 15/10/2005 for an x Apt and right after I insert for example



another one for the same apt starting 12/10/2005 and ending 16/10/2005 the control works perfectly on the date



12/10/2005 because it is already taken and displays the message. This is great but if I, on the same apt, insert the following



reservation starting 11/10/2005 and ending 17/10/2005 the reservation get accepted fine while the period is taken and only



the single nights of 11/10 and 17/10 are available NOT the period in between 12 - 16.

What do you think the best way to make a control over all the dates present in the RS?


Quote:
Originally Posted by lwells
Hi ItalianLodging

See if this is closer to what you were looking for.


Cheers
lwells
Reply With Quote
  #6  
Old December 17th, 2004, 08:13 AM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
  
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 5
Hi ItalianLodging

Yeah, didn't have the code to check for dates that were already booked.

Add this additional piece of code to your Function CheckDeparture()

Dim dteRequest1 As Date
dteRequest1 = dtArrival.Value

Insert this between the End If and End With portion of the function

If (dteStart > dteRequest1) And ((dteEnd < dteRequest) And (dteRequest > dteStart)) Then
MsgBox "Date Range Already Booked " & _
vbCrLf & dteStart & " Thru " & dteEnd
DoCmd.CancelEvent
Exit Function
End If

I haven't tested this throughly so you may have to work with it some, but I think you will understand how to go about it.

lwells
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > Microsoft Access Development > Availability

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway. 
Stay green...Green IT